Showing results for 
Search instead for 
Did you mean: 
Advocate II
Advocate II

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



"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.



        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.




Advocate II
Advocate II

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

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.


         "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.

Responsive Resident
Responsive Resident

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

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

See my answer to:


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?



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:

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:


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.

Helpful resources

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!


Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.


Super Users Coming in August

We are excited for the next Super User season.


Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,951)