cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Create dynamic variable based on current date to compare against date in sharepoint list - for reminder using recurrence

I'm hoping that someone can advise me on how to create a dynamic variable based on the current date that I can compare against a date stored in a sharepoint list. My scenario is that I need to send out a yearly reminder to renew an approval request. I need my flow to check a list daily, identify if there items for which the date in my 'Date Approved' column match the current date, and if so, send an email reminder for each.

 

I haven't worked much with scheduled flows or the recurrence trigger, so any advice on how to get started and/or links to tutorials that directly apply to my scenario would be appreciated. Thanks in advance! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Based on the error message the filter you used is completely different from what I sent you, it seems you're not even using expression, it's missing commas and apostrophes....

If your column contains also time, then you can't use 'eq' in the filter, you need to define a range, expiration date is greater or equal today and less than tomorrow, that will give you all items from today's 00:00 until 23:59.

 

ExpirationDate ge 'utcNow('yyyy-MM-dd')' and ExpirationDate lt 'addDays(utcNow(),1,'yyyy-MM-dd')'

 

 

View solution in original post

5 REPLIES 5
Highlighted
Helper III
Helper III

To clarify, the values I get when I try to add the current date and the date from my column as variables are not comparable. The date from the column includes the time, even though time is turned off for that column. 

Screen Shot 2020-10-29 at 16.02.00.png

Screen Shot 2020-10-29 at 16.01.44.png

Highlighted
Solution Sage
Solution Sage

Power Automate has an expression utcNow() that gives you the today's date. I'd use filter:

 

DateApproved eq 'utcNow('yyyy-MM-dd')'

 

with the assumtions:

  • your 'Date Approved' column is Date Only
  • DateApproved is the internal name of the 'Date Approved' column

The actual reminder sending flow is quite straightforward, unless you need some other functionality than just to send a notification.

image.png

 

Edit: so 'Date Approved' == ExpirationDate? It doesn't make sense why it would contain time if it's Date Only column.

Highlighted

Thank you! 

 

I wasn't clear at first, the problem is that even though the column is set as Date only, it returns a value with time included (you can see in the screenshot below. When I tried the formula you suggested I got this error (I assume because the values weren't comparable). 

 

The expression "ExpirationDate eq. formatDateTime(utcNow()2020-10-29)" is not valid. Creating query failed.
clientRequestId: daaab908-4d7d-484d-94ce-aa5cbbd6156a
serviceRequestId: daaab908-4d7d-484d-94ce-aa5cbbd6156a

Screen Shot 2020-10-29 at 16.02.00.png

Highlighted

Based on the error message the filter you used is completely different from what I sent you, it seems you're not even using expression, it's missing commas and apostrophes....

If your column contains also time, then you can't use 'eq' in the filter, you need to define a range, expiration date is greater or equal today and less than tomorrow, that will give you all items from today's 00:00 until 23:59.

 

ExpirationDate ge 'utcNow('yyyy-MM-dd')' and ExpirationDate lt 'addDays(utcNow(),1,'yyyy-MM-dd')'

 

 

View solution in original post

Highlighted

Thank you so much! This works 🙂 As you can tell I'm very new with using expressions - I appreciate you taking the time to help! 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (9,482)