Survey123 Tricks of the Trade: Working with attachments in Microsoft Power Automate

7219
39
02-25-2021 07:58 PM
IsmaelChivite
Esri Frequent Contributor
6 39 7,219

Survey123 smart forms let you record signatures, photos, annotated images and upload documents into ArcGIS. All of these are stored in ArcGIS as 'attachments'. Microsoft Automate includes a Survey123 connector that helps you access ArcGIS attachments submitted through a smart form.


This blog provides guidance for two common scenarios involving Survey123 attachments and Microsoft Power Automate:

  • Adding multiple Survey123 attachments to an email
  • Uploading Survey123 attachments into Microsoft OneDrive

It is assumed you are already familiar with task automation using Survey123 and Microsoft Power Automate. If so, get started by publishing a survey with an image, signature, file or some other question that uses ArcGIS attachments. Then log into Office 365 and create a new Automated flow with the Survey123 trigger. Follow the instructions below to experiment first hand with Survey123 attachments in Power Automate.

Adding multiple attachments to an email


Survey123 attachments are modeled as an array. In Microsoft Power Automate, you can create an array variable and populate it with Survey123 attachments. Later you can include that array variable in your email.

Immediately after your Survey123 trigger, add the Initialize variable action. We will populate this variable later with the attachments from your survey. Make sure you set the action Type to Array and set a descriptive name. Leave the initial value empty.

Automate_001.png

 Next, add an HTTP action. This will help us download the attachments. Set the Method to POST. For the URI, you will use the Dynamic content dialog and select the feature attachment URL for your question. In my case, the question with attachments in my survey is called photo, so the dynamic content is feature attachments photo URL.

If you are working with a survey that is shared privately, you will also need to add your token as a query parameter. You can get a valid token from the dynamic content dialog (Portal Info Token).

Automate_002.png

Survey123 attachments URLs are modeled as an array because a single question in Survey123 can be used
to capture one or many attachments. Microsoft Automate will recognize your photo URL content as
an array and add an Apply to each block automatically.


Immediately after your HTTP module, add an Append to array variable action.

  • For the Name, select the variable you initialized at the top of your flow.
  • For the value, you need to carefully enter a JSON string specifying the ContentBytes and unique Name of the attachments.
  • Start by adding this JSON string

{
  "ContentBytes": <Your HTTP action Body goes here. Build using an Expression>,
  "Name":<Your feature attachments name goes here. Get from Dynamic Content>
}

  • Now, replace the ContentBytes and Name values as follows:
    • ContentBytes: Use the Expression tab to write the following expression: base64ToBinary (body('HTTP').$content)
    • Name: Use the Dynamic content tab to select the feature attachments name property for the question with your attachments. In my case it was feature attachments photos name.

fow3.gif Make sure you set your JSON value accurately. Do not forget the commas, curly brackets and other things that make JSON be JSON...  Do not forget the comma right after the Body Content!

Add a new Step to send an email.

Automate_004.png

 The key to setting up the Send an email action is to open the Advanced options panel and click the Switch to input entire array icon.

 

Automate_005.png

 You will now be able to use the Dynamic content dialog to select the array variable you setup at the beginning of the flow.

Automate_006.png

Adding Survey123 attachments to Microsoft OneDrive

Next we will have a look at another common scenario where you might want to backup your files, or host them outside ArcGIS so they can be used with other third party systems.

If you want to upload your attachments into Microsoft One Drive, for example, you can use the Upload file from URL action. For the Source URL property, select from the Dynamic content dialog the feature attachments URL array for the appropriate question in your survey. Remember that if working against a private survey you will also need to add your token to the url (?token=[Portal Info Token]). An apply to each block will be added automatically for you. Now all you need to do is to select the Destination File Path for your file. You can select an existing folder, or even create a new one.

Automate_007.png

In the example above, I set the Destination File Path using one of the questions in my survey. The value of my question determines the directory in OneDrive where the file will be stored. If the folder exists, it will use it. Otherwise, it will create a new one. This is an interesting concept, as it helps organizing files nicely: by ZIP
code, by case ID, by field team, etc.

A similar approach can be used to upload files into SharePoint, Google Drive or Microsoft Azure File Storage

 

39 Comments
ChrisWoodward
New Contributor III

@IsmaelChivite - Thanks so much for this resource. We've used power automate with great success and I'm happy to see you helping to advance understanding of this platform coupled with Survey123. What a great combo, thanks!

IsmaelChivite
Esri Frequent Contributor

Hi @ChrisWoodward   Thanks for your comment. Please be aware that the content of this blog applies to an upcoming update of the Survey123 Microsoft Connector. We are a few days behind its planned release and I forgot to delay the scheduled publishing of this article. Ouch!

I will delete this comment once the new connector is updated. The current version of the connector does not expose the token in dynamic content as described in this blog. Only the upcoming update will. We should be able to release the new connector within the next 2 weeks. Fingers crossed!

Hussam_AlJabri
Occasional Contributor II

Can't wait to try the new update! where the attachments is an important elements in our workflow.

erica_tefft
Occasional Contributor III

Hi Ismael,

This new update looks fantasitc - can't wait to try it out.

On a related note - I am wondering if there has been a change to webhooks from Power Automate with Survey123.

Previously, I was able to create multiple webhooks per survey in Power Automate. I'd created them in Power Automate and then see multiple webhooks listed in Survey123 > Settings > Webhooks. Now, when I create my second webhook it appears to 'overwrite' the original. 

Is this a change in behavior for Power Automate? 

MichaelBruening
New Contributor III

@IsmaelChivite when using power automate to upload the photos into the OneDrive application are you simply specifying the folder link within the OneDrive application? Meaning are you simply using a path example like "OneDrive/MyMainFolder/MyProjectFolder/Photos/Feature" or do you need to define the folder using the "encoded" OneDrive values that are all numbers?

Here is an article that shows the numbers I am referring to below (bold text) - https://helpcenter.veeam.com/docs/vbo365/rest/get_onedrives_id_folders_id.html?ver=50

Request:

GET https://support.north.local:4443/v5/RestoreSessions/{restoreSessionId}/Organization/OneDrives/{oneDriveId}/Folders/{folderId}

 

Request Header:

Authorization: Bearer <Access-Token>

 

Response:

200 OK

 

Response Body:

{

 "offset": 0,

 "limit": 30,

 "results": [

  {

   "id": "89e2b52a-66e3-4a1a-a088-4655dfbe95d7",

   "name": "F1",

   "createdBy": "System Account",

   "creationTime": "2017-04-04T12:01:49",

   "modifiedBy": "System Account",

   "modificationTime": "2017-04-04T12:01:49",

   "_links": {

    "self": {

     "href": "https://support.north.local:4443/v5/restoresessions/61954db6-7278-4d26-86f7-888bda5a4ec4/organizatio...b3184e77-b95b-45f7-96cc-180a20b61cbd8507931c-4561-4ddf-bc90-411aba44bb39/folders/89e2b52a-66e3-4a1a-a088-4655dfbe95d7"

    },

    "onedrive": {

     "href": "https://support.north.local:4443/v5/restoresessions/61954db6-7278-4d26-86f7-888bda5a4ec4/organization/onedrives/b3184e77-b95b-45f7-96cc-180a20b61cbd8507931c-4561-4ddf-bc90-411aba44bb39"

    },

    "folders": {

     "href": "https://support.north.local:4443/v5/restoresessions/61954db6-7278-4d26-86f7-888bda5a4ec4/organizatio...b3184e77-b95b-45f7-96cc-180a20b61cbd8507931c-4561-4ddf-bc90-411aba44bb39/folders?parentId=89e2b52a-66e3-4a1a-a088-4655dfbe95d7"

    },

    "documents": {

     "href": "https://support.north.local:4443/v5/restoresessions/61954db6-7278-4d26-86f7-888bda5a4ec4/organizatio...b3184e77-b95b-45f7-96cc-180a20b61cbd8507931c-4561-4ddf-bc90-411aba44bb39/documents?parentId=89e2b52a-66e3-4a1a-a088-4655dfbe95d7"

    }

   }

  }

}

JamesChan1
New Contributor

Hello Ismael,

Great write-up! Is it possible to send PDF (.pdf) files as an attachment to an email using Power Automate and Survey123?

I have successfully used Power Automate to send emails with photo (.jpg) and MS Word (.docx) attachments...but we have been unsuccessful using automate to send PDF files.  The PDF files are "corrupt"/unopenable when we receive them via the automate email.

I hope the answer is yes...and you can help.

Thank you

IsmaelChivite
Esri Frequent Contributor

@JamesChan1I am not aware of any limitation in Power Automate in regards with attaching PDF files. This would be more of a question for Microsoft. 

IsmaelChivite
Esri Frequent Contributor

@MichaelBruening  In my own experience I have been able to specify the target folder in OnDrive using human friendly paths.  I was even able to use dynamic content to define the target folder. For example, say you want photos to go into a folder for every user. You can get the username value from userInfo and pass it as a parameter to construct the target path.

IsmaelChivite
Esri Frequent Contributor
JamesChan1
New Contributor

Hello Ismael,

Just to clarify...

I am trying to include Survey123 FILE attachments in PDF format in an automated email notification using Power Automate.  I am following the ESRI instructions here:

https://support.esri.com/en/technical-article/000024579

Those instructions and your write up focus on adding photo attachments. Do you have any information on successfully attaching PDF files to emails from Survey123 using automate?  Does this still sound like a Microsoft limitation?

Thank you

MichaelBruening
New Contributor III

@JamesChan1I have not tried this within Power Automate as of yet.

However, when working within Integromat I had to make a call through the API for the DOCX file format and then make a call to CloudConvert. This was over a year ago and since then they have released documentation on the Survey123 reporting API.

You should be able to accomplish the attaching of a PDF file using the following API documentation within this blog through an HTTP request. You would use the output format of PDF instead of DOCX. 

https://developers.arcgis.com/survey123/api-reference/rest/report/

MichaelBruening
New Contributor III

@IsmaelChivitethis is great news. Being able to use "human-friendly" paths will definitely make things easier than it was within Integromat. It was a process when trying to reconstructing the OneDrive path using the "computer-friendly" folder ids for my scenarios. 

They'll just be a small transition period of relearning how to manipulate the flows to do what I had accomplished within Integromat. Always fun to learn new ways. 

JamesChan1
New Contributor

Hello @IsmaelChivite and @MichaelBruening ,

UPDATE:

Thank you for all your help.  I solved my issue.  Turns out that my problem was user error related.  Survey123 and Power Automate work great. Automated emails with attachments is a huge time saver for us.  Using your guidance I successfully atached jpg, docx, AND pdf's. Keep up the good work.

WillieChoi
New Contributor

@IsmaelChivite , can you provide an example of uploading file to SharePoint list? I'm having trouble figuring out what value I should provide for the file content parameter of the Add Attachment action?

ChrisVandenberg
New Contributor III

@IsmaelChivite , can you provide an example on how to specify the target folder in OneDrive using human friendly paths? I'd like to save photos to go into a separate folder for every user then sub folder for a specific record. I'm a little lost on how to get the username value from userInfo and pass it as a parameter to construct the target path.

Thanks for all of your helpful content and guidance! 

MichaelBruening
New Contributor III

@ChrisVandenberg The idea is that you are replacing a portion of the path to the OneDrive folder with certain "questions" from your survey. In the last example above, the "Destination File Path" is using the answer from two separate questions to create or deposit photos into the OneDrive system.

Within the "Upload From URL" module, you would click into the "Source URL" and then within the "Dynamic Content" dialog, you could do a search for "attachments" and then from the resulting list choose the "feature attachments "Name of your question that allows attachments" URL".

Then you would click into the "destination File Path" and then from the "Dynamic Content" dialog you would then choose the questions that you want to use in constructing folder path of where the photos would be uploaded to. If you want to use the "username", then you could do a search for "username", then choose the "User Info Username" content, then place a "/" and then choose the next question that you want to use within the folder path that is being created.

For example if you want to use the "username" and then an question for a "Unique ID", then within the "Destination File Path" you would select from the "Dynamic Content" dialog the "User Info Username", then type a "/", then select from the "Dynamic Content" the "Unique ID question".

Select the URL -

MichaelBruening_3-1618410104854.png

Select the Username information

MichaelBruening_2-1618410014615.png

Add in the "/" with the next question within the folder path.

MichaelBruening_0-1618409914093.png

You can add as many questions as you want, but you will have to determine this based on how your survey is setup and how you want to divide up the photos.

I hope this help you and once you start to understand how things work, then you can do some really great things.

 

CameronRex1
New Contributor

@IsmaelChivite, for uploading attachments (in this case images) to OneDrive, is there a way to increment the file name in order to support multiple images that reside in a "multiline" Survey123 image field?

In the example shown, the flow fails if the attachment has more than one photo (if overwrite is disabled) or only the last photo of a "multiline" enabled image field (if overwrite is enabled).

I currently have the Destination File Path set as [FormName]/[ResponseName].jpg, but this results in flow failure or overwriting the image if multiple images exist in a multiline image field.

I'm trying to figure out how to make the Destination File Path set as [FormName]/[ResponseName]-[IncrementedValue].jpg

On a side note, in the example, for images, shouldn't ".jpg" be appended to the end of the Destination File Path, otherwise it is saved without a file extension.

CameronRex1
New Contributor

@IsmaelChivite, I answered my own question.

For multiple images contained within a "multilline" enabled image field, the easiest method is to Initialize a variable, say "photoNum" outside of the Apply to each. Then in the Apply to each, use the same Upload file from URL. Set the Destination Path with [Directory]/[Name]-{photoNum}.jpg and then after the Upload file from URL, but within the Apply to each, add Increment variable with the Name set to your variable "photoNum" and Value to 1.

MPA_S123_Increment_Filename.jpg

Note: My Survey123 icons look different because I have it set up as an Enterprise Connector. Just in case anyone is confused.

 I hope this helps.

QPWS_SpatialServices
New Contributor II

@IsmaelChivite,

The Power Automate to generate an e-mail didn't work when we tried.  However, by adding the objectIds= parameter to the HTTP POST request to get the attachments and converting the "body('HTTP').$content from base64 to binary the attached images were in the correct binary format in the email.
Query Attachments (Feature Service/Layer)—ArcGIS REST API | ArcGIS for Developers
Handle Base64 and Binary File Content Types in Power Automate (linnzawwin.blogspot.com)

Thanks

David

DavidClark9
New Contributor

@IsmaelChivite,

One other point is that if the survey doesn't have any attachment the flow will fail as it is expecting a list (even an empty list would do), instead of null.  To handle this you need to put in condition like "equals(triggerOutputs()?['body/feature/attachments/photos'],null)" and only process the attachments into the array of attachments if this value is not equal to true or is equal to false.  The other path through the condition doesn't need to do anything as the array variable will be empty and no attachments will be added to the e-mail.

BobSteiss
New Contributor III

Hello.  I have set up a flow using Ismael's example and it works great to send my 2 jpeg photo attachments collected using survey123, via email, to my email account, however, I cannot open the jpegs.

BobSteiss_0-1619481369663.png

I tried saving them to my desktop and opening them from there but got the same message.

Has anyone else experienced this issue and if so, can I ask how you solved it?  Could you please share the code you added or a picture of what you had to add to Ismael's flow to keep flow from corrupting the jpegs?  The photos look great in survey123.arcgis.com so I am pretty sure the problem is occuring during the flow.  Thank you.

 

DavidClark9
New Contributor

@BobSteiss 

As noted above you need to use the base64ToBinary function i.e."base64ToBinary (body('HTTP').$content)".  Image files are binary but the data is transmitted via HTTP as base64.

Thanks,

David.

BobSteiss
New Contributor III

Thanks David.  I inserted the code you provided and now the attachments are coming through great.

DavidClark9
New Contributor

@IsmaelChivite,

Is it possible to include the map that displays in Survey123 when you enter the coordinate?  It would be nice to show this in the e-mail.

Thanks,

David.

joncroley
New Contributor

@IsmaelChivite 

I followed the process, however when I execute the flow it will upload the files to OneDrive, but when one tries to view the photos or even view them it states the file format cannot be displayed. There are multiple images that could be with the survey (max of 5). Am I missing something?

Screenshot 1.jpgScreenshot 2.jpg

  

IsmaelChivite
Esri Frequent Contributor

@joncroleyYou were not missing anything. I was! In the original text of the article, the expression for contentBytes was incorrect. It was body('HTTP').$content but it should be base64ToBinary (body('HTTP').$content).

I edited the text and animation to reflect the correct syntax. Sorry about that.  @DavidClark9 and @QPWS_SpatialServices already had pointed that out in comments.

IsmaelChivite
Esri Frequent Contributor

@DavidClark9  I have not figured out how to include a static map in the email. However, you could include a link to a web map and use URL parameters to center the map, set the zoom level and even add a marker symbol with a nice popup.  This help topic provides details on how to manipulate the map viewer to do of all that. Hope it helps, and thanks for the catch with base64ToBinary!

MichaelBruening
New Contributor III

@IsmaelChivite would there be a way of using an HTTP POST call through the REST API to retrieve the map image; kind of similar to the Custom Report functionality for Survey123? I would try to look this up, but it appears as though the developers.arcgis.com website is malfunctioning currently or there is a new website that I am not using?

MichaelBruening_0-1620649746409.png

 

QPWS_SpatialServices
New Contributor II

@MichaelBruening getting a simple example of an http call that works to get the feature report would be ideal. This would have the map and the incident report formatted correctly. Unfortunately there are lots of half threads and hints on how to do this but no one has posted a complete example.

https://developers.arcgis.com/survey123/api-reference/rest/report/

 

MichaelBruening
New Contributor III

@QPWS_SpatialServices I believe the Survey123 team has talked about creating a "report" widget for the MS Power Automate portal, but I am not a hundred percent sure when they would have it completed?

However, if you are not married to MS Power Automate, then you should take a look at the Integromat website for this capability. You would be able to use the Survey123 connector within this program for requesting the Custom Report for a Survey123 submittal. The setup is extremely easy due to the amount of time the Survey123 put forth on developing the two different connectors with Integromat. The scenario is basically four connectors;

1. Survey123 Watch Survey = a connector that you choose your survey123 form within and then define which event (add data or edit data) to listen for on submittal of a form. 

2. Create Report = is the module that you define which custom report template for the survey you have chosen that will be used to create the report.

3. HTTP request = using a "GET" request and using the "resultInfo" resultFiles[1]: url" dynamic content from your first survey123 module.

4. Your email program with the Subject, Body Content (html encoded use <br> for hard returns), Email Address(es), and then for the attachments you would use the "data" from the HTTP module and then the "resultInfo: title" from the dynamic content and also add the following text directly behind it, ".docx" for a Word Doc or ".pdf" for a PDF file.

If you cannot use the Integromat website, then there will be a need to reconstruct an HTTP call to the ArcGIS REST Service. This can be a little complex, but once you complete one, then it will be easier. 

I thought I remembered a blog post about how to achieve the custom report generation using MS Flow/Power Automate and there was one. The link below should help walk you through the process.

https://community.esri.com/t5/arcgis-survey123-blog/power-automate-html-survey123-pdf-report/ba-p/89...

I also would HIGHLY recommend taking some time to scroll through all of the blog posting as they generally have A LOT of helpful materials within the blog and also the comments associated to them. Tons of information within them.

https://community.esri.com/t5/arcgis-survey123-blog/bg-p/arcgis-survey123-blog

I also love to Google different ideas that I have for achieving certain things and you can usually find enough pieces from different articles, posts, or how-to's to help complete what you want to do.

I hope this works for you and Good Luck!

Mike

QPWS_SpatialServices
New Contributor II

@MichaelBruening thanks for your comments and thoughts. So, we were using Integromat until it was deemed not secure by our IT agency. Hence being forced into the joys of Microsoft!

Having said that, I have my Integromat scenarios failing now with gmail authentication so I am not sure it is a much better solution for us!

We do use FME also for another survey but I don't have as much skill there with configuring the body of the email or working out how to get the attachments etc.

I have looked at those articles that you reference and it became a bit complex for me (simple GIS Manager) but I may get one of my team members to take a look @DavidClark9 seeing as he solved the binary riddle!

José_LuisCc
New Contributor II

Hola @IsmaelChivite ,

he seguido paso a paso tus instructiones pero llego a un punto que no entiendo. Es al momento de enviar el correo.

José_LuisCastro_Castillo_0-1620850423672.png

José_LuisCastro_Castillo_1-1620850484930.png

Basicamente dice que no ha recibido ningun parametro.

El problema se produce cuando hago Switch para cargar la variable exactamente.

José_LuisCastro_Castillo_2-1620850988932.png

si NO activo el switch  y no cargo la variable, el flujo funciona correctamente...... pero sin archivo adjunto.:face_with_rolling_eyes:

 

 

 

 

 

José_LuisCc
New Contributor II

@José_LuisCc 

@IsmaelChivite He encontrado la causa : 

José_LuisCc_1-1620922691851.png

Ha funcionado sin hacer la conversion

José_LuisCc_0-1620922547519.png

Gracias @QPWS_SpatialServices 

ChrisVandenberg
New Contributor III

@MichaelBruening @IsmaelChivite 

Thanks for your help! I was able to get the flow working to save photos in nested folders. I'm wondering if you could provide some insight on how to execute this flow when the survey photos are in a repeat. I'm having trouble getting the flow to work properly in this case. I believe there is an issue with what I have entered in one (or both) of the apply to each connectors. Thanks again for any insights you may have.

 

ChrisVandenberg_0-1625836032258.png

 

MichaelBruening
New Contributor III

@ChrisVandenberg I have not worked with Power Automate as much as Integromat. I would imagine that you would have to recreate the API call of "Query Related Records" and then Query their related Attachments.

For the second repeat you would have to "Query Related Records" and then query the related records of the first repeat, and then finally query the attachments of those related records. Keep in mind that you would have to iterate through each level of the each repeat in order to get all of the related records. I know within Integromat that it takes a little doing, but I am unsure of the correct workflow within Power Automate.

From your screen shot it appears as though "apply to each" is similar to an "iterate" within Integromat? It looks as though you may need to the HTTP and append Array modules added into your flow, similar to the what Ismail demonstrates within the article?

Sorry I could not provide a definitive answer.

ChrisVandenberg
New Contributor III

@IsmaelChivite Thank you for the great power automate solutions!  I'm curious if you have any insights on how to save survey123 photos to onedrive when they are updated via inbox?

MichaelBruening
New Contributor III

@ChrisVandenbergwithin the webhook you should be able to define which action the webhook is listening for "create" or "edit" when the survey is submitted. The "create" option is when the Survey123 form is submitting "new" data, while the "edit" is for when the Survey123 form is submitting previously submitted data.

Again, it has been a minute since using power automate, but there is a setting either within the module or you may have to go directly into the Survey123 website, click on the survey, then go into the "setting" tab, then find the webhook from Power Automate, and update the "trigger events" to include the "Existing record Edit".

FrankMartin1
New Contributor III

@JamesChan1 ,

Could you fill me in on your user error.   I seem to be having the same issue in attaching files such as excel and pdf files.  Wonder if I making the same mistake.  :)

Thanks,

Frank

FrankMartin1
New Contributor III

Does anyone know if this base64ToBinary (body('HTTP').$content) works with other attachment types such as pdf, docx, or xlsx formats?  Are all attachment stored in the same format ( base64) as the photo?  This command for accessing the content doesn't work for me when try to attach other media types.

Thanks,

Frank