Select to view content in your preferred language

Guide: Working with Nested Repeats in Power Automate

721
1
05-15-2024 02:59 PM
abureaux
MVP Frequent Contributor

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.

abureaux_1-1715807183274.png

First, I need a survey with a nested repeat:

abureaux_8-1715790427036.png

abureaux_7-1715790188116.png

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.

abureaux_4-1715789229687.png

abureaux_5-1715789374086.png

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:

abureaux_2-1715785696724.png

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.

abureaux_3-1715785799843.png

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

abureaux_9-1715802688297.png

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.

abureaux_10-1715802958850.png

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

abureaux_11-1715803036535.png

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.

abureaux_13-1715803117526.png

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

abureaux_1-1715804527643.png

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:

abureaux_3-1715808704580.png

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.

abureaux_4-1715808866969.png

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.

abureaux_5-1715809006229.png

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.

abureaux_6-1715809259130.png

And again, if you want to confirm your selections:

abureaux_7-1715809440695.png

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

abureaux_8-1715809897450.png

That's it!

abureaux_10-1715810339254.png

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

abureaux_9-1715810110022.png

*** 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.

0 Kudos
1 Reply
Juan_FelipeVillegas
Occasional Contributor

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.

 

Juan_FelipeVillegas_0-1719006288539.png

 

Regards

0 Kudos