topic Re: Guide: Working with Nested Repeats in Power Automate in ArcGIS Survey123 Questions

Guide: Working with Nested Repeats in Power Automate

abureaux — Fri, 31 May 2024 17:43:21 GMT

My parent thread with additional Power Automate Guides

Working with repeats in Power Automate isn't always intuitive, but it is doable. And once you learn a few basics, you should have no problem transforming your data the way you need.

Goal: Enter repeat data from Survey123 submission into an Excel table without duplicates.

First, I need a survey with a nested repeat:

Next, I need a place to dump my data. I will be using Excel, but you could also use a database, SharePoint, or even an email with an HTML table--There are a ton of options. Whichever option you choose, the steps to get there are still the same.

Now, for the flow. Go to Power Automate > New flow > Instant cloud flow > Skip > turn off 'New designer' (located top right). You should see this:

Select your trigger: When a survey response is submitted. Then, select your survey. This is the ONLY difference between this flow in AGO and Enterprise (AGO connector for AGO, custom Enterprise connector for Enterprise... at least for now). Also, I added corporate colours and a random icon to my custom Enterprise connection, so it will look different than yours. That's okay! They are all the same connection.

Add a Parse JSON step > format like below > save your flow > turn on your flow (if not done so already):

Go into Survey123 and ensure you download any pending updates for your survey. Then, fill out all the fields you care about, ensuring you only add a single item to each repeat. Finally, submit the survey. NOTE: Adding the When a survey response is submitted step in Power Automate adds a webhook to your survey, which in turn results in a pending update for that same survey. You need this update for the flow to work.

Go back to Power Automate and open the flow that just ran

Open your trigger and copy the body. Before you ask, we are doing this because working with the Parse JSON step, rather than the body directly, makes a lot life easier. For example, we can trim the content down to exactly what we need.

Open your favourite script editor (HINT: It's VS Code) and paste in the body. Then trim it down to contain only the fields you care about. At a minimum, you will need all "uniquerowid" and "parentrowid" fields, as well as the "attributes" blocks with some attributes (you know, the stuff going into your table/database). Don't forget to replace all the text with the word "null" as I have below. This can/will save future headaches. I don't always make uniquerowid and parentrowid fields null right away, but I do when I am ready to move into production. Reason is that if you select a string in VS Code, it highlights matching strings... This lets you see the connection between the different ids!

uniquerowid is always unique
parentrowid is only found in children (e.g., a repeat), and it always matches it's parent's uniquerowid

{ "feature": { "attributes": { "m_date": null, "m_person": null, "uniquerowid": "{24BECBC8-F8A8-46CC-9AFD-990ABDCB80D7}" }, "result": { "objectId": null }, "repeats": { "r1_survey_rpt": [ { "attributes": { "r1_block": null, "r1_lot": null, "r1_plan": null, "parentrowid": "{24BECBC8-F8A8-46CC-9AFD-990ABDCB80D7}", "uniquerowid": "{F41E1C35-CB5C-4AB0-B8E5-A5009C90B61B}" }, "repeats": { "r2_tree_rpt": [ { "attributes": { "r2_tree_sct": null, "parentrowid": "{F41E1C35-CB5C-4AB0-B8E5-A5009C90B61B}" } } ] } } ] } } }

Copy what you have in VS Code (or copy mine from above if you are replicating what I am doing). In Power Automate: Go to Parse JSON > click Generate from sample > paste in the copied text > press Done

Now for the fun part. We want to add rows to our table/database based on the most nested repeat. In my case, that's ${r2_tree_rpt}.

Add Condition. As we work our way down to the most nested repeat, we need to start at the "top" (aka, the main Feature Layer) and descend from there. Here I have circled the parameters in the order that they will be used:

Now, you might be asking yourself "which is which? They all look the same in Power Automate!" Well, Power Automate adds things to the Dynamic Content list in order. Go to your JSON in VS Code (or just look at mine above). The first uniquerowid in VS Code is the top result in Power Automate's Dynamic Content.

In your Condition: On the left, add uniquerowid, and on the right, add parentrowid.

As soon as you add parentrowid to the Condition, Power Automate adds an Apply to Each for you. That is what we want. Just ensure you got the correct parameters by hovering over them and checking their path.

Now, we do the same for the next level. On the "YES" side of our Condition, we add another Condition: On the left, add uniquerowid, and on the right, add parentrowid.

And again, if you want to confirm your selections:

Final step is to add your data transformation step. As I mentioned earlier, I am dumping my data into Excel:

That's it!

If we test, there should be zero duplicates (trust me when I say that there are no duplicates in this screen cap).

*** One small note. If you test on Excel, just know that SharePoint can be slow to realize data has been added. It took ~20 seconds for my flow to run, but then another 1 minute before my data actually appeared in the table.

I have attached a couple files to help. One is the XLSX that I used here. The other is a high-level overview of the end result compared to VS Code.

Re: Guide: Working with Nested Repeats in Power Automate

Juan_FelipeVillegas — Fri, 21 Jun 2024 21:45:28 GMT

Hi.

There's somethin that im not getting.

Do i have to change the word GlobalID for the word uniquerowid and ParentGlobalID for parentrowid in the body.

The structure of text that i have copied from the body is identical to what you have with my particular fields , but these two fields are different.

Regards

Re: Guide: Working with Nested Repeats in Power Automate

WilderNiss1 — Tue, 17 Dec 2024 19:22:11 GMT

Thanks very much to this guide! You mention above to fill out the survey including "all fields you care about". How should we approach this if our survey has two "paths" depending on an answer and relevant fields? I care about both paths, but it isn't possible to fill out a survey both ways. How can all those fields be captured in a parse JSON step? For example: Surveyor answers a question about the status of a nest. If they select that the nest fails, they are asked about options on how it may have failed (human, predator, weather, etc.). But if they select active, they are asked questions about how many eggs are in the nest. Is it possible to structure an email that would report both fields (from the JSON parsed out) in an email out to surveyors? Thanks!

Re: Guide: Working with Nested Repeats in Power Automate

abureaux — Tue, 17 Dec 2024 20:05:37 GMT

That's a great question!

I have a few like that as well. Honestly, adding data to the fields is just a nice quality of life thing. You can also do it manually.

Here are a few options:

Submit the survey twice, and make two separate JSON files for the two separate paths. You can use the "Switch" step in Power Automate to create a branched flow. This is likely the most straight forward approach as it replicates how your survey is set up. I put an example of this below. Depending on how your survey is set up though, the next option may be better.
Submit a survey for "Path A" where you fill in all the applicable fields. Then, submit a survey for "Path B" were you follow the same procedure. And finally, combine the two separate JSON into a single block of code. As long as you drop things into the correct spots, this works well. While similar to the first option, this option would keep the entire flow in a "single direction". Depending on how things are set up, this may be a better idea.
Submit a survey for "Path A" where you fill in all the applicable fields. Then, manually add the missing content to the JSON. This is similar to the previous option, but is just more manual.

In any case, make extra sure you set all the values to "null" in the JSON (as per the steps in my original post). Power Automate gets really grouchy when it expects a specific data type just to be fed nothing.

If you go with the branched approach, you may end up having two separate emails. In that case, it is likely straight forward what to do. However, even when I use a branched approach to a flow, I tend to share as many of the common elements as possible to avoid making extra work for myself. In many cases, that means I share the final email. If that ends up being the case for you, I'd suggest you use "Initialize Variable" to set a text variable. You can then "Set Variable" with each of the branches, and then call upon that variable outside the branches. Here is a quick example that may help illustrate what I mean:

(Note: You may also be able to substitute the "Condition" step for the "Switch" step. If you only have two branches, they essentially do the same thing)

Re: Guide: Working with Nested Repeats in Power Automate

WilderNiss1 — Wed, 18 Dec 2024 15:55:05 GMT

@abureaux Thanks for taking the time to provide such a thorough answer! I actually discovered in between the time that I asked this question and when you answered it that a parseJSON will still show all the "not relevant" fields in the output as null, even if the surveyor never saw those questions (e.g. they were on a different path). So it doesn't appear that the manual step of combining them would be necessary? At least it doesn't appear so for me.

A note on this, I don't have any nested repeats, I was just trying to learn how to do the JSON action so I can better understand what my variables are because they are so dang hard to read in the dynamic content viewer in Power Automate. All I am trying to do is create an email that includes all the filled in contents of a repeat (in fact of a couple different repeats). So in a separate branch of the flow I've used the "Initialize Variable" step to create an array, then set up an "Apply to each" around "Append to Array variable" for the fields in my repeat. I got those steps from this guide https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-working-with/ba-p/1371206. I am hoping to create parallel branches of this for each separate repeat. Then I just need to figure out how add that array into the body of my email, since I am not working with attachments as that example explains. You go over how to add the repeat data into an excel, but what about if you just want to display it in an email out to a group of people?

Thanks for the explanation of the switch, I wasn't aware of that capability, and even if I don't use it for this survey I may in the future and others will hopefully find it useful as well.

Re: Guide: Working with Nested Repeats in Power Automate

abureaux — Wed, 18 Dec 2024 16:30:53 GMT

Ahhh, okay. That makes more sense!

Ya, using ParseJSON makes life 1000% easier, and for that reason I use this action on all of my S123 flows to parse out that initial S123 response.

The easiest "array" you can add to an email would probably be the Create HTML Table action. Without knowing what the array contains, it's hard to say though. To use the Create HTML Table action, first dump all your data into that table (likely with an Apply to Each), and then dump that table into an email. For more control over how the table is created, press "Show Advanced", and then for Columns select "Custom".

Re: Guide: Working with Nested Repeats in Power Automate

WilderNiss1 — Wed, 18 Dec 2024 20:06:13 GMT

Thanks! For the purposes of my example, the array I've created and want to add into an email is just a few fields. The repeat is designed such that surveyors can report band combos of all birds they see on a survey. So the Append to array (Apply to Each) is set up as so: {
"Band Combo": @{items('Apply_to_each')?['attributes/band_combo']},
"Sex": @{items('Apply_to_each')?['attributes/sex']},
"Age": @{items('Apply_to_each')?['attributes/age']},
"Presumed Band": @{items('Apply_to_each')?['attributes/presumed_band']},
"Bird Notes": @{items('Apply_to_each')?['attributes/bird_notes']}
}

Is an HTML table the only way to put data from a repeat like this into an email? I agree it does sound like the simplest. I was just wondering if there was anything that could be replicated with a "apply to each" structure within the body of the email.

Like If the email had a section as

Birds Seen:

[Band_combo],[Sex],[Age] ([Bird_notes]) <- and then just have this add a line in the email like this for each bird seen? I'm probably asking too much haha

Re: Guide: Working with Nested Repeats in Power Automate

WilderNiss1 — Wed, 18 Dec 2024 20:19:48 GMT

Disregard - I think the HTML table will do exactly what I need it to. Just had the columns input automatically from the array I set up. Thanks!!