Sending delayed email with Power Automate and Survey123

[erica_poisson]

Hi - 

I am familiar with using Power Automate to do various (simple) things with Survey123 & feature services, however I am using a new connector and am baffled by the error I am receiving. 

Goal: Send an email x days after the date stored in the "File_Complete_Date" attribute field of my data. I am testing with a 2 day delay, but if I can get this to work the delay would be 55 days. 

I want to use the Delay until action to accomplish this, however I receive this error when I test my Flow.

Unable to process template language expressions in action 'Delay_until' inputs at line '0' and column '0': 'The template language expression 'addDays(triggerBody()?[outputs('Convert_File_Complete_Date')],2)' cannot be evaluated because property '{
"body": "6/16/2022 12:00 AM"
}' cannot be selected. Please see https://aka.ms/logicexpressions for usage details.'.

Here is what my Flow looks like, and below is the syntax I am using. 

Convert File Complete Date = 

addSeconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['File_Complete_Date'], 1000), 'MM/dd/yyyy')

Delay until = 

addDays(triggerBody()?[outputs('Convert_File_Complete_Date')],2)

Any suggestions would be appreciated. Also, if this is not the best way to accomplish an email notification 55 days later, I would love suggestions on alternative options! 

[SeanKMcGinnis]

Hi @erica_poisson, would it be possible for you to share a larger screenshot of your flow? I would like to try and recreate it and try to help find a solution.

[erica_poisson]

Hi @SeanKMcGinnis,

Sure! Let me know if what I've included below is better. Thank you in advance!

Syntax for addSeconds is above in original post. 

Syntax for addDays is above in original post. 

Last week I also started playing around with creating a Scheduled Cloud Flow (instead of an Automated Cloud Flow) and tried to get the workflow detailed in this post to work for what I was trying to do. I did not have a lot of time to play with it, but wasn't able to get it to work. Here I got an error at the Generate Server Token step (which I have never had issues with in my other Flows which follow this exact workflow, but use the trigger "When a http request is received" instead of a Recurrence). I was hoping to get back to testing this particular setup sometime next week. 

Error: Unable to process template language expressions in action 'Generate_Server_Token' inputs at line '0' and column '0': 'The template language function 'first' expects its parameter be an array or a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#first for usage details.'.

If you're able to help here, it would be incredible!

[erica_poisson]

Hi @SeanKMcGinnis - I just wanted to circle back here and see if this was something you still may be able to help with. 

Thank you,

[SeanKMcGinnis]

Hi @erica_poisson, thanks for the conversation today.

I wanted to post a follow up to share the ideas that we came up with in case others are looking to solve a similar problem.

Power Automate does not support using a flow for long 'Delay Until', the current maximum duration is 30 days. Power Automate best practices also recommend not using flows to support processes that need to be on pause for a long period of time.

In our conversation, we identified a couple of options to help address your problem:

1. Create a flow that runs on a set interval that uses the 'Get data from feature layer' action and queries for the creation date  (and whatever other conditions you are looking for). The query will return all of the results that meet the criteria allowing you to perform the appropriate steps on each one individually.
2. When the data is submitted, perform some data engineering upon submission and populate a field in the feature layer to store the notification date. The data engineering can be done in the submitting client or using a Power Automate Flow on a create or update webhook.
3. Store the information in a data store (hosted table, Dataverse, Sharepoint List, database record, etc.) of file (Excel, CSV, etc.), and use the values stored there are a pointer as to when a notification about a specific record should be pushed.

Hope this helps and please let me know if anyone has any additional questions.

[erica_poisson]

Hi @SeanKMcGinnis  - 

Thank you. As soon as the ArcGIS Connector is added to our business connectors, I plan on testing the following Flow which I think will do what I am looking for. My plan will be to post back here if I am successful so others can use this as an example. 

Flow as it stands so far:

Where I'm using the following expression for addDays():

addDays(body('Convert_File_Complete_Date'),int(45),'yyyy-MM-dd')

This would be set up to run daily. Hopefully I am actually able to test this soon. If you see anything inherently wrong with anything here and let can me know, that would be appreciated. 

Thank you for your time and help, it was nice talking with you.

[erica_poisson · Accepted solution]

Hi @SeanKMcGinnis  - 

We've finally had the ArcGIS Connector added into our Business Connectors and I have begun my testing. I've had the flow successfully run, but my condition is evaluating as False and not triggering my email notification. I am hoping you can advise on this. 

EDITED!

Here is the beginning of my Flow: 

Initial -  config in screen capture below no longer used. 

Updated to - this removed the configurations within Available fields, Comparison type and Search parameter value and added an additional statement to the Where clause to make it much more specific:

After getting the data from a feature layer, I use Apply to each with the following configuration:

Within the Convert File Complete Date, my Base Time is set to be: 

addSeconds('1970-1-1', Div(items('Apply_to_each')?['attributes']?['File_Complete_Date'], 1000), 'MM/dd/yyyy')

Within the Compose (Calculate 45 days from File Complete Date), the Input is set to be: 

addDays(body('Convert_File_Complete_Date'),int(45),'yyyy-MM-dd') 

My Condition is set to be equal to utcNow: 

utcNow('yyyy-MM-dd')

I have some questions/concerns about why this might not be working as expected:

1. utcNow... to me this means date and time, however my data is really only configured to have a date (all time are defaulted to 12pm). Could this be why the Condition evaluates to False for all? 

2. When the Flow runs, I see 1,000 iterations within Apply to each (see below) - could the test record just not be reached? When I bring my feature layer into a map and apply my query, I have 3,172 records which meet my criteria (Application_Status = App Complete AND File_Complete_Date IS NOT NULL)

I have addressed this by altering my Where clause to be much more specific - I probably should have known to do this from the beginning!

3. When I look at the raw data from the ArcGIS Connector Outputs, I do not see my test record included; I have searched by it's unique file number using Find and it does not come up. 

I will continue testing to see if I can solve this. I will attempt to make my Where query in the ArcGIS connector more specific so I return less records than I currently am (#2 above). If you have suggestions in the meantime, I would appreciate anything you have.

I think I have solved this... I do have a question however... even after reading the documentation, I am unclear what the Available Fields, Comparison Type and Search Parameter Value is supposed to be doing within the "Get Data" connector. Could you elaborate on what these should be used for? 

Thank you!

[SeanKMcGinnis]

Hi @erica_poisson.

To clarify on those inputs - the intent was to create a simple way to use drop downs and dynamic content to create a query and return a filtered set results from the feature layer. The 'Where' parameter allows the flow maker to build more complex SQL queries and filter the results.

Something also to note, the 'Where' input will take precedence over the 'Available fields', 'Comparison type', and 'Search parameter value' inputs when all are used.

[erica_poisson]

Hi @SeanKMcGinnis  - 

Thank you for the information. I was uncertain if/how these worked together, so this is helpful. 

[erica_poisson]

Hi @SeanKMcGinnis -

Apologies for a late question but I am running into an issue and hope you can assist with resolving. 

The query I am using within the flow is very specific. I felt this was necessary to make it run efficiently because the hosted feature layer it is getting data from has thousands of records. Because of how specific it is, I would expect it to return no results on many dates. When I was testing, I'd created dummy data to work with so I never ran into this problem. Now, on days when no results are returned I get the error - I am not even sure if it is because of no results returned from my query because of the last bit "and offset undefined":

I do not want these Flows to be disabled automatically because of this. Do you have suggestions on how to resolve this problem? 

Thank you,