cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Huntson
Frequent Visitor

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

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.

 

flow.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Amanthaper
Responsive Resident
Responsive Resident

Hi @Huntson,

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.

PAUS_SPDATEexcelfun2.png

PAUS_SPDATEexcelfun1.png

(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.
------------------------------------------------------------------------------------------------------

 

View solution in original post

7 REPLIES 7
Amanthaper
Responsive Resident
Responsive Resident

@Huntson 

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.
Amanthaper_0-1602006765010.png

 

 
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

@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
Responsive Resident
Responsive Resident

@Huntson 

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.

Many thanks for your help @Amanthaper . 

Following your direction I have given it a try:

 

flow1.png

 

 

flow2.png

 

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
Responsive Resident
Responsive Resident

Hi @Huntson,

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.

PAUS_SPDATEexcelfun2.png

PAUS_SPDATEexcelfun1.png

(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.
------------------------------------------------------------------------------------------------------

 

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

Amanthaper
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,561)