cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScottRMeredith
Helper I
Helper I

Filter SharePoint List off comparison of calculated date field

I have a SharePoint list with an Assigned Date field.  This field then populates a calculated field of Due In Ops (Assigned Date + 14 days).  I also have a field Date In Ops that gets updated when the report is turned in.  In my flow I am trying to create an email reminder if a report is not turned in, and it is 2 days before the Due In Ops date.  I have my Get Items action set and can get the appropriate records if the Date in Ops field is null (Date_x0020_In_x0020_OPS eq null).  The problem I am having is getting the additional condition to account for 2 days prior to the due date.  From what I have been able to gather, using the Due In Ops (calculated) may be out of the question.  I have tried to add 12 days to the Assigned Date and compare it to utcnow()

addDays('Assigned_x0020_Date',12,'yyyy-MM-dd') eq utcNow('yyyy-MM-dd')

 I get this error....

Unable to process template language expressions in action 'Get_items' inputs at line '1' and column '9424': 'In function 'addDays', the value provided for date time string 'Assigned_x0020_Date' was not valid. The datetime string must match ISO 8601 format.'.

 

I am using the field references from the edit field URL on the SharePoint Site.

 

WF1.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

 

@ScottRMeredith 

 

A couple more things then.  The output of assigned date is definitely a date yyyy-MM-dd and so you will need to use formatdatetime as you can see it's outputting the date with time. 

Assigned_x0020_Date eq 2021-03-03T00:00:00.0000000" is not valid

 

You will have to use

Assigned_x0020_Date eq 'formatdatetime(adddays(utcNow(),-12),'yyyy-MM-dd')'

 

I think you will have to put your dynamic value in apostrophes too.

 

apos.PNG

 

Give that a go and let me know how you get on.

 

Thanks

 

Damien

 

View solution in original post

7 REPLIES 7
DamoBird365
Super User
Super User

Hi @ScottRMeredith 

 

I think you need to switch it the other way around...

 

Assigned_x0020_Date eq adddays(utcNow('yyyy-MM-dd'),-12)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

Thank you for the help.

 

So I tried to add an and and then just pasted in the formula you provided, then I also add the function.  Neither worked unfortunately...WF1.PNG

 

The error it spits out with the above setup is....

 

The expression "Date_x0020_In_x0020_OPS eq null and Assigned_x0020_Date eq 2021-03-03T00:00:00.0000000" is not valid. Creating query failed.
clientRequestId: 01e18ae0-6670-43e9-b5f3-e1acdcc65f9c
serviceRequestId: 01e18ae0-6670-43e9-b5f3-e1acdcc65f9c

 

I have a feeling the date formatting yyyy-MM-dd needs to be tweaked, but not sure how.

 

Thank you

@ScottRMeredith 

 

The best way to test it is to remove the filter and potentially set the top count to 10 (or less) and then look at the history output for the two date fields to see what the format is.

 

You can then use the expression formatdatetime(adddays(utcNow('yyyy-MM-dd'),-12), 'MM-dd-yyyy') to reformat the date.

 

Damien

ScottRMeredith
Helper I
Helper I

When I have successfully run the flow just to check for null dates, I have "downloaded" the Get Items data.  On the following screen it had the date formatted as: 

,"Assigned_x0020_Date":"2021-03-01",

 So I think the original formatting was correct based on this.

DamoBird365
Super User
Super User

 

@ScottRMeredith 

 

A couple more things then.  The output of assigned date is definitely a date yyyy-MM-dd and so you will need to use formatdatetime as you can see it's outputting the date with time. 

Assigned_x0020_Date eq 2021-03-03T00:00:00.0000000" is not valid

 

You will have to use

Assigned_x0020_Date eq 'formatdatetime(adddays(utcNow(),-12),'yyyy-MM-dd')'

 

I think you will have to put your dynamic value in apostrophes too.

 

apos.PNG

 

Give that a go and let me know how you get on.

 

Thanks

 

Damien

 

View solution in original post

ScottRMeredith
Helper I
Helper I

Thank you, that worked.  My only issue right now is, it is late in the day and with the time zone difference I had to change my data on my list to meet the criteria.  Not an issue for when I go live with the WF as it will run early in the morning.  Thanks again.

That's brilliant @ScottRMeredith 

 

For the future, you could also explore the following action that will allow you to choose the timezone:

https://support.microsoft.com/en-us/topic/converting-time-zone-in-microsoft-power-automate-8bce2441-...

975fc420-a5e9-4288-1e31-37b5e1a659b9[1].png

 

Damien

 

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.

Users online (1,201)