topic Re: Survey123 Create Report Action Failing in ArcGIS Survey123 Questions

Survey123 Create Report Action Failing

RobertAnderson3 — Mon, 19 Jun 2023 11:42:24 GMT

I've set up a bunch of Power Automate flows with the Create Report function and it's a beautiful tool for many workflows we have.

However, I am finding on an unfortunately regular basis that the report isn't returning any results?

The action Create Report itself marks as "Successful" but then the "Upload File from URL" action fails because "A value must be provided for source path" so when I look further into the "Create Report" I find it has returned an error, a few different ones 'Internal Server side error' (I think this was Esri server errors mid last week) or 'Error assembling data' or 'Error querying data'

But when I run them again, without making any changes to the data or webhook, they work without an issue.

I'm curious if there's something I'm missing (other than a try-catch to run it again if the output is an error) or why the Create Report action marks as successful when it has in fact failed.

Has anyone else been having similar issues?

Re: Survey123 Create Report Action Failing

abureaux — Mon, 19 Jun 2023 13:51:33 GMT

I have a large number of Power Automate flows generating reports for my surveys constantly. Errors are rare, but not unheard of. And as you pointed out, simply rerunning the flow will have it succeed.

You mentioned that Create Report was successful, but then also mentioned that there was an error in that step. Are you using Esri's Power Automate Action, or is this a custom flow? If this is Esri's, I have never used it. If this is a custom one, what does your Do Until step look like? To generate a report, you will be waiting for the server to "finish" generating the report and will want the loop to look like this:

@or(equals(variables('jobStatus'), 'esriJobSucceeded'), equals(variables('jobStatus'), 'esriJobPartialSucceeded'), equals(variables('jobStatus'), 'esriJobFailed'))

This step failing is where I see 100% of these random errors. In fact, I had a flow fail just this morning:

(It automatically reran and was successful)

In my case, I have error handling set up in the flow. Basically, if there is an error, the flow will reset and run again. I have never seen one of these mystery errors happen more than once in a row, and only at a frequency of every (very rough guess) 1/50 to 1/100 submissions.

Honestly, if you are experiencing the same thing as me, I don't think Esri support can help. The errors are far to transitory to analyze. I would suggest going the same route as me with the multi-stage report generation.

Re: Survey123 Create Report Action Failing

RobertAnderson3 — Mon, 19 Jun 2023 16:20:32 GMT

Thank you!

I am using the Esri Create Report action that they put out this year, so I just have that action in the flow itself. I do not have a "Do Until" in my flow at this point in time, but I think that will be the approach I take to avoid this issue, so I do really appreciate the example you included!

So basically the Action still runs and gives the little green checkmark saying it worked, it returns status code 200 either way at the end.

So it knows, it just powers through it and it's on me for not catching it. Can you set a delay on the do until to try again?

I wanted to bring it up too just in a "be aware" kinda way with how much more they've been popping up the past week or so as well.

Actually, @abureaux could you share the whole Do Until set up? I tried putting it in and it just spun for forever so I didn't quite get the set up I guess.

Re: Survey123 Create Report Action Failing

abureaux — Mon, 19 Jun 2023 19:47:26 GMT

For sure. The Do Until step I am using should work with this Esri Action, based on what I am seeing in the JSON output. But there are things I do before this to set it up, so there will need to be tweaking one way or another to get this to work in your set-up.

For context, I believe the Esri Create Report Action would be the equivalent of my "HTTP - Create Report" step:

Here are the components (starting at the first Parse JSON):

1. Parse JSON - Job ID + Job Status

Body = The previous "HTTP - Create Report" in my first screen cap (so, probably your Esri Create Report Action?)

Schema =

{ "type": "object", "properties": { "jobId": { "type": "string" }, "jobStatus": { "type": "string" }, "submitted": { "type": "integer" }, "resultInfo": { "type": "object", "properties": { "title": { "type": "string" } } }, "inputInfo": { "type": "object", "properties": { "jobType": { "type": "string" }, "parameters": { "type": "object", "properties": { "featureLayerUrl": { "type": "string" }, "queryParameters": { "type": "object", "properties": { "objectIds": { "type": "string" } } }, "templateItemId": { "type": "string" }, "portalUrl": { "type": "string" } } } } }, "success": { "type": "boolean" } } }

2. Set variable - jobId

Value = jobId from the previous Parse JSON

3. Do Until - Create Report Job Finished

3a. Do Until's Logic. Must be entered in Advanced mode.

@or(equals(variables('jobStatus'), 'esriJobSucceeded'), equals(variables('jobStatus'), 'esriJobPartialSucceeded'), equals(variables('jobStatus'), 'esriJobFailed'))

3b. Delay - 5 seconds (Get jobStatus)

Simply a 5 second delay. This helps the Loop run smoothly.

3c. HTTP - Get jobStatus

This one will be interesting... JobId you already have from above. But Token I set previously. Not sure how to best accommodate that.

3d. Parse JSON - Result Info

Body = Previous HTTP step

Schema =

{ "type": "object", "properties": { "jobId": { "type": "string" }, "jobStatus": { "type": "string" }, "submitted": { "type": "integer" }, "completed": { "type": "integer" }, "resultInfo": { "type": "object", "properties": { "title": { "type": "string" }, "totalCount": { "type": "integer" }, "resultFiles": { "type": "array", "items": { "type": "object", "properties": { "objectIds": { "type": "array", "items": { "type": [ "integer", "string", "number" ] } }, "name": { "type": "string" }, "size": { "type": "integer" }, "url": { "type": "string" } }, "required": [ "objectIds", "name", "size", "url" ] } }, "succeededObjectIds": { "type": "array", "items": { "type": "integer" } }, "failedObjectIds": { "type": "array" }, "details": { "type": "array" } } }, "inputInfo": { "type": "object", "properties": { "jobType": { "type": "string" }, "parameters": { "type": "object", "properties": { "featureLayerUrl": { "type": "string" }, "queryParameters": { "type": "object", "properties": { "objectIds": { "type": "string" } } }, "templateItemId": { "type": "string" } } } } } } }

3e. Set variable - jobStatus

Value = jobStatus from previous Parse JSON step.

4. Condition - Job Succeeded or Failed

This Condition is probably the most important part. If the report doesn't print, it goes to the If no branch, which resets the flow. If the report does print, it hits the If yes branch, and does whatever the next steps are.

Re: Survey123 Create Report Action Failing

RobertAnderson3 — Thu, 22 Jun 2023 13:23:57 GMT

Amazing, thank you for that @abureaux !

I did end up going another way with it for the Create Report action and I THINK this should work too? A little simpler because I can work with dynamic content instead of the whole JSON.

I initialized a variable reportSuccess and in a Scope (Try) I added a Do Until where the Create Report runs, and then updates the variable based on the jobStatus return, so it will run until it is esriJobSucceeded

I then included the rest of my actions in the Try, and the last step I have outside of the Try is Send an Email with the Configure run after set to failed, skipped or timed out.

I also have the reportError variable which just accepts the body of the return from Create Report and puts it in a text file attachment to my email. My only issue with that right now is it doesn't retain any structure so it's just a big block of text. I'd love to parse the return for the error messages themselves, but it looks like the return changes depending on what happened so I'm not sure how to do that with consistency.

I also added a direct link to the flow run that failed in my email so that's super handy, and maybe easier.

Re: Survey123 Create Report Action Failing

abureaux — Thu, 20 Jul 2023 14:29:53 GMT

Nice! This looks good.

I do have two things to consider though:

Add esriJobPartialSucceeded. This does come up from time to time, and a report is still generated. You can monitor your flow to see if it's worth it for you.
Without esriJobFailed, all failures will take the full duration of the Do Until to timeout. Again, may not be a big deal for you, but I thought it was worth mentioning.

Re: Survey123 Create Report Action Failing

RobertAnderson3 — Thu, 20 Jul 2023 16:51:04 GMT

Thank you for the feedback!

I was curious about the esriJobPartialSucceeded, I have no idea what that result means, what would the break down be to make it partial?

And how do you mean with the esriJobFailed? I want it to re-try when I get the failure because I was finding a bunch of random no reason failures that worked if it tried again, I set a cap of like 10 re-tries to avoid problems on unsolvable problems (like the ever fun 14 day token timeout).

Re: Survey123 Create Report Action Failing

abureaux — Fri, 21 Jul 2023 15:04:44 GMT

Honestly, that result may no longer exist! It's been a while since I've looked at that documentation. You should be fine with just esriJobSucceeded.

Also, I didn't look at your Create Report step close enough, and I believe you are correct. It should do the thing until the other thing is done. Honestly, I haven't had the time to update my flow template to include Esri's new Create Report Action.

Your flow is looking good! Best of luck 🙂

Re: Survey123 Create Report Action Failing

RobertAnderson3 — Fri, 21 Jul 2023 15:24:08 GMT

All good haha, we all have our own stuff to focus on!

Yeah it doesn't look like it's mentioned in that documentation now it must have been removed.

And if you have one working without the Create Report action I wouldn't worry too much about updating the flows right now, especially not until they have the 14 day token timeout improved at least!

Re: Survey123 Create Report Action Failing

DanLissick — Tue, 14 Nov 2023 16:10:38 GMT

Hey Robert,

By chance could you provide more information on your final Flow? I am having trouble replicating the final solution. Thank you!

Re: Survey123 Create Report Action Failing

abureaux — Tue, 14 Nov 2023 16:46:09 GMT

I assume you are Enterprise, but if you are AGO, it is slightly different (and easier, since you can skip a step). Also, if you are Enterprise, but you have an old connection (like I did), you will not see any of the new options unless you update your old connector (which is easy, but annoying).

If you are Enterprise and are making a connection to Power Automate for the first time, it's relatively straight forward:

Follow instructions here. Esri hasn't updated the graphics in a while, but it is still valid.
After you add that connection and test connections successfully, you are done! You will see all the new options when you create a flow.

Beyond that, it is really up to you for what you want to do. Personally, I upload all generated reports to SharePoint for storage, then I use those files in all subsequent processes (mainly to avoid issues pulling a report at a later time).

Is there a specific thing that isn't working or place you are stuck?

Re: Survey123 Create Report Action Failing

DanLissick — Tue, 14 Nov 2023 18:41:35 GMT

The flow suggested in the attachment seems to be the solution I am looking for. Though in the image I don't understand how "reportSuccess" or "Set reportError" variables are created.

Re: Survey123 Create Report Action Failing

abureaux — Tue, 14 Nov 2023 19:03:07 GMT

Ahh. So it shouldn't be necessary to do this inside an ~~Apply to Each~~ Do Until. I only figured out my connection was outdated earlier this year, and have since updated that and modernized all my flows. As such, I've been using the new workflow for most of the last year, and haven't run into any issues yet.

Here is my (very simplified) process:

When adding the Value to your Variable, you need to select status of the first result file. If you select the other "status", your Set Variable step will be tossed into an apply to each. That is because Esri's Create Report step is a self-contained Do Until. Esri set up all of the "... of the first result file" variables specifically for situations like this.

NOTE: I suppose you could probably skip over the Variable entirely if you really wanted. I just like variables.

Re: Survey123 Create Report Action Failing

DanLissick — Tue, 14 Nov 2023 19:31:12 GMT

Thank you!

Re: Survey123 Create Report Action Failing

RobertAnderson3 — Fri, 17 Nov 2023 13:45:23 GMT

Did you end up getting it all sorted @DanLissick ?

And @abureaux have we heard anything more about the token timeout for the Survey123 report functions yet? I still have my 14 day reminder to sign back in lol

Re: Survey123 Create Report Action Failing

DanLissick — Fri, 17 Nov 2023 14:22:26 GMT

I believe I have. The update to Power Automate in the early summer seemed been the issue. I too have a reminder to refresh Survey123 under Connections. Though when starting to use the new designer instead of classic I noticed my flows in the new designer had broken connections for each Survey123 step even though I was updating Surveying in the Connections location. It has been only a few days but I hope that was my main issue.

Re: Survey123 Create Report Action Failing

RobertAnderson3 — Fri, 17 Nov 2023 14:35:48 GMT

Good to hear! Yeah an update earlier in the year broke surveys that were created from a template prior to then, surveys kept working but if you wanted to make changes things went awry.

Re: Survey123 Create Report Action Failing

abureaux — Fri, 17 Nov 2023 16:54:15 GMT

Nope 😞

We have to update our employee list CSV weekly anyways, so I added "refresh Power Automate connection" to my Monday Morning tasks for now.

Re: Survey123 Create Report Action Failing

RobertAnderson3 — Mon, 20 Nov 2023 15:11:53 GMT

Darn, yeah it's on my calendar to refresh when needed. The CSVs I have I update as I get the info. (That said, I'm still waiting for them to fix the issue with CSVs not refreshing if you use a URL scheme to open the survey too)

Re: Survey123 Create Report Action Failing

abureaux — Mon, 20 Nov 2023 15:38:36 GMT

"I'm still waiting for them to fix the issue with CSVs not refreshing if you use a URL scheme to open the survey too"

I can't express in words how much I need this... (Same with the survey itself not updating when launched via a URL)