Recurring Summary Report from Survey123 Using Microsoft Power Automate

[RobertAnderson3]

I'm looking to make a webhook using Power Automate that can create a summary report on a weekly basis to send a supervisor data on repairs that need to be made to certain assets collected through the week.

The struggle I've run into is the ability to add an ObjectID to the Create report action, how do I set this up as a dynamic content piece when it is not being triggered by a survey submission?

And any additional help on the where clause for "in the last 7 days" type would also be greatly appreciated. Would this be a filter I set up in the report template itself? Or in the WHERE clause parameter on the Create report action of the webhook?

Thanks!

[SeanKMcGinnis]

Good Day @RobertAnderson3

In looking through your post, it looks like you are asking two questions:

1. How can I schedule a flow to run and query a feature layer once a week for only the records created that week
2. Pass the ObjectID from the results to the Survey123 Create Report action

I worked through a flow this afternoon and I think I have a way for you to do it!

Recurrence Trigger

In order to get the flow to run at my desired interval, use the Recurrence trigger. It will allow you to set up the desired frequency and when you want the flow to run.

Get Today's Date

The next step is to get the date the flow runs - to do this, I am going to initialize a variable to represent today's date and set the value with the utcNow expression.

utcNow()

Seven Days Ago?

Now that we have the date the flow is running, the flow will need to figure out what the date was seven days ago. I will create another variable to calculate what the date was seven days ago. I will use another expression to calculate the value:

addDays(variables('todaysDate'),-7)

The 'addDays' expression will use the 'todaysDate' variable we created in the previous action and subtract seven days from it.

NOTE: The previous two steps can be combined into a single action, for simplicity's sake I broke it into two actions.

Optional Step - Time Zone Conversion

If you are looking to query for a time in a local time zone, the Convert Time Zone action will give the flexibility to convert to any time zone. In the screenshot below, I am converting the 'sevenDaysAgo' variable from UTC to Eastern Time.

Query for Records Created in the Past Week

Now that we have the start date, we can get all of the records that have been created during the desired time range. The 'Get data from feature layer' action will query the feature layer for us filtered by the desired date range.

NOTE: Notice the single quotes on either side of the Converted time dynamic content. The query is expecting a string wrapped in single quotes.

The action will return all of the records that meet your query as JSON which will the flow to iterate through each of the records - including the ObjectIDs to create the reports.

Iterate Through Results and Create Report

To get the ObjectIDs, we need to iterate through all of the data returned from the query with the 'Apply to each action. This will give the flow access to each record that met the query parameters.

Now you will be able to access the output from the 'Create report' action and build out the rest of your .

This is what the whole flow will look like when implemented as laid out in this post.

I hope this helps and please let me know if you have any other questions.

[BrandonA_CDPH]

@SeanKMcGinnis - thank you for the great workflow. I know this is an older post, but it addresses exactly what I am trying to do. 

I, unfortunately, do not have access to the "Get Data From Feature Layer" action as it is a Premium feature. IS there a way to do this query without that action? 

[RobertAnderson3]

Is it a single report for each record you're looking for? But then have them all send at the same time? You could maybe add in an item for a delay on sending the emails but have the flow trigger when a survey is submitted?

[BrandonA_CDPH]

That could work.. I haven't experimented with delayed actions in Flow, so I'l lhave to look at that... 🤔

I was hoping to generate a summary report on a schedule (say monthly) and pull records from the previous month. Right now, users go into the Survey123 Website's data tab, select the records they want, then run the report from the Report Menu. Trying to automate that "select" action, then generate the report for the selected records.

[RobertAnderson3]

That's my goal as well, but on a weekly basis. The info @SeanKMcGinnis gave is a fantastic start, but since it ends up iterating through the selected entries it creates multiple reports and I'm not sure if there's a way to do the summary one.

[RobertAnderson3]

I know it's been a bit but I finally got a chance to return to this task. This is incredibly helpful and I really appreciate the time to put it together! Thank you @SeanKMcGinnis !!

The one thing I would like it to do that's different than how it's set up here is to take those entries from the past week and create a single SUMMARY report, so that it's one document being created and attached to the email instead of many. 

I'm not sure if that would be possible to do with the way the create report action is set up or not?

This definitely does the part of grabbing the information from the layer that I needed though!

[RobertAnderson3 · Accepted solution]

As an update to this, it appears they have added the ability to include a WHERE clause on the Create Report action! I followed the first couple steps for setting up the dates from Sean above, and then used them in the WHERE clause and got it working without the ArcGIS connector!

@BrandonA_CDPH did you get yours set up now too?