Expression help - checking if item has been updated since being added

[cb23]

Hi there, 

I want to send reminder emails to a person if they haven't submitted documents within three days of them submitting an application. 

My flow is attached. I want to run the flow once a day in the morning to send an email if the DocumentsReceived column is set to 'No' and the date is 3 days after the DateofApplication column. I tried writing an expression but kept getting a null value error message. 

The DateofApplication will eventually pull through from a form being completed so I think this all has to be compatible with the date and time formatting of that. 

Can anyone suggest an expression please?! Thank you.

[wskinnermctc]

Can you make the DateOfApplication column a date/time type column instead of a text?

[wskinnermctc]

Do you want it to send an email when it is only 3 days past the application date? 

Or

Do you want it to send an email every day after 3 days past application date?

Like do you want to send a person a single reminder once 3 days has passed, or do you want to send the person an email every day after 3 days has passed?

[wskinnermctc · Accepted solution]

This example looks long, but if you recreate my steps, you will have all of the building blocks to adapt or change whatever you want. I'm using variables to make references easier, but you could just combine all of it into single expressions once you have the basic steps in place.

I have a solution example that will send an email when the current date is 3 days after the Application Date. It will not send an email if it is 4, 5, 6, etc. days after Application Date, only on day 3.

Basically what we are asking:

"Is today's date greater than or equals 3 days after the application date and less than 4 days after the application date?"

The first thing you need to do is convert your Text ApplicationDate into an actual datetime that can be read by power automate. You need to use the function parseDateTime() that will read your text format and then put it into standard datetime.

In my example for parseDateTime() I used the location fr-fr which I think is like france that uses MM/dd/yyyy.

parseDateTime(items('Apply_to_each')?['DateofApplication'],'fr-fr')

 example

parseDateTime('15/01/2024','fr-fr') will turn into '2024-01-15T00:00:00

Now that you have converted your text into a proper datetime, you can use functions to add days and compare dates.

This is my example SharePoint list I'm using.

SharePoint List and Column Types

In my example below I create variables that will hold dates that will then be compared.

All Flow Steps OverviewGet Items from List and Initialize Variables, leave the variables blank.Within the Apply to Each Set the variables for dates that will be compared.The Compose compare expressions are an example of what is being done in the Condition step. You don't need the Compose steps in your flow, I have them for the example.The final condition compares current date utcNow() to the application date + 3 days and application date +4 days.

Here are the results of the flow using that single sharepoint item.

That long example is to help understand the basic functions that are being used. It is possible to put it into a single expression (I don't recommend). A single long expression is difficult to manage if something isn't working.

All of the previous example steps in a single expression

The expression used is:

and(greaterOrEquals(utcNow(),addDays(parseDateTime(items('Apply_to_each')?['DateofApplication'],'fr-fr'),3)), less(utcNow(),addDays(parseDateTime(items('Apply_to_each')?['DateofApplication'],'fr-fr'),4)))

Hope this helps, let me know if it works for you

[cb23]

I can do, yep

[cb23]

Yes, I would like to send a person a single reminder once 3 days has passed

[cb23]

Wow, thank you for the time you have put into your responses! This is brilliant. I will try it. For your workflow, should I keep the date column as it is (text) or as a date/time formatted column? 

[wskinnermctc]

You should keep it as text.

If you change the column type to a datetime, it will be mostly the same except you do not need the parseDateTime() function since it will automatically come into PA as datetime.

I recommend using date/time type columns, but I understand it could cause other issues or difficulty with conversions or getting it from other sources like forms. Date/Times aren't always user friendly in power automate if you don't know what is happening with the values as they cross applications (forms, power automate, sharepoint). So there can be a learning curve when dealing with date/time vs text.

Basically whatever works for you is the best to use.