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

54890
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
ShawnPOWER
New Contributor III

Integromat seems to work with on-premises ArcGIS Enterprise and has a way to trigger the report generation which is nice. Any idea when the Microsoft Flow connector will allow for on-premises ArcGIS Enterprise connectivity? And as a bonus trigger the report generation?

JamesTedrick
Esri Esteemed Contributor

Hi Shawn,

Built-in support for ArcGIS Enterprise is currently not planned for the Survey123 MS Flow connector, as support for other Portal URLs cannot be defined within the Flow connector.  The Survey123 team is exploring other ways to make a connector available for MS Flow users, but we do not have a definite timeline yet.

JackiePhipps
New Contributor II

Personally, it would be helpful if when you resubmitted the form to expand that to the fields in the form, or at least the objectId to be able to link people to the form/maps/apps it may be supporting. Do you have a suggestion for helping pass some of the non-edited field information along in the Flow automated emails on a re-submission? 

AntonioRodrigues_Martins_Junio
Esri Contributor

Hi James, Have we had some improvements related to support ArcGIS Enterprise for the Survey123 MS Flow connector? or still not supported?

Thank you!

Antonio Junior.

JamesTedrick
Esri Esteemed Contributor

Hi Antonio,

We have been researching/testing support for ArcGIS Enterprise, though some issues have been discovered.  It is still not supported.

DerekKonofalski
Occasional Contributor

Does the connector currently work when an entry is edited if it was submitted prior to the flow being created? I have a survey that has existing entries in it and I've gone into Survey123 on the web and enabled the option to trigger when items are edited but nothing gets triggered when I go to the data tab and make changes. Does this only work for entries that were added after the connector was put in place?

by Anonymous User
Not applicable

Hi - I have created a connection between Microsoft Flow, Survey123 and an excel sheet. I have successfully set up in Flow for when a record is submitted in survey123 and a new row is added in excel. I am looking though to edit a row in excel based on when a survey is edited and resubmitted without setting up a trigger question in the survey itself. From reading this forum, I see I can create a webhook for when a survey is edited and resubmitted, but I am not sure how to create this new webhook so that flow can capture and edit the row in excel and feed it from the survey to flow then to excel. What is the best way using Flow and Survey123 to be able to update the excel workbook when a survey is resubmitted? Is this possible? 

JamesTedrick
Esri Esteemed Contributor

Hi Rayna,

Looking at Excel's documentation in Power Automate (Excel - Connectors | Microsoft Docs ), it looks like it should be possible, though apparently an entire row needs to be updated; you may end up using a pattern of getting the existing row from the sheet > altering the values > updating the row to implement this.

bbrown
by
New Contributor

Is it possible yet to add attachments from Survey123 forms as Dynamic Content when sending an email? If not, is there a viable workaround or better way of doing it? We're currently using the free Microsoft Power Automate version. 

JamesTedrick
Esri Esteemed Contributor

Hi Becky,

Attachment access is still not available in Power Automate at this time.

KevinG
by
New Contributor II

I haven’t been able to get the metadata fields to populate in the spreadsheet as described above. Using the triggerBody()?['feature']?['attributes']?['Creator'] syntax as an expression doesn’t throw any errors, but I get a null value in the spreadsheet. I have tried using lower case creator as well with the same result.

DerekKonofalski
Occasional Contributor

I noticed that the webhook created by Power Automate/Flow runs when a new survey is submitted but Survey123 gives the option, in the Webhook description, to allow for it to be triggered when the form is edited also. In this case, though, only the edited data is passed to the Webhook so my workflow runs but only with certain fields. Is it possible to set this up in a way that all the data from the form is passed to the Webhook on edits or can we put in a request to have another action type added where we can choose to include just the edited data or all the data?

DerekKonofalski
Occasional Contributor

To clarify, I'd like to use the logic operators and conditionals in Flow to determine what to do with the data rather than just having the edited data passed through. In order for that to work, though, I'd need all the fields sent to the Webhook on an initial submit but also on a edited submit.

IsmaelChivite
Esri Notable Contributor

Hi Derek. Thanks for your clarification. The behavior you describe is a software defect. We are currently working on ensuring that the webhook payload on edit events includes all fields. The is tentatively scheduled for release with version 3.10, which is planned to be available in late July 2020.

erica_poisson
Occasional Contributor III

Hi Derek,

We have been able to use this blog from Esri Canada to get around the issue you're describing in Power Automate - Webhooks, Microsoft Flow and the ArcGIS Rest API – Automating Your Web GIS 

Although it describes altering another hosted feature service, we've been able to modify it so that this works on the survey's hosted feature service. I think it should help you accomplish your goals.

Erica

DerekKonofalski
Occasional Contributor

Thanks, Ismael!  Will this fix be available in beta versions so that I can test to make sure it works?

DerekKonofalski
Occasional Contributor

So you're using the API to pull the additional information directly from the Feature and then just appending that to the edited info? I can wait for the fix to be implemented but it's good to have this in my back pocket for any other Flows. I didn't even know there was a Parse JSON operation. Thanks for the heads up!

erica_poisson
Occasional Contributor III

Yes; I use the API to grab all of the non-edited fields I need to support logic within my Flows. I don't use it to get every field in the hosted feature layer, but you could do that (as it sounds like that is what you're describing above).

Once note is that the HTTP connector in Power Automate is a "Premium" connector; it is available via 90 day free trial, but you'd need to upgrade your Power Automate account in the long run. The Parse JSON connector is not "Premium".

DerekKonofalski
Occasional Contributor

That shouldn't be an issue. We're already on Premium for the MS Word operations. Thanks again!

DavidMcClennen
New Contributor III

Hi!

I'm actually having trouble with this flow and cannot figure out what I'm doing wrong here.  maybe you can help. The flow we're trying to implement here is updating a parcels layer with from a damage assessment survey. We're using html  in the popups in the fieldworkers map to pass values automatically to the survey123 form to expedite the assessment process and mitigate data entry errors.

Main parcels layer (noneditable) (data) > surveyform > (power automate workflow updates 2nd parcels layer (editing enabled)

for the flow we only reference the 2nd parcels layer and are trying to use the HCAD number (Parcelid which is a string) to relate the submitted data to the target data. but we keep getting this error. Is it because the parcelids in the form and 2nd layers are strings? or am i missing something else?

NirmalOjha1
New Contributor III

I have added the custom connector for ArcGIS Enterprise following the article on earlyadopter. However, I do not get any variable exposed to it. When I tried to save the Flow it gave an error message:


The response from API 'arcgis-20enterprise-20connector-5f932af272f3911-5b5e745791d2b699' operation 'GetWebhookPayloadSchema' with status code '200' does not contain a valid OpenAPI schema object.

Any way this can be fixed?

Thanks.

Nirmal

JulieK
by
Occasional Contributor

Hello,

Were you able to generate token for a secured service in Power automate? @IsmaelChivite @tomthompson3 

Thanks,

Julie

RobertAnderson3
MVP Regular Contributor

Hi @IsmaelChivite do you happen to have a walkthrough somewhere that can show how to set up repeat data in the webook? I am having difficulties figuring out how to make it work.

JesusVelasquez
New Contributor

Has anyone had any issues in the string being cutoff in the email body for the dynamic features being added from survey123?

FlorenceRaimbault
New Contributor III

Hi @JamesTedrick @IsmaelChivite 

When we use Power automate to send info from a survye123 , we Need to  use the AGOL account of the owner of the survey123.

In Power automate usually we know the owner of all the Microsoft's connectors but not for the Survey123, for example I have more than 1 user for the teams connexion ( see image Below). 

When we use the Survey123 connector we can't discriminate the owner of the account. In fact, I can't user more the one AGOL account with all Survey123 connector. I try to make a new connection with another account, but the account is still with my name in the detail

Is there a way for ESRI to display the information, and can we use more than one Account for the survey123 connexion?
It's the same for the new ArcGIS connector ;(.

Florence


power_conector.jpg

 

OgunfolajiDare
New Contributor II

Dynamic content not creating for Survey123 fields

I am working with Power Automate for the first time, hoping to automate the process of taking data collected in Survey123 and automatically populating an excel spreadsheet. I've been following this workflow:

https://www.esri.com/arcgis-blog/products/survey123/constituent-engagement/instantly-load-survey123-...

 

However, even though I believe I've set the table up correctly (and it's clearly pulling in the column names) I am not able to enter any field values from dynamic content.

OgunfolajiDare_0-1685106201317.jpeg

 

RobertAnderson3
MVP Regular Contributor

@OgunfolajiDare 

I've encountered this issue before when I do not have a label on a group or repeat so make sure every item has one.

Aside from that I would just recommend refreshing the connection by logging into it again.