cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexanderUK
Advocate I
Advocate I

Flow reminder from a SharePoint List Calculated Field Date not meeting expression checks

Hi all.
 

 

I'm trying to get flow to send an email reminder on a calendar date that is listed in a Calculated Field but I can't seem to get it to send anything or get the condition checks to meet the criteria to send.

 

Essentially I have a 'S1Reminder' field that is set up as a Date/Time Calculated Field and I'm trying to get Flow to use the condition to send when S1Reminder is equal to utcNow(). At the start of the flow I get utcNow converted into dd/mm/yyyy to match up the format of the calculated field, but try as it might it won't match up the conditions if I do "equals". I'm a bit stumped!  

 

Image 1Image 1Image 2Image 2

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

Since you have multiple records to evaluating you need to do the conversion at the point where you are applying the condition, not in a loop earlier in the Flow.  REplace the s1CompletionDate dynamic content in your condition in Step #3 with the formula you used for your compose.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

11 REPLIES 11
Pstork1
Dual Super User III
Dual Super User III

You'll need to apply a similar formatting to the S1 reminder field also.  By default Date fields, even calculated ones, include both a date and a time.  So the chance that you will meet an equal condition based on a time field measured in milliseconds is almost impossible.  You need to apply the same formatting that you do to the UTCNow to get it filtered down to a text string containing a formatted date.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Thanks for your help PStork1,

 

Had a feeling that was the case, I've used the code formatdatetime(items('ComplaintsData')?['S1Reminder'],'dd/MM/yyyy') to convert S1Remind into the dd/MM/yyyy format like utcNow was set up in the previous step, and I've named the Compose process 'S1RemindConvert' (and renamed the utcNow compose process to 'utcNowConvert'. 

 

However, S1RemindConvert doesn't appear in the Dynamic content list for the condition check to match up the two values, not quite sure what's happened, any ideas?

 

Step 3Step 3Step 4Step 4

Pstork1
Dual Super User III
Dual Super User III

Since you have multiple records to evaluating you need to do the conversion at the point where you are applying the condition, not in a loop earlier in the Flow.  REplace the s1CompletionDate dynamic content in your condition in Step #3 with the formula you used for your compose.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

That did the trick! Thank you so much! 

 

For those stuck on this themselves I'll attach the end result of what I put in and got it to work for the more visual types. Step 1Step 1Step 2Step 2

Hi, 

I have the same situation, created the same flow, but the condition seems to be always false, even it should be true!

 

Any Ideas?

 

2 formulas:

 

formatDateTime(items('reminder')?['Remind_x0020_date'],'MM/dd/yyyy')
 
formatDateTime(utcnow(),'MM/dd/yyyy')
 
 

2020-02-14_17-37-54.jpg

Pstork1
Dual Super User III
Dual Super User III

Take a look at one of your recent runs to make sure that the dates actually match.  I suspect your issue is that you are comparing a date from UTC to a local timezone as stored in SharePoint.  Since those can be hours off it can actually throw the date off.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
h1k4ck
Frequent Visitor

When i check only data is shown, and even when data is the same result is false.

How to put all in local?
any other thougt?

Tnx

Have you tried adding a Convert Timezone step?

 

https://flow.microsoft.com/en-us/blog/working-with-dates-and-times/ 

 

Pstork1
Dual Super User III
Dual Super User III

Can you provide a screenshot of some of the data being used in the comparison?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
h1k4ck
Frequent Visitor

adding conversion it works!

posting the flow for other user:

 

2020-02-18_10-23-47.jpg

Pstork1
Dual Super User III
Dual Super User III

Problems working with dates are almost always an issue with the time component or a timezone mismatch.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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 (27,146)