cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tarjani
Advocate III
Advocate III

Filtering using 'x' days before a date field

Hi all,

 

I'm looking for help in selecting records by an expiry field.  I"m needing to send three emails to clients, and the first email is 6 weeks/42 days before the expiry, then another at 4 weeks/28 days before expiry and the final at 2 weeks/14 days before expiry.

 

So far the best way that I've seen to do this is using an expression of greater than and less than.  Which I think would be using the context of Greater Than today & Less than today + 42 days 

 

Would this work towards what I am doing?

This is the formula that I've seen with this:

(starttime gt addDays(body('UTC_Start_of_Day'),1,'yyyy-MM-ddTHH:mm:ssZ')) and (starttime lt addDays(body('UTC_Start_of_Day'),2,'yyyy-MM-ddTHH:mm:ssZ'))

https://www.arbelatech.com/insights-resources/blog/microsoft-flow-how-to-query-dynamics-365-date-tim... 

1 ACCEPTED SOLUTION

Accepted Solutions
v-duann-msft
Community Support
Community Support

Hi @Tarjani 

 

Thank you for posting.

 

According to your description, you would like to send several emails to user depends on expiry date. If I misunderstood, please kindly let me know.

 

I will use date info from SharePoint list as data source for your reference:

v-duann-msft_0-1616656057350.png

 

Seems you are using filter query, but I use three level conditions instead for better to understand.

Flow overview:

v-duann-msft_1-1616656057357.png

In first condition, we need to check whether today equals 6weeks before the ExpiryDate value. If yes, we send first email notification 6weeks in advance.

 

v-duann-msft_2-1616656057361.png

In condition 2, we need to use similar condition to check whether today equals 4weeks before the ExpiryDate value. If yes, send 2nd email 4 weeks in advance.

 

v-duann-msft_3-1616656057369.png

In condition 3, configure condition for 2 weeks in advance.

 

Related expression:

formatDateTime(items('Apply_to_each')?['ExpiryDate'],'yyyy-MM-dd')

 

formatDateTime(addDays(utcNow(),42),'yyyy-MM-dd')

formatDateTime(addDays(utcNow(),28),'yyyy-MM-dd')

formatDateTime(addDays(utcNow(),14),'yyyy-MM-dd')

 

Hope the content above may help you.

 

Best regards,

Anna

View solution in original post

1 REPLY 1
v-duann-msft
Community Support
Community Support

Hi @Tarjani 

 

Thank you for posting.

 

According to your description, you would like to send several emails to user depends on expiry date. If I misunderstood, please kindly let me know.

 

I will use date info from SharePoint list as data source for your reference:

v-duann-msft_0-1616656057350.png

 

Seems you are using filter query, but I use three level conditions instead for better to understand.

Flow overview:

v-duann-msft_1-1616656057357.png

In first condition, we need to check whether today equals 6weeks before the ExpiryDate value. If yes, we send first email notification 6weeks in advance.

 

v-duann-msft_2-1616656057361.png

In condition 2, we need to use similar condition to check whether today equals 4weeks before the ExpiryDate value. If yes, send 2nd email 4 weeks in advance.

 

v-duann-msft_3-1616656057369.png

In condition 3, configure condition for 2 weeks in advance.

 

Related expression:

formatDateTime(items('Apply_to_each')?['ExpiryDate'],'yyyy-MM-dd')

 

formatDateTime(addDays(utcNow(),42),'yyyy-MM-dd')

formatDateTime(addDays(utcNow(),28),'yyyy-MM-dd')

formatDateTime(addDays(utcNow(),14),'yyyy-MM-dd')

 

Hope the content above may help you.

 

Best regards,

Anna

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,767)