topic Re: Generate Feature Report in Power Automate in ArcGIS Survey123 Questions

Generate Feature Report in Power Automate

Bryan_Wade — Wed, 07 Jul 2021 21:56:36 GMT

Does anyone know if the ability to generate Surevey123 Feature Reports using Power Automate has been fixed? I've used the work arounds described in previous posts but some of my surveys are getting a bit complicated to continue to do that.

Re: Generate Feature Report in Power Automate

Ruth_JiatengXu — Thu, 08 Jul 2021 07:53:22 GMT

Hi @Bryan_Wade

Currently, Survey123 does not support generating report using power automate. I encourage you to contact Esri Support, and our Support team will assign an official bug number for your records. This number can be used to search for and subscribe to the bug on the Esri Support site. If the issue is reported by other customers it will be attached to the same bug report, which helps us assess the impact of the issue and prioritize it accordingly.

Since you mentioned that you are using custom power automate connector for report generating, this developer documentation of the Report REST API might be helpful: https://developers.arcgis.com/survey123/api-reference/rest/report/. Also, if it is possible could you please let us know what is the issue you encountered with your workflow?

Thanks,

Ruth

Re: Generate Feature Report in Power Automate

Bryan_Wade — Fri, 09 Jul 2021 13:37:56 GMT

Ruth,

I wasn't so much an issue just a pain to do the work around. My organization currently doesn't allow us to use integromat and I would like to generate a feature report that then gets emailed to s supervisor when a particular survey is submitted. Mostly just hoping that the option to generate a report using power automate will be available soon

Re: Generate Feature Report in Power Automate

Ruth_JiatengXu — Mon, 12 Jul 2021 07:45:02 GMT

Hi @Bryan_Wade

Thanks for your reply! I will log your comment in our internal repo.

Thanks,

Ruth

Re: Generate Feature Report in Power Automate

abureaux — Tue, 24 Aug 2021 20:42:34 GMT

I can confirm that you can generate a Survey123 Feature Report via Power Automate flows. I am using it for all of our automations right now (mainly because Integromat isn't working in 10.9).

Re: Generate Feature Report in Power Automate

Bryan_Wade — Wed, 25 Aug 2021 15:10:05 GMT

I would be curious to know how you are doing that as there isn't an option to generate a feature report in power automate. I know there are custom options 1 of which I am currently using but it requires consistent monitoring to ensure functionality. Integromat though allows me to create the report using my report template from Survey123. You are the first person that I've heard from about Integromat not working with the newest survey123 version.

Re: Generate Feature Report in Power Automate

abureaux — Thu, 02 Sep 2021 14:20:07 GMT

It's not so much that it doesn't work with this version of Survey123, but rather Integromat doesn't currently work with Enterprise 10.9. If you are on 10.8.1 or earlier, you are fine.

As for Power Automate, here are the general steps:

Create the custom connector: https://support.esri.com/en/technical-article/000025025 (troubleshoot said connection in two weeks: https://support.esri.com/en/technical-article/000022795)
Create a new Flow and add your new When a survey response is submitted as the trigger
Add 8 Initialize Variable and customize as per below (Name / Type / value)
1. templateItemId / string / <Your survey's templateItemId>
  1. Go to your ArcGIS Survey123 page
  2. Go to your survey's Data page
  3. Click Report
  4. Under step #2 in the Report slide-out, select your template from the drop-down (do this even if it already looks selected)
  5. Now look in your URL bar. You will see something like: templateItemId:62abcdef123456abcdef123456abcdef123456abcd63
  6. Grab that long alphanumeric string. That is your template's Item ID
2. reportOutputType / string / <pdf> or <docx>
3. jobStatus / string / <blank>
4. jobId / string / <blank>
5. reportUrl / string / <blank>
6. objectId / string / objectId
7. featureLayerUrl / string / serviceUrl
8. formItemId / string / formItemID
... I recommend adding a Parse JSON here to grab all the values mentioned above as it can make your life a lot easier, but I feel like adding those instructions may be too convoluted for this super quick guide. There isn't anything wrong with grabbing them from the initial response, and for some things actually works better (I blame PowerAutomate). I omitted a few things (like some Set Variable following my Parse JSON) for the sake of simplicity.
Add an HTTP and configure as per below. username and password are plain text and should be your Creator level account that will be hosting this survey. Personally, I only allow a single account to publish our live surveys to simplify the automation process (this also helps in other areas, such as general content control). TIP: Name your Steps. Below, I named mine HTTP - Generate Token
Add a Parse JSON
1. Content is the Body of your HTTP above.
2. The Schema? Just copy-paste:
  {
      "type": "object",
      "properties": {
          "token": {
              "type": "string"
          },
          "expires": {
              "type": "integer"
          },
          "ssl": {
              "type": "boolean"
          }
      }
  }
Add another HTTP and format as per below:
As per the above image, I added a 5 second delay. It seems to help, but maybe I am just imagining it.
Add an HTTP and format as per below
Add a Parse JSON
1. Content is the Body of your HTTP above. (same as the previous Parse JSON step)
2. Just copy-paste the 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"
          }
      }
  }
Add a Set Variable
Name = jobId
Value = jobId (from the Parse JSON directly above)
Add a Do until
1. Click "Edit in advanced mode" and add this:
  @or(equals(variables('jobStatus'), 'esriJobSucceeded'), equals(variables('jobStatus'), 'esriJobPartialSucceeded'), equals(variables('jobStatus'), 'esriJobFailed'))
2. Within the Do Until, add these steps:
  1. Delay - 5 seconds
  2. HTTP
  3. Parse JSON
    1. Body is from the HTTP directly above
    2. Scheme, copy-paste:
      {
          "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"
                              }
                          }
                      }
                  }
              }
          }
      }
  4. Set Variable
    Name = jobStatus
    Value = jobStatus (from Parse JSON above)
Following the Do Until, add a Condition and set it to Or
On the "Yes" side:
1. Add an Apply to each
  Output from previous step: resultFiles (From the Parse JSON in my step #12 above)
2. Within the Apply to each, add Set Variable
  Name = reportUrl
  Value = url (From the Apply to each you are currently in)
3. Below the Apply to Each, add HTTP
4. DONE! If you want to send your pdf/docx somewhere (e.g., in an email), attach the body of the above HTTP. When you specify the file name, dont forget to add the extension. Example:

*Corrected a couple spelling errors*

*Edit 2. Forgot some JSON*

Re: Generate Feature Report in Power Automate

Bryan_Wade — Wed, 25 Aug 2021 18:32:25 GMT

That is what I currently do and want to stop doing. Also, I am using AGOL and not Enterprise. But I appreciate the replies. I'll just keep doing what we are doing until I can get approval for integromat or Esri decides to provide support in power automate.

Re: Generate Feature Report in Power Automate

abureaux — Wed, 25 Aug 2021 18:37:31 GMT

Then I am not sure I understand.

When you say "...it requires consistent monitoring to ensure functionality.", what do you mean? My flows are all completely autonomous.

And what do you mean by "Integromat ... allows me to create the report using my report template from Survey123"? Power Automate lets you do the same thing.

Re: Generate Feature Report in Power Automate

RyanB1 — Thu, 02 Sep 2021 03:22:27 GMT

Hi Abureaux

Looking at step 12 part 3. At the moment its coming up with the below. Is this correct? or is a txt file i cant get access to?

Parse JSON
1. Body is from the HTTP directly above
2. Scheme, copy-paste:
  2.txt

Re: Generate Feature Report in Power Automate

abureaux — Thu, 02 Sep 2021 14:15:57 GMT

Opps, my apologies. Give me a min to grab the JSON. I will edit the above post.

...the text was being annoying, so I was going to toss those blocks into text files and attach them, but then things started working again and I must have missed this one.

Re: Generate Feature Report in Power Automate

abureaux — Thu, 02 Sep 2021 14:21:44 GMT

Hmm. Says my post will be edited after moderator approval. Not sure how long that takes... I'll just leave that missing JSON here in the mean time:

{

"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"

}

Re: Generate Feature Report in Power Automate

wcozzens — Tue, 05 Oct 2021 16:05:48 GMT

Thanks for posting that. I recreated that flow and everything works - except if the report template contains a geopoint type question, then it fails with:

["An error occurred when rendering by the report engine. Failed to print map for `$map_geopoint`. waiting for function failed: timeout 180000ms exceeded"]

If I remove the geopoint question from the MS Word feature report template, then it works fine and generates the report from the Flow API request. When the geopoint question is included, the report generates normally without error from the Survey123 web site, but fails if called from the Flow API.

I have already opened a support request with ESRI on this issue, but so far we have been unable to resolve this. The query works and everything is returned, but it seems like it cannot access the map used for printing the geopoint in the MS Word feature template.

We have tried many things to to resolve this, including adding the webmapItemId and mapScale parameters and trying different formatting of the request within Flow and also using Postman, but nothing seems to work when a geopoint question is included.

Just wondering if you or anyone else has tried including a geopoint question in the MS Word feature report and then using the API request from Flow to generate the report. Has anyone successfully done this, had similar issues or found a way to resolve this problem? I'm testing with a report template that is very basic - one question and one geopoint... no repeats or anything.

Re: Generate Feature Report in Power Automate

abureaux — Tue, 05 Oct 2021 17:55:08 GMT

Right. That... I forgot about that. That is a bug.

I noticed this a while ago as well. Took me a bit to figure out that the geopoint was the issue when handled by Power Automate since the template worked fine when printed through the Portal and this feature used to be supported in Power Automate.

A recent(ish) update from Esri broke something, so geopoint questions seem to cause the error you described above. I removed my geopoint questions a while back to get around this bug. I have been meaning to contact Esri Support to report the issue, but it slipped my mind.

If you are using Portal 10.9 (like me), there aren't many other options. If you are still on 10.8.1 or earlier, you can try Integromat. It may still work with geopoint questions. I cant really test though as it doesn't work at all in 10.9 ><

Sorry I couldn't be more help on this one. Hopefully they can identify the issue and fix it. I miss geopoints...

Re: Generate Feature Report in Power Automate

wcozzens — Wed, 06 Oct 2021 14:20:28 GMT

Thanks for that reply. I'm testing this process using AGOL instead of Portal, but it sounds like exactly the same issue regardless. You indicated this process used to work fairly recently - I'm guessing it broke sometime around September 16, the last major AGOL update by ESRI - does that sound about right?

If so, maybe we can pinpoint the issue to something that occurred in that last ESRI update. I'm working with tech support and will post updates to the issue as received.

Re: Generate Feature Report in Power Automate

abureaux — Wed, 06 Oct 2021 15:01:09 GMT

I believe it was before that update. Looking back through my records, there was an update on July 29, 2021 that caused me a lot of grief. It was sometime after that when I started removing the geocode questions...

If I were to go off of my Word document's time tags, I have a template with a geocode question on August 19, and a subsequent template without the geocode question that same day. So, looks like I noticed the issue in mid August.

Re: Generate Feature Report in Power Automate

abureaux — Tue, 12 Oct 2021 18:11:18 GMT

As I am dealing with this right now, it triggered me that I should probably post this here as well. The second major limitation that I have encountered (the first being geopoints not working), is that "Edit and resend" does not seem to trigger Power Automate for me even with the switch checked in the webhook on the Portal. Integromat did work with this feature though. I am unsure if this is Power Automate, Esri, or our Portal acting up though. ~~I will likely open a formal case tomorrow as this one is pretty annoying.~~ (I actually already had a case open for this for a while. Just forgot about it because it had been so long)

~~(I have told people to use "Copy data to a new survey" in the meantime. As this acts like a new submission, it does trigger PA)~~

Edit: After I went back to check on this feature to update my case, it appeared to be working again. I am guessing that something in the 3.13 update (or a more recent update by Esri behind the scenes) fixed whatever was causing this problem for me.

Re: Generate Feature Report in Power Automate

wcozzens — Fri, 08 Oct 2021 13:11:18 GMT

Thanks abureaux. Can you please post the results of your findings with ESRI Support on the Edit & resend issue? I'm sure I'll be running into that too at some point.

ESRI Support has diagnosed the issue with the geopoint question and provided a workaround. It has been recorded as a bug, and I have escalated it.
#BUG-000143518 Trying to run createReport Request using a token from the generateToken Request Fails

The issue is with the token. Using the generateToken API request fails to provide a valid token to access the map required for the createReport function, it only works using the token that was included with the survey submission.

So the workaround is to use the token that's included with the submitted survey, not the one created from the generateToken API call. Instead, use the value of Portal Info Token that's included with the survey submission. It works, (it will create the map if a geopoint question is included in the template) however there are some caveats that make it less than ideal. This token expires in 15 minutes, so it's not going to work if your automated report generation is on a schedule longer than that, and if the process fails the token will not be valid for resubmitting. Also, since the token is included as part of the survey submission, it's using the submitters permissions to access and generate the report, meaning that all submitters MUST be included in a group that can access and view the surveys (Share Results in the web app), and the history of the report generation will only be available from the Survey123 web app when logged in as that user.

In our environment this workaround is not really feasible. Hopefully the escalated request to resolve this bug will be implemented quickly.

Re: Generate Feature Report in Power Automate

JulieK — Mon, 29 Nov 2021 20:51:18 GMT

@abureaux - Thanks for the workflow. I am trying to send out a report when a certain field is updated in a feature service(Survey123 fs). The feature service when edited does trigger the flow, but I get an error 'cannot read property 'username' of undefined' when it gets to step 7 in your workflow. The survey isn't shared to everyone but the flow user has access to the survey. Is this workflow only possible when survey123 is triggered? Any thoughts?

Re: Generate Feature Report in Power Automate

abureaux — Mon, 29 Nov 2021 21:12:53 GMT

Hi Julie,

Sorry, but I'm not sure off-hand. I haven't tried a flow with the feature service as the trigger. I also haven't gotten that error before (at least in that context. I have seen it before, but only while editing the flow. The solution was to re-authenticate a Data Connection). It may be related to the trigger not containing all the necessary data? If it is failing on the "Check Template" step, I would start by checking the values for formItemId, featureLayerUrl, formItemI, and token. That step is really just formatting a URL though. So it's strange that is got passed the Generate Token phase and failed there. Maybe also check that you are using the same Data Connection?