cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YatinMumbai
Advocate IV
Advocate IV

Send email reminder after 15 days of Registration Date, if Filing date is blank (filing not done).

I need help with following scenario - 

SharePoint custom list has 2 date columns: Registration Date and Filing Date
Filter Items from the list using this criteria :
Registration Date + 15 Days is equal to today (utcnow function) and Filing Date is blank.
- I am not getting the right OData statement to filter the items as required.

Result expected: Scheduled flow to run daily at 8am to Send email with HTML table of filtered items to one supervisor.

Steps / Actions in Flow: Recurrence trigger ->  Get Items (with filter) -> Select using value from Get Items (list of items) to map required columns -> Create HTML table using output from Select -> Send Email using output of HTML table in the body. 

Also, suggest if there is more efficient way to implement the steps/actions.

1 ACCEPTED SOLUTION

Accepted Solutions
YatinMumbai
Advocate IV
Advocate IV

UPDATE: Solution Found

 

Instead of trying to add days to SharePoint column which would have required a complex expression, I subtracted 15 days from utcnow() and compared using "le" operator (less than or equal to).

RegistrationDate le '@{addDays(utcNow(),-15)}'

YatinMumbai_0-1624807967978.png

Another interesting way is to create a view in SharePoint with required filters and ordering, then select it in "Limit Columns by View" drop-down. That way you don't need to to provide OData filter query which can be difficult or complex, and filtering is managed in SharePoint's simpler UI.

More optimzed solutions are always welcome.

View solution in original post

3 REPLIES 3
Tascin
New Member

Range operators:

  • gt: Test whether a field is greater than a constant value
  • lt: Test whether a field is less than a constant value
  • ge: Test whether a field is greater than or equal to a constant value
  • le: Test whether a field is less than or equal to a constant value

OData comparison operator reference - Azure Cognitive Search | Microsoft Docs

Those are the available operators to use. There was a great blog entry from Georgiev;
PowerAutomate and SharePoint OData filter queries (velingeorgiev.com)

 

I would suggest to use a compose action to get the registration date and then to the get items action:
Registration Date eq 'from subtract time action' AND Filing Date ne null

 

Subtract from Time Action explained: Solved: subtract from current time - Power Platform Community (microsoft.com)

 

In thatw ay you probably don't need the separate filter anymore 🙂

Thanks for your inputs and the links are very useful. I am aware of the operators and condition required. However, I am missing something in the syntax of the filter expression, which is why the filter is not working. That's where I need help 🙂 

YatinMumbai
Advocate IV
Advocate IV

UPDATE: Solution Found

 

Instead of trying to add days to SharePoint column which would have required a complex expression, I subtracted 15 days from utcnow() and compared using "le" operator (less than or equal to).

RegistrationDate le '@{addDays(utcNow(),-15)}'

YatinMumbai_0-1624807967978.png

Another interesting way is to create a view in SharePoint with required filters and ordering, then select it in "Limit Columns by View" drop-down. That way you don't need to to provide OData filter query which can be difficult or complex, and filtering is managed in SharePoint's simpler UI.

More optimzed solutions are always welcome.

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.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Top Solution Authors
Users online (1,093)