Answered

Using Filter Query for dates 1 week old and older

Like (0) Share

Report

Posted on 16 Nov 2022 15:38:38 by Community Power Pla...

Good morning

I have an SP list that members add to frequently with a PowerApp. I would like to have a recurring flow that will take all items that are more that 7 days old and

move those items to another list "Archive"
Delete these old values in the active list

I have a flow similar to this, with the difference being the Filter Query. I don't know the syntax for the date. here is what I have so far.

All responses (10)

Answers (1)

Community Power Pla... on 21 Nov 2022 at 19:56:38

Like (0)

Report

Re: Using Filter Query for dates 1 week old and older

I guess I was unaware of this and found out unintentionally. Thank you for you help!
Pstork1 66,135 Most Valuable Professional on 21 Nov 2022 at 19:12:30

Like (0)

Report

Re: Using Filter Query for dates 1 week old and older

The difference is whether you are using the display name for the column or the internal name. For OData you need to use the internal name.
Verified answer

Community Power Pla... on 21 Nov 2022 at 18:21:57

Like (0)

Report
Re: Using Filter Query for dates 1 week old and older

I removed the space in "Date Returned" and the flow worked. I'm not sure why that made a difference

'DateReturned' lt '@{formatDateTime(addDays(utcNow(), -7),'u')}'
Community Power Pla... on 21 Nov 2022 at 17:18:40

Like (0)

Report

Re: Using Filter Query for dates 1 week old and older

Good morning,
I made that adjustment and still have the same error
Without the apostrophe on the expression:
Pstork1 66,135 Most Valuable Professional on 17 Nov 2022 at 20:32:57

Like (0)

Report

Re: Using Filter Query for dates 1 week old and older

Single quotes shouldn't be around the Field name (Date Returned). That's why its throwing an error. That's the display name. You need to look at the column details to find out what the internal name is and use that. It won't have quotes and won't have a space in it.
Community Power Pla... on 17 Nov 2022 at 18:48:45

Like (0)

Report

Re: Using Filter Query for dates 1 week old and older

I tried this and got the following error:
As you can see it does show a date that is 7 days old, but I don't know why there's a Z at the end of the time stamp
Ajinder31 530 on 16 Nov 2022 at 16:43:05

Like (0)

Report
Re: Using Filter Query for dates 1 week old and older

Hi @Anonymous , If you're not familiar much with expressions then please try following code in Filter query
'Date Returned' lt '@{formatDateTime(addDays(utcNow(), -7),'u')}'

--------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Pstork1 66,135 Most Valuable Professional on 16 Nov 2022 at 16:32:34

Like (0)

Report

Re: Using Filter Query for dates 1 week old and older

I suspect you aren't entering the formatDateTime() and AddDays() functions using the Expressions tab of the dynamic content box. It looks like you are just typing them in in the screenshot.

The 'u' is the standard ISO8601 universal sortable DateTime format.
Community Power Pla... on 16 Nov 2022 at 16:01:08

Like (0)

Report

Re: Using Filter Query for dates 1 week old and older

I still receive a "Query not valid" error. I must be missing something else somewhere.

What does the 'u' signify?
Pstork1 66,135 Most Valuable Professional on 16 Nov 2022 at 15:51:38

Like (0)

Report
Re: Using Filter Query for dates 1 week old and older

Since you want items that are older you want to turn your OData query around

'Date Returned' lt FormatDateTime(AddDays(UtcNow(), -7),'u')

That will find all the entries where the Date Returned is prior to 7 days ago.