Filter delegation not working for 'in' operator with Azure DB

[Anonymous]

Hi,

"Part of this formula cannot be evaluated remotely. Right side of "in" operator is not a column name"

What does "not part of a column name" mean? I'm trying to fill a PowerApps collection by filtering an Azure DB by another collection. The function is not being delegated to Azure SQL Server. Is it possible to filter by a collection?

I've rewritten my filter function to try to remove as much logic from it as possible and place more work on other controls. I have three dropdowns that each act as AND filters by changing the content of their respective DropDownXSelections collection. I then use the collections to filter the data I'm hoping to return.

ClearCollect(
    recScope3, 
    Filter(
        '[dbo].[Open_Orders]',
        Text_Purchasing_Group in DropDown1Selections,
        Text_Branch_Plant_Name in DropDown2Selections,
        ID_Owner in DropDown3Selections
    )
);

The filter function is not being delegated and the returned recScope3 collection only represents a 500 record subset of the data. I can't think of a way to not have a collection be the filter criteria for my application and use a "column name" as the delegation error suggests.

If the solution for Why is delegation not working on this app work there, I don't see why mine isn't working.

Thanks!

[Anonymous]

Putting the Azure DB column name on the right side of the "in" operator helps, but I still don't know if there is a way to use a collection as a criteria.

For anyone else out there looking, I got it working if I use a free text input and Search function.

ClearCollect(
    recScope3, 
    Search(
        '[dbo].[Open_Orders]',
        TextInput.Text,
         "Text_Purchasing_Group", "Text_Branch_Plant_Name", "ID_Owner"
    )
); 

This isn't my favourite work around, because users will have no idea what data options to search for before trying to return data. This will also increase the amount of unecessary queries on the server.

[nickduxfield]

Your aim is to filter a collection using 3 other collections?

See my answer to: https://powerusers.microsoft.com/t5/PowerApps-Forum/Filter-a-filter/m-p/54144#M22414

I've simplified by suggestions without understanding your data model.

Perhaps you can identify why you need 3 collections as your filters, are they lists of: Purchasing Groups?, Plant Names? OwnerIDs? and how to they relate to the recScope3? Is there a column that binds them?

Do you have 3 dropdowns?

DropDown1Selections,xxx2,xxxx3

If you want to filter directly using the dropdowns you will need to identify another column to ensure they are grouped up to 500 items. Possibly a month column?

ClearCollect( recScope3, Filter( '[dbo].[Open_Orders]', Text_Purchasing_Group=DropDown1.Selected.Value, Text_Branch_Plant_Name=DropDown2.Selected.Value,ID_Owner=DropDown3.Selected.Value, Month=DropDown2.Selected.Value ) );

//------------------------------//

However, if your data model is like: https://powerusers.microsoft.com/t5/PowerApps-Forum/Filter-a-filter/m-p/54144#M22414

Would you need to:

OnSelect for each DropDown: ClearCollect a filtered collection for the collections of DropDown1Selections,DropDown2Selections,DropDown3Selections first.

You will need a column to compare or relate the collections to your recScope3. I take it the definition is Text_Purchasing_Group, Text_Branch_Plant_Name,  ID_Owner. We need to know your starting point.

ClearCollect( recScope3, Filter( '[dbo].[Open_Orders]', Text_Purchasing_Group=DropDown1Selections.Text_Purchasing_Group, Text_Branch_Plant_Name=DropDown2Selections.Text_Branch_Plant_Name, ID_Owner=DropDown3Selections.ID_Owner ) );

I believe i have been able to filter by collections in the past but i needed to change my syntax to reference the column in the CollectionFilter against the columns in the SourceCollection.

I used this syntax:

SourceColumnName=CollectionFilter.CollectionFilterColumnName

You may also like to try || for AND, but I would only usually use that when filtering with a textinput. I think this is why you get "Part of this formula cannot be evaluated remotely. Right side of "in" operator is not a column name"

it may be looking for:

Text_Purchasing_Group in DropDown1Selections.Text_Purchasing_Group

You didnt tell it what column in the collection to compare.

Other ideas is to create a filter screen sequence that brings the 3 drop down colums up real big on the screen with a InfoNote and a big FILTER icon. you can use the OnSelect propertes to ClearCollect the 3 Collections. Then you can redirect to the filtered gallery.

If you think it takes long, you can add a processing indicator, then a Tick.

In terms of the 500 item limit. You will have to identify another parameter to categorize by that equals up to 500 items, say, month? Staus, In Progress, Complete? Assigned to just Me?

Let me know how you get on, I'm interested.