Automating workflows with Survey123 and Microsoft Power Automate (fomerly known as Microsoft Flow)

54908
127
07-19-2018 11:17 AM
IsmaelChivite
Esri Notable Contributor
24 127 54.9K

[Last update: June 16, 2020]

Starting with our July 2018 update, Survey123 supports working with Microsoft Power Automate (formerly known as Microsoft Flow). Microsoft Power Automate is a cloud-service that lets you easily build workflows to automate repetitive tasks across multiple applications and services.  Here are some examples of the type of things you can do with Survey123 and Power Automate:

  • Notify a supervisor via e-mail and/or SMS, when a new high priority incident is reported through Survey123.
  • Add a reminder to an e-calendar 6 months after a new installation is submitted via Survey123.
  • Add a new row into a spreadsheet, sharepoint list or SQL Server database when a new survey is submitted.

In this ArcNews story, you can learn about how the Massachusetts Department of Conservation and Recreation’s Office of Watershed Management is leveraging Survey123 and Power Automate to streamline boat inspections.

To get you started I will describe, step by step, how you can automate e-mail notifications after one of your surveys is submitted. This is a simple scenario, but going through it, you will quickly realize how to build your own different flows to do entirely new things.

The Survey123 Microsoft Flow connector is available in all Office365 regions except in US Government (GCC)
As of July 10, the connector is ALSO available in US Government Cloud (GCC)

Getting Started with Microsoft Power Automate

Microsoft Power Automate is included with Office 365. The free version has limited capabilities, but you can upgrade to premium subscriptions. For details check the Microsoft Power Automate pricing page.  You can also create a 90-day free trial.

Unless your subscription to Office 365 already shows Microsoft Power Automate in the main page, you will need to launch it from the Office 365 App Switcher as shown in this animation:

Within Power Automate, go into My flows and select Create from blank. This will present a gallery of Connectors, from which you will be able to trigger your Flow.

Setting up a Survey123 trigger in your Flow:

The very first step in every flow you create is to define a trigger. Search for Survey123 in the gallery of connectors. You will notice that Microsoft includes many connectors out of the box. That is the beauty of it!

As you can see, the Survey123 Power Automate connector has only one trigger: When a survey response is submitted.   After selecting the When a survey response is submitted trigger, you will be prompted for your ArcGIS  credentials.   This is because you will need to select among the surveys that you own, the one that will trigger your flow when a record is submitted. Once logged in with your Survey123 credentials, Power Automate will keep your ArcGIS connection for later use.

In my case, I selected the survey called Incident Report.  My Incident Report survey has a few questions such as the location of the incident, a photo, comments and priority, which can be High, Medium or Low.

Adding condition logic and actions

Once your survey trigger is set, you can start adding conditions, actions and loop statements to your flow. In this example I will add a condition to check if the value of the Priority question in the survey response is equal to High

The survey data is exposed within Flow, so you can dynamically extract values from the survey submitted and use them in your condition expression.

Next, I will add an action in the event that the condition (Priority is equal to High) is true.  I want my action to send an e-mail to someone.  I will choose the Gmail connector, and sign in to grant Microsoft Power Automate permission to use my Gmail account to send e-mails.

E-mail connectors will let you define the recipient, subject, body and attachments in the message.  Again, bringing values from the survey response is possible too when defining actions. Note how in the subject and body of the e-mail for example, you can include values from the submitted data, such as the Date, Priority, Comments etc.  If you get creative, you can do other things such as building a link to a map centered at the Incident's location.

The body of the e-mail is made out of HTML tags. This helps you format your email, by highlighting certain text in bold, or adding breaklines when appropriate. To create a map link illustrating the location of the incident I looked at the ArcGIS  Use URL parameters to modify maps—ArcGIS  Help | ArcGIS help topic. Note how the X and Y coordinates of the submitted incident, can be dynamically added to the link in the e-mail.

In a later section in this blog post I will describe some tips to dynamically get data from your submitted survey.

While you could keep adding additional conditions and actions, I will leave it here, and save the Flow.  I encourage you to explore the many connectors included with Power Automate. 

Testing your Flow

Now that your Flow is saved, it will be triggered by Survey123 every time a survey response is submitted.  Do the test yourself: submit data with your survey from either the Survey123 web or field apps and your recipient will receive an email.

Learning more about Microsoft Power Automate

Power Automate provides many capabilities that are out of the scope of this post. If you would like to learn more about Flow I suggest the following:

Handling Dynamic Content from Survey123. A few tips.

One of the great features in Microsoft Power Automate is that you can process incoming data submitted through your survey response. For example, in the scenario above I processed the values in the Priority question to build a condition. I also used the values in the Comment and Location question for the subject of the e-mail.  

Data from your form is exposed through the Dynamic Content dialog in Microsoft Flow. You can directly bring values or apply additional expressions to them.

When you bring data from the submitted survey into your Flow using Dynamic Content, keep the following in mind:

  • If you have hidden questions in your survey (or metadata questions such as start, end, username etc), you will not see them exposed in the Dynamic Content dialog. However, you can always add them manually into Flow following this syntax:

?['feature']?['attributes']?['yourhiddenquestioname']

  • Date and DateTime questions from Survey123 are exposed in Microsoft Flow using the UNIX time format.  If you want to convert UNIX time into a readable date string, use the addseconds function. For example:

addseconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['mydate'],1000) , 'yyyy-MM-dd')

  • The output of a select_one question in Microsoft Flow will not be the label of your choice, but its name.  If you want to include the labels of your selected choices in the payload of the webhook, so you can use them through the Dynamic Content in Flow, then use the jr:choice-name() function to extract the label in a calculate or hidden question.

jr:choice-name(${activity},'${activity}')

The first parameter represents the choice name. In the example above, I am passing the choice name selected in the 'activity' question.  The second parameter is the question in the form using the list where the choice has been made.  Very often, the first and second parameters will reference the same question, but note that the second parameter is enclosed with quotes, because it does not refer to the value (response) of the question, but to the actual question itself.

Some additional technical details

Technically, when you save your Flow, Microsoft Flow registers a webhook in your own survey. Webhooks are the foundation for having Survey123 communicate with Flow to make all the steps described above work.

A webhook is simply a URL Callback. That is, a URL that is invoked by an app when a particular event occurs, to pass some data to another app.  In our case, the apps are Survey123, which invokes the URL, and Microsoft Flow, which is the receiver of the webhook.

In the initial steps described above, when the trigger was setup by selecting the survey, Microsoft Flow created a unique URL representing your Flow. This url was registered as the webhook URL in your own survey when you saved the Flow.

If working with the Survey123 field app, it is important to remember that users will need to download the survey into the app after the webhook URL has been registered. 

If you want to look at the webhooks registered in a particular survey, simply follow these steps:

  1. Log into survey123.arcgis.com and select a survey
  2. Switch to the Settings tab of the survey
  3. Open the Webhooks section.

You really do not need to setup webhooks manually when using Power Automate but the webhook dialog can be of interest in case you are working with other Workflow Automation services or with your own custom developed web service end points.

Known limitations and roadmap:

I believe that with this first implementation of the Survey123 Connector you will be able to do quite a bit, but there are some known limitations I want to highlight:

  • Attachments are not going to be exposed in the Dynamic Content dialog. You cannot reference attachments manually either. We are working towards enabling access to attachments within Flow in a future release (planned for version 3.10). Implemented in 3.10.
  • Data from repeats will also be missing in your Flow at the moment (planned for version 3.10). Implemented in 3.10.
  • The Microsoft Automate connector is not compatible with ArcGIS Enterprise (tentatively planned for Q4 2020)
127 Comments
ZacharyOrdo__GISP
New Contributor III

James, thanks for the information.  Is there any useful documentation or examples on how to do this?

JamesTedrick
Esri Esteemed Contributor

Hi Zachary,

You would need to open the Survey123 website configured to work with your ArcGIS Enterprise implementation; see Use Survey123 with ArcGIS Enterprise—Survey123 for ArcGIS | ArcGIS.  After that, follow the instructions at Webhooks—Survey123 for ArcGIS | ArcGIS.

JamesWilson2
New Contributor III

Hi #ismael chivite‌, can you expand on this please?

- Do I download it from Flow (as a .zip or a .json?) and put it in the survey folder and re-publish? or

- Do I create the flow in Survey123  then press the cloud download icon in Survey123 connect?

I tried the second of these options and can't see any extra files or information, so I don't know if it has done anything

JamesTedrick
Esri Esteemed Contributor

Hi James,

For surveys published by Survey123 Connect - after creating the webhook, please re-download the survey into Survey123 Connect from the web by clicking the 'download; button:

JamesWilson2
New Contributor III

works like a charm, thanks!!

SusanEnyedy_Goldner
Esri Contributor

Hi James, Could you please clarify - can webhooks work with Surveys hosted on ArcGIS Enterprise at this moment? 

Trying to understand from these questions if I can get it going or not. Thank you! Sue

ZacharyOrdo__GISP
New Contributor III

Unfortunately they make it so that you need a Ph.D. in computer science to get anywhere in implementing any of these back-door features.

SusanEnyedy_Goldner
Esri Contributor

Actually I got Survey123 on Enterprise working with webhooks using Integromat just fine -  wondering whether/when it can work with Flow.

VladimirBorek1
New Contributor

Great functionality!

I was able to use the addseconds to convert a date, but can't figure out how to do it when I need date and time.  help would be appreciated.

Thanks!!!

AdminValorInmueble
New Contributor III

HELP! 

my workflow, updates rows in an excel sheet, one of the fields that I require, corresponds to the survey's OBJECTID, however, when writing the following expression

triggerBody()?['feature']?['attributes']?['ObjectID'
I receive a null value.
I do not know if the problem is in the syntax or is a limitation of the survey
I appreciate any help
by Anonymous User
Not applicable

Not sure if this is the issue, but I wonder if it is the case within the word "objectid" that is causing the issues. I looked at a hosted feature service that was created for one of my surveys and within the data tab, take a look at the "Fields" view of the feature table and then click on the field "ObjectID" and you will see the screenshot below. Notice how the word "ObjectID" is referenced as all lower-case.

Also try downloading the data set as an FGDB file and then refer to the field properties to get the name of the field. You will also need to look at the error within your Flow set up to see if it tells you what the error is associated to.

Here is the ArcGIS Online data field information

Here is the hosted feature service downloaded and used within ArcGIS desktop. The field name is all lower case and hopefully that is all you need to change.

Cheers!

Mike

JamesTedrick
Esri Esteemed Contributor

Hi Vladimir,

the addSeconds conversion should also work for a dateTime.  Remember that the timestamp forma of the date is in milliseconds, so it should be divided by 1000 inside the function:

addSeconds('1970-01-01', div(triggerBody()?['feature']?['attributes']?['reportdatetime'] , 1000))

AdminValorInmueble
New Contributor III

Hello Mike, I have tried writing the field in lowercase, but, the result is still empty, some other suggestions,
Thank you. see: 

                                                                                                             

annaAve
New Contributor

Hi!

I am not sure what's going on, but I keep getting this error message in response to clicking the 'sign-in' button on the sign-in dialog box "Failed with error: 'You closed the sign-in window before competing sign-in.' Please sign in again."

Has anyone experienced that? Thank you!

by Anonymous User
Not applicable

Hello Jeffry,

Something looks really odd within your screen shot above that refers to the "GlobalID", "CreationDate", "Creator", "EditDate" and "Editor" fields? The expected values are not what they should be if these are the typical ESRI Fields generated for tracking changes? I am not sure of how to help out this one?

by Anonymous User
Not applicable

Hello Survey123 Team,

 

I was wondering if there is a projected timeline of when we can see MS Flow retain the connection to the ESRI ArcGIS Online account; similar to how Integromat retains it's connection, that was used within the setup? I understand that the two companies have been working on a solution and it would be very helpful to have an idea of when this would be resolved.

 

I am still very hopeful with the abilities that MS Flow has for use within my Survey123 workflow. Any information would be greatly appreciated.

 

Thank you,

Mike

AdminValorInmueble
New Contributor III

Hello mike, sorry, it's correct, the capture presents fields completed at random, I'm using another table to do the test to complete the ObjectId, and show that it is completed with an empty field.

deleted-user-EpRzjYvkmULR
New Contributor III

Any updates on when repeats will be accessible in Flow? I can't use this solution without that functionality.

Thanks for the blog!

KassandraRodriguez
Occasional Contributor

I know you can use Integromat for emailing reports but I'm limited to using Microsoft Flow. Can emailing reports be done through it yet and if so any documentation I can follow?

Thanks

LebogangMashishi1
New Contributor

Hi James,

 Could you kindly help with the exact steps to connect my public facing ArcGIS Enterprise in MS flow. Currently im only getting the option to log into ArcGIS Online(Please see image).

Is there way i can do this in flow or should it be done in Survey123 Settings?is in Flow 

PaulCone2
Occasional Contributor III

I'm trying to edit an automated email flow to include the information about who filled out the survey.  I went to survey123.arcgis.com and edited the webhook, under event data, and ticked the box for User info, but I'm not seeing those fields as being available when I edit the flow.

JamesTedrick
Esri Esteemed Contributor

Hi Lebogang,

Currently the MS Flow connector does not support ArcGIS Enterprise.

JamesTedrick
Esri Esteemed Contributor

Hi Paul,

The MS Flow connector does not support the userInfo component of the webhook payload at this time.

ParasAcharya
New Contributor

James Tedrick

Hi James, 

What is the expression to show labels in the choice question? For example my choice question name is field_4 and choices are choice0 (label-Yes) & choice1(lable-No). Thanks

JamesTedrick
Esri Esteemed Contributor

Hi Paras,

Label information is not sent over - you would need to use the replace function to substitute the labels for the values.  See Functions reference for Workflow Definition Language - Azure Logic Apps | Microsoft Docs 

ParasAcharya
New Contributor

Thanks James. I was able to get it working using "jr:choice-name" expression.

erica_poisson
Occasional Contributor III

Hi James,

I'm sorry if this is redundant, but my organization is just considering upgrading to ArcGIS Enterprise. If we want to maintain our current webhooks, that were created using MS Flow, would they continue to work as expected if the surveys and their data were published into ArcGIS Online instead of Portal?

Thanks,

Erica

JamesTedrick
Esri Esteemed Contributor

Hi Erica,

Yes, the Webhooks URLs would work with forms that have been published to ArcGIS Enterprise as well as Online- submission is done from the field app/webform.  The main issue would be that you would need to manually set the webhook information, as the MS Flow connector does not support ArcGIS Enterprise.  This is probably most easily done by copying the webhook settings from the .info file (the notificationsInfo section) of the existing survey and pasting it into the .info file of the new survey.

PaulCone2
Occasional Contributor III

Thank you James.  Sadly since I attempted that I am no longer able to get a Flow to trigger.  I have gone in and deleted the webhook in the survey and also completely deleted the Flow but I get nothing.  I thought that maybe it was Outlook so I also adding a step using Gmail but that doesn't work either.  Any ideas?

by Anonymous User
Not applicable

I have not been able to retain the connection, but I did find an easier way to re-establish the connection for "all" flows.

https://community.esri.com/thread/231731-updating-flow-connections-with-less-effort 

PaulCone2
Occasional Contributor III

Thank you Mike.  I tried that, and also downloaded the survey again.  Not sure which of those did the trick, but it's working now.

JamesTedrick
Esri Esteemed Contributor

Hi Paul,

One way to test the webhooks is to use the desktop (Windows/Mac/Linux) version of Survey123 with a network proxy such as Charles.  It allows you to view the request to the Webhooks if it is made.  You can also view the webhook settings in the .info file to make sure they are present.

GeoffreyLewis
New Contributor

Is there any update on Repeats being accessible - without repeats it does half of what we want to be be really useful?

KevinBrown10
New Contributor III

I also have this question.  Where you able to get any information regarding generating reports using Microsoft Flow?

KassandraRodriguez
Occasional Contributor

No but I found this Python script John Stowell wrote to generate the report in this forum: Generate Survey123 Report

MeganBirch
New Contributor III

If your Office 365 is in the US Government (GCC) region, this connector is unavailable. This isn't at all clear in the ESRI articles/webinars/etc I've looked at related to this, but it is in the Microsoft Flow documentation.  Hopefully, that will change sometime in the future.

Survey123 - Connectors | Microsoft Docs 

RogerDiercks1
New Contributor III

Thank you, Megan. This explains why as I've posted elsewhere in GeoNet, the Survey123 connector isn't available to me. The strange thing is that it was there a month or so ago when I started testing it.

StevenDel_Castillo
New Contributor III

Hello Ismael. Are we to add jr:choice-name(${activity},'${activity}') on the survey xlsx form or on Flow? In reading the passage in the blog post I am not sure. Thanks for the clarification.

JamesTedrick
Esri Esteemed Contributor

Hi Steven,

jr:choice-name is a function added into the form using Survey123 Connect - it would be a new question added to the form.

JeffJackson4
New Contributor III

We are currently trying to establish an MS Flow to send reports like above.  However, for some reason, the connector does not recognize when a new report is submitted and does not trigger.  If I set up a manual trigger, the flow will connect to Survey123 perfectly well, however, it does not appear that the same options are available for manual triggering as are available for the automatic one and I cannot generate reports.  I only can get information on what surveys are available.

Any idea why this is?

CorrynSmith2
New Contributor III

I attempted to use the example above, but I don't get anything.  I'm on an Enterprise AGOL.  I've tried Creator and Editor as the 'yourhiddenquetionname'.  Any other leads on how I can populate the email or username of who submitted the Survey123? 

CorrynSmith2
New Contributor III

Survey123 Connector is now available for Gov't version of Office365.  I just found this out today from one of my Solution Engineers. 

RussellSturm
New Contributor II

Are there any updates on having MS Flow work with Enterprise?  Integromat is not approved by our corp so we are trying to use MS Flow.  I would like to have a general idea of if/when this will be implemented.

Thanks!

CorrynSmith2
New Contributor III

Hello,

I'm trying to use the hidden field example as displayed in the blog post, but I'm having difficulty pulling out the hidden username.

?['feature']?['attributes']?['yourhiddenquestioname'] 

I attempted to use Anthony Gile's example in this comment thread, however, Flow states there is an error.  Has anyone else been successful pulling out the username for the person who submitted a log entry in Survey123 with Flow?

erica_poisson
Occasional Contributor III

I'm experiencing some inconsistencies with using dynamic content in an email notification (through the Gmail connector). I have one survey with three different webhooks. The survey was built in Connect (v3.5.166) and contains a few different choice lists where the 'name' field contains email addresses, and the 'label' field contains the person's name. I've ensured that within the Survey123 settings, I've set Webhook #1 to 'New record submitted' and Webhook #2 & #3 to 'Existing record edited'.

  1. Webhook #1 is triggered when a new survey is submitted; it generates an email notification if a condition is met. The email address is being pulled from dynamic content (a select_one field) within my survey. This email notification generates perfectly when a new survey is completed.
  2. Webhook #2 is triggered when a survey is edited (my workflow requires that the survey is edited twice - once by a supervisor, and then by a regional director - this webhook is associated with the first round of edits); it generates an email notification if a condition is met. The email addresses here are entered directly (no dynamic content). This email notification also works perfectly.
  3. Webhook #3 Attempt A is triggered when a survey is edited (this webhook is associated with the second round of editing); it should generate an email notification if a condition is met (different from condition in webhook #2). I am attempting to pull the email address from a dynamic field from my survey (select_one - configured the same way within my xls). When I test this, I get an error that there was no email address entered. It seems as if the dynamic content is not coming through. I've ensured my connection with Survey123 through Microsoft Flow is there, but this doesn't change anything. The field I am pulling from has it's choice list set up the same way as the list used successfully in Webhook #1.

  4. Webhook #3 Attempt B I've also tried modifying this webhook to create a Google Calendar event where the email address stored within my dynamic content is in the 'Attendees' section of the "Create an event" connector. I am using the "Convert time zone" connector w/ the function addseconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['Vacation_StartDate'],1000) , 'yyyy/MM/ddTHH:mm:ss') for each my start date and end date. This is the exact expression (with changed field name) that I used in another webhook which is able to successfully create calendar events.
    When I test this, it fails with the following error: Microsoft Flow error InvalidTemplate. Unable to process template language expressions in action 'Convert_time_zone' inputs at line '1' and column '2626': 'The template language function 'Div' expects its first parameter to be an integer or a decimal number. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#mul for usage details.'
  5. Webhook #3 Attempt C I've also tried this with a different function in the "Convert time zone" - formatDateTime(addDays('1970-01-01T00:00:00Z',div(triggerBody()?['feature']?['attributes']?['Vacation_StartDate'],86400)),'yyyy-MM-ddTHH:mm:ss'). This shows that my flow runs successfully, but says that the condition is not met and doesn't create the calendar event, however my condition has in fact been met during the second round of editing.
  6. I've also tacked my calendar event onto my successful Webhook #2 without success. It either causes the entire thing to fail, saying the condition hasn't been met (when addseconds is used), or it runs but only sends the email (when formatDateTime is used).

My questions are -

  • Can one survey support two editing workflows / multiple webhooks?
  • What are potential causes of my dynamic content not successfully populating an email address in the Gmail connector (when it works other times)?
  • Has anyone seen the error associated with my addseconds function?

Any help would be greatly appreciate!

Erica

JamesTedrick
Esri Esteemed Contributor

Hi Erica,

Is the e-mail address one of the fields edited?  Currently, Survey123 does not guarantee the full feature in the payload of an edit event (as the form itself may not reflect the full feature if it is missing fields).

erica_poisson
Occasional Contributor III

Hi James,

No, the email address is input when the survey is initially filled out. When I edit, do all of the fields/information not get pulled into Flow?

JamesTedrick
Esri Esteemed Contributor

Hi Erica,

Currently, the webhook payload will have the edited fields.  We are discussing whether to expand that to the fields in the form; as I mentioned, because that is not guaranteed to be the entire feature, it is in someways safer to treat this as a notification of an update and then query the feature via REST to retrieve the full information.

by Anonymous User
Not applicable

I'm having trouble getting my connection set up and the documentation isn't helping. Would you be able to send your steps to get this set up? Specifically, I'm getting an authorization error after I put in my connection information, App ID, and App Secret

GlenGardner1
New Contributor III

Is there any update on if connecting to enterprise with Flow will be available?

Is there documentation on setting up a webhook connection outside of the connector to check for new record in portal hosted feature service?