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.
