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

54901
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
SusanMathai
New Contributor III

Good Stuff!!

GaryBowles1
Occasional Contributor III

Does this work against Portal? If yes, are there any version limitations?

Thanks,

--gary

by Anonymous User
Not applicable

For the road map ahead; could we add in support of pulling data and records from repeats (related features/tables) to the list? It would be great if we could also incorporate this into an upcoming release.

Keep up the great work and thank you!

ElliottPlack1
New Contributor III

When the referenced field has a domain (choice list) attached, should the condition be set to the coded value or the label value?

DelaereMickael
Occasional Contributor II

hello

Nice job!

Does the connector support date fields?

They don't appear in microsoft flow (but i use start,end type of field)

IsmaelChivite
Esri Notable Contributor

Hi. I added a new section in the blog post called Handling Dynamic Content from Survey123. A few tips.  Have a look to learn how to handle dates and hidden questions. Hope it helps!

IsmaelChivite
Esri Notable Contributor

You need to use the name, not the label.

I added a new section in the blog post called Handling Dynamic Content from Survey123. A few tips.  Have a look for details. Hope it helps!

by Anonymous User
Not applicable

I am unsure if this is an expected behavior, but I have been experiencing the following and just made an adjustment to my routine when updating surveys with webhooks enabled.

After posting a survey through Survey123 connect; either adding a new choice(s) or question(s), I have noticed that the web hook will be removed from within the Survey123 website -> Settings -> Webhooks tab. However, if you go into the MS Flow dialog and turn the webhook off and then back on, then the web hook is reinstated for the survey.

Is there any chance of preventing the above described event from happening?

Our field crews and the field supervisors are liking the ability to just "send" a survey and have the auto-generated email(s) sending pertinent information to the right people for project management needs, instead of having to take the time to go back into their notes, construct an email, make sure the information is correct and then send the email. Now they submit the survey and go about the rest of their day.

IsmaelChivite
Esri Notable Contributor

Hi Mike. Here is what I think it is happening:  First, you published a survey from Survey123 Connect. This survey is kept locally in Survey123 Connect (in case you want to make modifications to it), and also published to ArcGIS. The publishing process leaves a copy of your survey as a Form item in your account. When you save your flow, Microsoft Flow saves it along with your Form item in ArcGIS Online, leaving local copy in Survey123 Connect out of sync. Thus, when you make modifications in Connect and then publish the survey again, the webhook definition is wiped out from your ArcGIS Onine item, because the local contents in Connect completely replace your Form item.

To avoid this, I believe you could simply do the following: Once you have registered the webhook with Microsoft Flow, download it into Connect. This will bring the survey along with its webhook definition, so the next time you publish, you will not lose it.

CurtisDeVault
Occasional Contributor

Enabling access to attachments and repeats in Flows would be incredible. Also curious to see what the possibilities are about allowing reports to be generated automatically and sent through Flow as an attachment as well. 

by Anonymous User
Not applicable

Ismael, I will give this a try today and let you know what my experience is. I appreciate the feedback and this makes total sense now.

SuddhaGraves
New Contributor III

Good morning.  I am trying to create a Flow for Survey123, but it must not be accessing my Survey123 credentials.  I am currently signed in to Survey123 and also have previously signed into Flow and had created Webhooks.  Now when I try to choose a Survey123 Form to add the Webhook to, it only displays "No Items" or "Enter custom value".  It should return a list of my Surveys and allow me to select one. 

Also, can someone provide suggestions as to how to download the Flow into Survey Connect so I can publish it along with the Survey?

Any feedback would be appreciated.

TomThompsonEsriCA
Esri Contributor

Great stuff as usual from the Survey123 team. One question, however:

Presently, in Integromat, we're able to define the type of event data sent to the Webhook (Survey Info, Server Response, Portal Info, etc). Does this workflow through the Flow Survey123 Trigger support anything other than just the Submitted Record data?

Part of my workflow involves making HTTP requests to the hosted feature service. I use the Survey Info's serviceUrl property and Portal Info's token property to make these requests against secured services. I don't see these as being possible right now in Flow, hoped for some insight from you guys.

Thanks,

Tom

IsmaelChivite
Esri Notable Contributor

Hi Suddha. This looks like a caching issue on the Microsoft Flow of things.

If you still do not get the list of surveys after refreshing your web browser, try deleting the Survey123 Connection and recreating it. You can manage your connections from the Settings section in Microsoft Flow (it is located near your Profile link).

Learn to connect to your data using connections and on-premises data gateways - Microsoft Flow | Mic... 

IsmaelChivite
Esri Notable Contributor

Hi Tom. Thanks for pointing this out. This is definitively something we would like to bring into the Flow connector at some point but as you mention, it is currently missing. Stay tuned for new updates.

SuddhaGraves
New Contributor III

Hi Ismael,

I was able to go into Settings section in Microsoft Flow and refresh my credentials and it worked.  Thank you!

I am still trying to add Microsoft Flow to my Survey so I can publish them together.  I've searched the threads, but haven't been able to find any documentation on how to do this.  Can you please provide some direction on how to do this?

IsmaelChivite
Esri Notable Contributor

Hi. I am not sure I follow your last question. You do not need to do anything special on your survey to register it with your survey.  Microsoft Flow will do that for you once you save the Flow. Create a Flow, click Save and then load your survey into a fresh new browser window or download it fresh into the Survey123 field app for testing.

You may want to look at the 'Some Additional Technical Details' section in the blog post above.  May that Webhook thing is what got you distracted?

erica_poisson
Occasional Contributor III

I've used your advice on utilizing the addSeconds command to convert my dateTime field into a readable date. My ultimate goal is to take a "start time" and "end time" (start + 20 minutes, calculated in my XLS Form), and use these to create an event in Google Calendar. I'm running into some issues with date formatting and was hoping for some advice, if possible.

My flow workflow is as follows:

When I run the flow, I get an error that my time is malformed (see below for full error):

{
  "error": {
    "errors": [
      {
        "domain""global",
        "reason""invalid",
        "message""Invalid value for: Invalid format: \"2018-08-15 10:00:00Z\" is malformed at \" 10:00:00Z\""
      }
    ],
    "code"400,
    "message""Invalid value for: Invalid format: \"2018-08-15 10:00:00Z\" is malformed at \" 10:00:00Z\""
  }
}
While I realize this isn't a Microsoft Flow forum, I wanted to at least post the question here to see if I could get some suggestions on how to proceed.
Thanks very much!
Erica
IsmaelChivite
Esri Notable Contributor

Hi Erica. The culprit, I am guessing, could be the formatting string. Did you try something like this?

'dd/MM/yyyy HH:mm'

For a complete date-time formatting reference, please refer to Standard Date and Time Format Strings | Microsoft Docs 

erica_poisson
Occasional Contributor III

Ismael, thanks for putting me onto the right track!

I was finally able to get this to work by changing the following parameters:

1) Within Convert time zone, my Format String is now 'yyyy-MM-ddTHH:mm:ss.fffZ'

2) My source and Destination time zones are both set to UTC-05:00 (which seems strange to me, but having a source time zone of UTC, and destination of UTC-05:00 was creating events at the wrong time [4 hours too early]).

One follow-up question for using dynamic content within the Google Calendar connector though - I've selected the Description of my event to contain the result of two fields (text) in my survey, but when the event is created, no value shows up, even though it is required, and not null.

Below is a screen capture of my corrected flow (and what the dynamic content calls look like within the Google Calendar settings:

DominiqueBerger2
New Contributor

Hi All

Has anyone had any success with accessing hidden questions ? I am trying to add the answer to a hidden question to my email body, using the above example of ;

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

that @ Ismael Chivite references in the article. However I have no luck as you can see below; It just comes through with written text. 

Thanks very much!

Dominique 

DelaereMickael
Occasional Contributor II

Hello everybody,

Have used webhooks in flow successfully for one month but does not work anymore for 5 days.

can't see my forms anymore in the drop list of the connector.

Any calls limititations with survey123 connector?

TomThompsonEsriCA
Esri Contributor

Hi Delaere,

Did you republish your survey 5 days ago? Sometimes republishing a survey can erase the webhooks. Have a look at your surveys settings to make sure the flow webhook still exists. 

If it doesn't exist, you'll need to rebuild the connection to Survey123 in MS Flows website.

Tom

DelaereMickael
Occasional Contributor II

Hello Tom,

No i just come back from my holidays.

i'm waiting for an answer from esri france support too.

- i see the webhook in survey123 web

- i see the fields and functions in the flow.

- i can replay old sending messages

But new sendings still don't working

TomThompsonEsriCA
Esri Contributor

Hi Delaere,

Thanks for the reply. Interestingly, this is also happening with one of my surveys as well.

Its very puzzling because I have two flows attached to the same survey (same trigger). When a survey is submitted, one flow is executed, and the second one is not. I have confirmed that the flows are enabled in MS Flow, I have confirmed that the webhook is enabled in Survey123, and I have confirmed that the trigger is set to the correct survey.

Replaying old test triggers works as well. It just doesn't seem to acknowledge when a survey is submitted.

I'll be investigating this throughout the week, if I find a solution I'll let you know.

Do you by chance have two flows attached to the same trigger, or is it just a single flow?

Tom

AnthonyGiles2
Esri Contributor

Dominique,

I have not tried with hidden fields, but I have manged to access the email address within the userInfo (which is not shown in the add dynamic content list) by adding through an Expression:

 

Make sure you add by clicking OK (note: no question marks)

it should appear in the message as follows:

Strange thing is that when you save and reopen the flow it looks like a parameter from the s123 connector:

Regards

Anthony

DelaereMickael
Occasional Contributor II

Hi Tom,

I "save as" the concerned flow and it seems to work again.

But on an other flow (that put an event in a calendar) i did nothing and it works again.

puzzling...

i am conctacting microsoft (france). No  error in the flow, puzzling too.

Mickaël

TomThompsonEsriCA
Esri Contributor

Hi Delaere,

My flows are also both working this morning, I had not changed anything (to my knowledge). What I am seeing now is a second MS Flow trigger in Survey123, though. 

If there was an easy way to see which triggers were associated with which MS Flow connection, I'm sure it would explain why the trigger is now working. I'll keep an eye on these and see if I'm able to better understand this behaviour, but for now everything is working again.

Good luck!

Tom

EricShreve
Occasional Contributor II

I noticed this in Microsoft Flow this morning. I have several Survey123 survey's created and Microsoft is not recognizing them through my ArcGIS  Account. I was doing some testing a couple weeks ago and everything appeared to work correctly now it is not setting any email notifications.

TomThompsonEsriCA
Esri Contributor

Hi Eric,

This is slightly different behavior to what myself and Delaere were seeing. In our scenario, surveys were listed in the Survey123 Flow Trigger, but the flows were not executing.

In your case it looks like either the flow connection to your Survey123 account containing the surveys has an issue, or the Webhook in Survey123 website have been deleted.

I would try rebuilding the connection in Microsoft flow, hit the three dots on the top right corner and select 'Add new connection'.

If this resolves the issue, you can later delete the old, broken Survey123 connection in the Connections menu.

Good luck!

Tom

AndrewMcClary1
New Contributor II

Any word on using it with Portal? I haven't been able to get webhooks to work with our portal yet but hopefully I'm just overlooking a setting somewhere.

Thanks

JamesTedrick
Esri Esteemed Contributor

Hi Andrew,

Currently, the Survey123 Microsoft Flow connector does not support ArcGIS Enterprise/Portal.  We are looking to support it in the future.

DelaereMickael
Occasional Contributor II

Hello Tom,

Everything works well now too (at the same date 23 august).

Note that the flow first started to work with the last updated version of survey123 (3.0.144) when i did some tests on an i pad and a survey123 on windows 10.

But the ms flow did not work on the user phone. The  iphone was disconnected from the apple store so that the automatic update could not execute.

i updated the survey123 version manually on the phone and it started to work fine.

ShelbyZelonisRoberson
Occasional Contributor III

James - it would be soooo helpful to have this work with Enterprise/Portal. Please keep us updated, I'll be anxiously awaiting the functionality! 

by Anonymous User
Not applicable

Hi, this is very nice, with so many possibilities!  I was able to configure the email notification reliably in the case of a very simple survey, and am emboldened to move on to more complex tasks.

I wonder if you can provide any guidance re the adding a row in your SQL Server database?  I am having trouble making a connection to our Azure database with the Flow connection using SQL Server authentication, even though SSMS and Esri data connectors work fine.

Has anyone had success with this that they could share?

Both Flow and Azure are in the raw dev stage for us now, so I am not up to speed with either.

Thanks!

CallumSmith2
Occasional Contributor

Hi James I would also like to see this avaliable in Enterprise. Regards Callum

AdminAccount2
Occasional Contributor II

"Once you have registered the webhook with Microsoft Flow, download it into Connect. This will bring the survey along with its webhook definition, so the next time you publish, you will not lose it."

Hi Ismael - can you expand on this answer? I'm not exactly sure how to download the webhook information into Connect. Currently every time I republish the survey I then have to re-add the connection in Flow, in addition to remembering to delete and re-add the surveys on the mobile devices. 

CHRISJONES10
New Contributor II

How long before Survey123 Microsoft connector will support ArcGIS Enterprise/Portal?

by Anonymous User
Not applicable

If anyone wants to convert UNIX time into a readable date string using Integromat - use a similar expression to what's shown below.

{{formatDate(parseDate(substring(1.feature.attributes.dateTimeFieldName;0;10); "X"); "YYYY-MM-DD hh:mm A")}}

TiLor
by
New Contributor

Hello James - Getting Flow working with Enterprise would be incredible and would get more people here into using Survey123 over other tools. Please keep us informed when something has been worked out to get this running on Enterprise. Thanks.

by Anonymous User
Not applicable

Yes, I concur that Survey123/MS Flow connector supporting Enterprise would be wonderful.  Please keep it on the table!

TredRiggs
New Contributor III

I am trying to define a Survey123 trigger in Microsoft Flow but I get No Results for Your Search.    Is this trigger still available in Microsoft Flow or do I need to upgrade to a higher plan of Microsoft Flow?  Currently I am using Flow Plan 1.

Thanks

JamesTedrick
Esri Esteemed Contributor

Hi Tred,

Which region is your Flow account based in?  We have been working with Microsoft to ensure the connector is deployed across all regions.

TredRiggs
New Contributor III

United States

I decided to try Integromat and it worked perfectly with Survey123. 

Thanks

ChrisMontague-Breakwell
New Contributor III

I'm having the same problem: using the free version of MS Flow in the US doesn't have a Survey123 trigger. 

Chris

IsmaelChivite
Esri Notable Contributor

HI Chris Montague-Breakwell‌    We have communicated this issue to Microsoft and they are working towards having the Survey123 Connector in all regions.

ZacharyOrdo__GISP
New Contributor III

James Tedrick‌ when will Enterprise/Portal functionality be available for MS Flow?  Why aren't your programmers working to ensure their code is modular enough to make these critical features available in tandem across both platforms (both AGOL and Portal) as often as reasonably possible?

ZacharyOrdo__GISP
New Contributor III

Also, James Tedrick‌, should we assume that currently NO webhooks will work with Enterprise/Portal?

JamesTedrick
Esri Esteemed Contributor

Hi Zachary,

Webhooks can work with surveys deployed on ArcGIS Enterprise/Portal.  There are some limitations; in particular, the Enterprise implementation would need to contactable from the Survey123 API (in the public internet) and cannot use IWA authentication.

JamesTedrick
Esri Esteemed Contributor

Hi Zachary,

As my comment below indicates, the webhook capability utilizes API resources that require Survey123 servers to contact an ArcGIS Enterprise deployment.  The decision with the initial release of the MS Flow connector was to approach the least complicated use case, ArcGIS , where the network visibility environment is known.