Use Attribute from Parent Table Power Automate

[RobertAnderson3]

I have a Survey123 form with repeats, I am using Power Automate (ArcGIS, the yellow one) to grab the data from the feature layer (I had previously been using the When a survey is submitted trigger but had been running into issues with it just, not triggering at all a lot more lately). This data is then fed into SQL tables using an Insert Row action that goes into an Apply to each so each row gets added.

In the parent table I have an Activity field that I also need in the repeats. The problem is because it's in a separate table, it creates nested Apply to each functions and duplicates the data.

I could just add the fields in the background and calculate them in Survey123, but that's extra fields for minimal reason so I want to know, is there a way to set up Power Automate to link the value from the parent to the child table?

Tagging @abureaux since this feels right up your alley and I've seen you reply to similar things recently.

[abureaux]

I have been noticing a similar behaviour with the "When a survey is submitted" trigger not doing it's job, and was planning on setting up a flow nearly identically to what you described; however, I keep having other tasks come up, so haven't been able to get around to it yet.

I would imagine that this is similar to working with a new survey submission though, in that you need to compare uniquerowid to parentrowid in a nested Condition within your Apply to Each. All of your operations would be on the "yes" side. E.g.:

In the above, "transect_plot" is the repeat, "uniquerowid" is from the main layer, and "parentrowid" is within the repeat.

As I said, this type of flow is still on my list for Q1, but most likely won't be able to fully set it up until our SQL server moves to Azure.

On a side note, have you brought this up with Esri yet? I was planning on opening a support case in hopes of a deficiency being logged. I set up a self-serve reporting feature as a short-term fix.

[RobertAnderson3]

Okay I'll have to give it a try using the rowIDs in the repeats like you've mentioned, I will keep this thread updated with my progress.

And yes I have brought it up with Esri (Canada) I had logged a support ticket back on January 18th after having multiple misses in a single day. Basically got told I would need to set up something like this monitoring the feature layer itself to catch any misses, since there's no logging with the triggers it's pretty well impossible to figure out whether the trigger being sent from ArcGIS Online or the receiver on Power Automate was the failure point.

They're looking to close the case since I've got a workaround, I just asked for an enhancement request to add some logging/messaging for this. Maybe I should request the deficiency being logged too? I don't know if they'd have passed it onto the development teams that there's an issue though. I had replied HERE as well where people have mentioned this issue.

[abureaux]

How are you handling already processed items in the Feature Layer? I was thinking about adding a "processed" field to mine, and then flipping that switch after an item was transcribed by Power Automate. My plan was I'd just leave the flow to run every minute and grab new items as they come in.

[RobertAnderson3]

I'm just running it nightly currently and having it just grab the records that were created in the last 24 hours. The next step in the process only runs every night anyways, so this seems to work for now. Keeping in mind this webhook strategy is still being tested before we move it live. I haven't started editing any records with Automate, definitely a consideration though.

I have started adding a field like that to surveys, but that's for the longer term goal of another team setting up using the API to grab information (built into the next step in the process that works with the records from the SQL table, to skip the webhook and the holding table we're using. It's a whole patchwork kind of process right now haha)

[abureaux]

Oh I get it! I'm very much in the early planning stages for a similar transition... Still so much to think about. But the reliability increase should be worth it.

[RobertAnderson3]

Yeah the reliability has been the big push for moving it forward here. A new project started so I figured now is the time to try it out (and of course it's moving SLOW, it's snow clearing related and we've just, not been getting much snow?)

The other big thing it will improve in our work flow is the ability to edit surveys after (before the webhook runs) as I never did quite get that sorted out using the When a survey is submitted trigger with the how do I link the rows between Survey123 and SQL (but that's more of an I just haven't had the time and focus to make it work thing I think).

And the fact the approach for fixing the token timeout issues with the Survey123 connector makes it sound like they're going to be bringing it into the ArcGIS connector anyways, this option may make the most sense? 

[RobertAnderson3]

Oh and @abureaux for what it's worth, my case with Esri Canada was #03531605 but I was told they can only see Esri Canada support tickets, not sure if Esri Inc in the US would have more access as a master list or something if you report it as well..

[abureaux]

Thanks! I'll get them to add that case # to our account as well. I am also in Canada, so we're in the same boat!