Forum Discussion
Get Files Filter Query
Hi!
I am trying to create a flow using Get Files filter query, but i am having so many problems!!! Help Please
What i want to do is the following:
When a page is modified, and the Review Date is grater than the Approval date, send an email out.
I tried using directly a condition: ReviewDate grater than Approval date, this gives me an error every time.
"The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. Creating query failed"
tried formatting the date, same, and i think the problem is because it picks up the first few rows of that document library where the Approval date is empty.
So i thought i could use filter query to ignore those where approval date is empty i used this Approval_x0020_Date ne null and FSObjType eq 0 and ToFilterNotifications ne 'Main'
so that it will leave out those with empty approval date, folders and those with a Main classification.
Still the same.
i stripped it all out, and then only left "Approval_x0020_Date ne null" and it brings me all the ones where that filed is empty
i tried ignoring that and instead using a condition: Review Date > Approval Date. Error about date format.
Formatted the date with an expression....same error.
Looks like for some reason it is not seeing the info inside that column.
Any ideas please??
thank you
2 Replies
Why You Saw the Error:
Review_x0020_Date gt Approval_x0020_Date
in your filter query isn’t allowed because OData expects a literal on the right-hand side—not another column. This misinterpretation leads to the DateTime parsing error.Empty (Null) Values:
When some items have an empty Approval Date, the query might misbehave if it isn’t properly filtered out.
-------------------------------------
Field-to-Field Comparisons Are Not Supported:
Work Around This:
Since you can’t compare two date columns directly in the Get Files filter query, you’ll need to split the logic into two parts:
You can use the filter query to retrieve only those items that are “eligible” for further testing
FSObjType eq 0 and Approval_x0020_Date ne null and ToFilterNotifications ne 'Main'Null Comparisons:
In many cases, filtering for ne null works. If you run into issues with that, you can sometimes work around it by using a constant that you know valid items will exceed. For example, if you know no valid Approval Date is before January 1, 2000, you might use:Approval_x0020_Date gt datetime'2000-01-01T00:00:00Z'
-------------------------------
Compare Dates Within the FlowAdd a Condition Action: In your Flow, after the Get Files action, use a condition to compare the values.
Use an Expression to Compare Dates: Convert or format the dates if needed (for example, using the utcNow() or formatDateTime() functions).
EXAMPLE:
formatDateTime(item()?['Review_x0020_Date'], 'yyyy-MM-ddTHH:mm:ssZ')
and compare it with:
formatDateTime(item()?['Approval_x0020_Date'], 'yyyy-MM-ddTHH:mm:ssZ')Hi MVCuser , I am wondering if creating a new calculated column where you compare these two dates and then filter by the new column using the SP API request _api/web/GetListUsingPath(PATH)/RenderListDataAsStream?&FilterField1=CalcDate&FilterValue1=1&FilterType1=Boolean will work in your case.
