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? 

[mdevaney]

@DanielMcnair 
What type of datasource are you using (Excel, SharePoint, SQL, etc?).  What column type are 'Actual number' < 'Desired number'?  I want to ensure PowerApps is reading these fields as a number and not text.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[mdevaney]

@DanielMcnair 
Is your problem now solved?  You haven't returned to the forums since I responded to your question.

[Anonymous]

I have been using this app for a few months now and all of the sudden i am getting this error and im not able to update already existing items in my SharePoint list. 

Here is my formula:
items is my collection, 'Sales Rep Transfer Inventory' is my SP list. 

ForAll(
    items,
    Patch(
        'Sales Rep Transfer Inventory',
        LookUp(
            'Sales Rep Transfer Inventory',
            Title = items[@Title]
        ),
        {
            ClubStatus: "TRANSFERRED",
            TransferInAcct: AccountNumberIN.Text,
            TransferedInName: SignName.Text,
            TransferInEmail: SignEmail.Text,
            PONumberIn: PONumIn.Text,
            TransferDateIn: Today(),
            DebitAmount: Price,
            Credit_x0020_Status: ""
        }
    )
);

any help would be appreciated!

@mdevaney 

[natecentric]

Same here @Anonymous ! My patch is very similar to what you have, was working, and started failing a few days ago. Any one have thoughts?

[PatrickOToole]

I'm having the same issue. I think this was working 1-2 weeks ago, and now I'm getting the error. It happens for me with ID and Title

Generic version of my formula using ID

ForAll(collectionName,Patch(SPDataSource,LookUp(SPDataSource,ID=collectionName[@ID]),{fieldName:someTextValue}))

Generic version of my formula using Title:

ForAll(collectionName,Patch(SPDataSource,LookUp(SPDataSource,Title=collectionName[@Title]),{fieldName:someTextValue}))

Replacing collectionName[@ID] with a hard-coded number works perfectly fine so I think I have the syntax correct.

Edit: I was able to find a workaround. My use-case was using a gallery with check-boxes. Option 2 (using an additional label within the gallery) in this article worked for me:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

Instead of referencing a net-new collection, I only referenced the existing gallery, and used a label within the gallery to filter by ID. I had to wrap the label with the Value() function so that it converted the text into a string. It looked something like:

ForAll(Filter(GalleryName.AllItems,<filter criteria>),Patch(SPDataSource,LookUp(SPDataSource,ID=Value(lableName.text)),{fieldName:"someTextValue"}))

[natecentric]

Second this @PatrickOToole ! your workaround similar to what i ended up doing. I am not using check boxes, but using the add column 'selected" trick to multiple select items in the gallery.

[Helen_BB]

we are having a similar issue! an app that has been working for around a year suddenly the Patch(Lookup)  code produces errors, something must have happened at PowerApps to cause this! 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Problems-Updating-SharePoint-List-using-ForA...

[natecentric]

Hey all, I think Shane Young said this is new, but I was able to get this to work again using the AS operator!

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators#as-operator

[gabeconsults]

Well shoot I'm using the AS operator and it's still failing. 

ForAll(
        Metrics As Updating, If(Updating.ID>0,
        Patch(
            'SPMetrics',
            LookUp('SPMetrics', ID = Updating.ID),
            {Somefield:somevalue}
            )
      )

'Metrics' is a collection in the app and SPMetrics is a SharePoint list. Still getting the red squiggly and a fetching error.