Unanswered

Comparing dates using power automate

(0) Share

Report

Posted on by cmspowerauto

Hello-
I'm trying to compare dates and having issues with my flow. I have a next review date in a column in an MS List (for routine work our team performs), and then compare it to today's date + 30 so that an email will be sent and to-do's added in Outlook so that upcoming work can be tracked. I've tried just comparing the dates using format, I've tried changing it to integers, etc. Could someone assist with what is wrong with my flow. I'm sharing a few pictures of the different pieces of the flows that I've tried, but note that in this set-up that you see, really the only thing that is working right now is that it is going to the MS List, it's pulling the data, and I'm trying to compare the dates. The first few steps of the flow I just haven't deleted in case it turns out I need to use them for some reason.

My last item is returning this error below.

Column from MS List: Next Review Date

The left side of my comparison:
addDays('1900-01-01T00:00:00Z',sub(int(item()?['Next_Review_Date']),33))

The right side of my comparison:
addDays('1900-01-01T00:00:00Z',sub(int(item()?['utcNow()']),2))

Thank you-

Error 1. 2021-05-23 084757.gif Error 2. Screenshot 2021-05-23 084839.gif Full pictureScreenshot 2021-05-23 084923.gif

comparison then send email Screenshot 2021-05-23 090325.gif

Categories:

Using flows

I have the same question (0)

All responses (9)

Answers (0)

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report
You don't need to jump through all those hoops. You can work with the dates directly to do the math. But you do need to do your condition on a range to make sure it doesn't keep firing. Add two rows to the condition and use an 'And' to connect them. Assuming your recurrence trigger is set to daily you can use the following.

Right Side of Greater than addDays(UTCNow(),-1) Right Side of Less than UTCNow() Left Side of Both addDays(item()?['Next_Review_Date'],-30)

Using that condition the yes side of the condition will fire when the Next Review Date is 30 days in the future.

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@cmspowerauto

I agree with @Pstork1 - he is right on point. However, I believe that you need to format the output of UTCNow() to match the format in which Lists/SharePoint stores dates.

For example:

addDays(UTCNow(),-1,'yyyy-MM-dd')

Rather than UTCNow() for the right side of less than consider using:

formatDateTime(UTCNow(),'yyyy'MM-dd')

The idea that the date formats need to match for the condition to work properly.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Actually, I disagree. YOu are correct that UTCNow will return the date time in ISO 8601 format, but since you are doing AddDays() on the SharePoint date that will also be in ISO 8601 format. So formatting UTCNow to match SharePoint will actually break the comparison. Here's the output of Compose statements for UTCnow and AddDays for SharePoint. And the comparison works.

Was this reply helpful? Yes No
cmspowerauto 44 on at

Like (0)

Report

Thank you both for your input. I've tried to make these updates, @Pstork1 and it's giving an error of invalid template. I've attached a picture... Any ideas?

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report
The error means that the date value you are feeding in is null. Other than syntax the formulas are correct. I would suggest adding some compose statements temporarily to check the value of

item()?['Next_Review_Date']

Was this reply helpful? Yes No
cmspowerauto 44 on at

Like (0)

Report

I think I'm confused... I have an MS list of ~280 line values. Correct, some of them are blank for the 'Next Review Date'- which as you state is causing this Null error. But if the formula is working, shouldn't it still send the email if it hits at this 30 day review with the formula? This is where my confusion lies. I have several dates in there that are at the 30 day reminder period for today and I'm not getting any email notification?

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

The issue is that the flow will fail when it encounters the null date, probably before it ever gets to sending the first email. Is some of the Review Dates are empty you need to check that as the first row in the condition so it will evaluate to false based on that before it tries to actually evaluate the date.

Was this reply helpful? Yes No
cmspowerauto 44 on at

Like (0)

Report

Thank you for the explanation- I understand now. I went to my MS List and filled in all of the 'empty' dates, and I'm still getting a 'null' error with 'no inputs'. I have no idea what I'm doing wrong with this...

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

As I suggested. Temporarily add compose statements to show you what is coming through for inputs. That's the only way to be sure what is going wrong. Once you figure it out you can remove them.

Was this reply helpful? Yes No