Guide: Working with Nested Repeats in Power Automate
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.
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
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)
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".
