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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (18,439)