Answered

Using Filter Query for dates 1 week old and older

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

Pstork1 69,110 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

What does the 'u' signify?

Was this reply helpful? Yes No
Pstork1 69,110 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Ajinder31 530 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Pstork1 69,110 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Good morning,
I made that adjustment and still have the same error
Without the apostrophe on the expression:

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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')}'

Was this reply helpful? Yes No
Pstork1 69,110 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

I guess I was unaware of this and found out unintentionally. Thank you for you help!

Was this reply helpful? Yes No