Fetching items failed. Possible invalid string in filter query. Create collection based on the column values in a sharepoint list

[DanielMcnair]

I have a SharePoint list, and I want to populate a gallery from this list but based on the condition in 2 columns. These 2 columns are number fields. I want to only get the items where field 2 is less than field 1. My ClearCollect looks like this: 

ClearCollect(dates, Filter('Custom Dates', 'Actual number' < 'Desired number'));

So I want to get all items where the actual number for that item is < the Desired number field. When I do this I don't get any error in the designer, however when I click "Alt" than try to click the button, I get the error saying: 

Fetching items failed. Possible invalid string in filter query. 

and that's it. Is there a solution to this? 

[Anonymous]

It worked for me! Thanks!

[dlprentice]

@natecentric Thank you!!!

[_severynm]

Using As worked beautifully for me!

I think what is happening is that the scope of the gallery item (ThisRecord) is lost within the Lookup, so the ThisRecord now references the lookup item instead of the gallery item. Giving the gallery item a specific name solves this problem. After figuring this out I came across this article:

---

ThisItem and ThisRecord are great, but they only make the innermost record available. In nested situations, it is impossible to reference the record in an outer scope. Further, it isn’t always obvious what they are referring to, requiring the reader to look back up through the formula or Items property to understand the source of the record.

Drawing on SQL syntax, we have adopted the As operator to name these records.

---

[rodger-st]

Hi, any idea on the solution? I'm having the same issue right now, AS operator doesn't seem to help.

[_severynm]

I looks like you're filtering a table but your criteria is set to only look at the current record, which will not work. Try this instead:

Filter(Requests, 'Start Date' <> 'End Date')

[KatheB]

Hi Patrick, three years later your answer gave me a lot of light, I am writing this because I spent the whole afternoon looking for a way to solve this not expected but great inconvenience.

Your answer is great, however, the VALUE clashes with delegation, in my case it is a list that quickly exceeds 2000 items, with which I had to add an extra step.

In my case, what I did was a copy of the column ID, I will describe it in two buttons/OnSelect

A button to fetch the data from sharepoint and create the collection by adding the copy of the column:

ClearCollect(
CollectUpdateStaffv3,AddColumns(
Filter('MOC Staff','MOC No'.Value = CBRA_Test.Selected.Title), "ID2",ID


))

Another button doing the patch of the columns I wanted to update

ForAll(CollectUpdateStaffv3,
Patch('MOC Staff',LookUp('MOC Staff',ID = ID2),

{Field1: Toggle2.Value
,'Field2Date':Now()}

I hope this helps