topic Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect in ArcGIS Survey123 Questions

Nested Repeats in Microsoft Power Automate, Survey123 Connect

Sammy_SalmonNSEA — Tue, 12 Dec 2023 18:29:12 GMT

Hello ESRI Community!

Does anyone know how to deal with nested repeats in Microsoft Power Automate?

Here was my process:

1) I coded a survey in Survey123 Connect (version 3.19.104.0) (see XLSForm below).

2) I used the "When a survey123 response is submitted" flow trigger in Microsoft Power Automate.

3) I used the "Add a Row" function to send data from my survey to an online Excel table. (see photo of flow below).

The survey has two levels of repeat ("plant" within "species_group_repeat"). When I tried to create a flow with the nested repeat, it created three "for each" loops (photo below). In the spreadsheet the data is mostly there but each response is entered multiple times. I've successfully done the above process with one repeat, but never with nested repeats. Does anyone know how to fix this?

Thank you in advance for any insight!

Sammy_SalmonNSEA_1-1702403175005.png

XLSForm

Sammy_SalmonNSEA_0-1702403053164.png

MPA Flow

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

abureaux — Tue, 12 Dec 2023 18:58:01 GMT

Two questions. 1) Have you swapped out of AI mode to see what is actually being referenced in the For Each? You can do that clicking the ellipses (...) in the top right. (Personally, I really don't like that AI assistant. It can make simple tasks difficult), and 2) Can you upload the XLSX? I'm actually not sure off-hand why you have three For Each.

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

Sammy_SalmonNSEA — Tue, 12 Dec 2023 20:12:02 GMT

Hi @abureaux, thank you for your response!

I'll try switching out of AI mode, hopefully that will solve the issue. I've attached the XLSForm.

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

abureaux — Tue, 12 Dec 2023 20:14:01 GMT

Thanks. I'll toss it in and have a quick look.

And just to be clear, swapping out of (or into) AI mode won't necessarily fix (or break) anything, it's just that the "normal" mode (aka, not the AI mode) can give you a more clear and intuitive picture of what is actually going on.

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

Sammy_SalmonNSEA — Tue, 12 Dec 2023 21:46:45 GMT

Hi @abureaux , thank you for taking a look!

Here's a look at the flow in classic mode. The dynamic text is the same in "For each- A" and "For each- B": triggerBody()?['body']?['feature']?['repeats']?['species_group_repeat']

The dynamic text in "For each- C" is: items('for each- B')?['repeat']?['plant']

I wonder if MPA somehow created an extra loop?

The yellow-highlighted fields correspond to the questions in the "species_group_repeat" and the blue-circled fields correspond to questions in the "plant" repeat.

Sammy_SalmonNSEA_1-1702416652694.png

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

abureaux — Tue, 12 Dec 2023 22:57:31 GMT

Yep. I think you inadvertently created an extra loop. It's quite easy to do in Power Automate. If you click the wrong thing, it automatically creates another Apply to Each for you.

I published the form and made a quick flow, and I go the expected two loops.

Now, for the second comment you made regarding duplicates. After I started looking at this, I realized that this was my first time automating a nested repeat from S123 within Power Automate! With some pondering, I finally realized what is going on. Essentially, all of the nested repeat's children are contained within a single large group. So, when it loops through each of your parent repeat items, it adds the total number of children within your survey for each parent (e.g., if you submit a survey with two parents and five children, total, you will end up with 10 rows).

As a result, we need to tell Power Automate what to filter out so it doesn't create duplicates. Luckily, this is pretty simple: Inside your nested repeat, use a Condition to compare the parent's uniquerowid to the child's parentrowid, then put your tasks into the Yes side of the Condition.

abureaux_1-1702420458699.png

This is a separate item...

I am not sure what your process in Power Automate is, but I like to parse the JSON of my initial response and then work with that. I find it makes things much easier than trying to guess what field I am selecting. This is why my flow above likely looks a little different than yours. I will lay out the steps I did below just in case you find them helpful.

1. Set up a Trigger When a survey response is submitted

2. Add a ParseJSON > click "Generate from sample" > paste in the below code > click "Done". (The below code is nearly complete, but I trimmed a few things from the main body since I was just testing the repeats).

{ "feature": { "attributes": { "land_use": null, "landowner": null, "observers": null, "origin_season": null, "watershed": null }, "result": { "objectId": null }, "repeats": { "species_group_repeat": [ { "attributes": { "plot_description": null, "species": null, "transect_plot": null, "notes": null, "uniquerowid": null }, "repeats": { "plant": [ { "attributes": { "cage": null, "health": null, "micro_topograpphy": null, "tube": null, "parentrowid": null } } ] } } ] } }, "surveyInfo": { "formItemId": null, "formTitle": null, "serviceItemId": null, "serviceUrl": null }, "portalInfo": { "url": null, "token": null }, "userInfo": { "username": null, "firstName": null, "lastName": null, "fullName": null, "email": null } }

3. Add a Condition. For the first value, add uniquerowid

abureaux_4-1702421357862.png

4. For the second value, add parentrowid

abureaux_3-1702421345552.png

5. At each step of #3 and #4 above, the Apply to each will be added for you automatically. Now, just add your data operations to the "Yes" side of the Condition.

In the end, I got the proper number of items from the nested repeat and only two Apply to Each loops.

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

Sammy_SalmonNSEA — Wed, 13 Dec 2023 19:31:07 GMT

Amazing! Thank you, @abureaux, for such a thorough and thoughtful reply!

I have a follow-up question (I'm still new to Survey123 & Power Automate). Which "Content" from the survey do I select for the Parse JSON? Does one of the dynamic texts (photo below) hold all of the survey information that needs to be parsed?

Thank you!

Sammy_SalmonNSEA_0-1702495791070.png

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

abureaux — Wed, 13 Dec 2023 19:36:39 GMT

Fair question! Seems I didn't include a screen cap of that step (my apologies).

You want body.

abureaux_0-1702496125595.png

Body, in any step, is essentially the entire raw result/response.

Re: Nested Repeats in Microsoft Power Automate, Survey123 Connect

Sammy_SalmonNSEA — Wed, 13 Dec 2023 21:40:39 GMT

The flow works now! Thank you so much!