Reading dates from a column and comparing them to system date to trigger an email

[Anonymous]

Hi Power experts,

Im trying to create a flow which on a monthly basis reads the dates in the column "Expiring" (see screenshot) and if any of the dates in that column is less than month away from the current date, it triggers an email which includes the column(s) information.

Example: Using the table in the screenshoot if today was 2021-09-18 and the flow was trigged, it would create an email which will include the info in rows 3 & 4 (less than a month away from the current date) but not row 1 (over a month away).

I've created simple flows, so I know how to create a Recurrence at the beginning and also how to add an output into an email but the part of reading columns values and comparing them to the system date to return the entire row value is another level for me so any help is much appreciated.

[Amanthaper]

@Anonymous 

Hi,

How are you defining "less than a month"? By a specific number of days (always 31) or by the corresponding date of the last month (10th Jun = 10th May). What would be expected in those months where the past month dos not have a corresponding day in the past month (31st Oct but no 31st Sept)?

If you always want to define "less than a month" by anything within the past 31 days then this function would do it:

addDays(outputs('Compose')),-31).

Replace outputs('Compose') with your Excel column reference.

Using the above function with -30 gives the following.

Cheers,

Aman

------------------------------------------------------------------------------------------------------
If my post helps you with your problem or answers your question, please mark it Solved or Answered. This helps anyone with similar challenges. If you like my response, please give it a Thumbs Up.
------------------------------------------------------------------------------------------------------

Cheers

[Anonymous]

@Amanthaper 

Thanks for your response. 

Yes in my case a month can be defined like les than 31 days between both dates. 

With that function I could create a variable which contains the current date + 31 days so then I can check each value of the "expiring" column to see if expiring date is between the current date and current date + 31 days. 

However still can't figure out how to 

a. Extract all the values from the "expiring" column

b. check one by one the "expiring" values to see if the value is between the current date and current date + 31 days. 

c. for those expiring values which match that condition, return the value of the entire excel row in a email 

Any suggestion?

[Amanthaper]

@Anonymous 

Sure.

1.  Use an Apply To All action against your date column and

2.  have another Conditional action which compares each date value against your date condition and

3. get the Excel row where your condition is met or true.

I'll set up a quick example for you.

[Anonymous]

Many thanks for your help @Amanthaper . 

Following your direction I have given it a try:

Positive: The flow run without returning an error

Not that positive: It didn't return an error because the condition didn't work as expected.

I used the following functions:

Expiring is greater than "formatDateTime(utcNow())"

AND

Expiring is less than "addDays(formatDateTime(utcNow()),+31)"

Am I using the "list rows present in a table" correctly to get the values from the excel?

Are the date functions correct? how can I compare dates in Flow?

Many thanks in advance

Huntson

[Amanthaper · Accepted solution]

Hi @Anonymous,

Great effort and very close. Apologies if I missed to mention the condition should be in an Apply to Each loop.

Your condition logic and date formulas are sound but date interactions among SP, PA, Excel are funky. Best to convert all dates/times to the same format.

I recreated your scenario and was able to get expected results.

Here's the working example.

(1)  formatdatetime(items('Apply_to_each')?['Expiring'],'yyyy-MM-dd')

(2)  formatDateTime(utcNow(),'yyyy-MM-dd')

(3)  formatDateTime(addDays(utcNow(),31),'yyyy-MM-dd')

I used simple 'yyyy-MM-dd' format but if you need all characters evaluated from your expiring date then you can use this 'yyyy-MM-ddTHH:hh:mm.fffffff'

Cheers,

Aman

------------------------------------------------------------------------------------------------------
If my post helps you with your problem or answers your question, please mark it Solved or Answered. This helps anyone with similar challenges. If you like my response, please give it a Thumbs Up.
------------------------------------------------------------------------------------------------------

[Anonymous]

Awesome. it works !! Many thanks. I wouldn't have done it without your help.  

[Amanthaper]

Your very welcome. You did all the the heavy lifting.