Delegation warning on SharePoint list lookup column .Id versus .Value

[bynumhoekstra]

I'm having an issue with delegation and, after having read all of the documentation, I have yet to see a description of the exact issue that I'm seeing. I know that the PowerApps filter function can be delegated to SharePoint for SharePoint lookup columns, but it seems that this functionality is limited to when you use the ColumnName.Value and not the ColumnName.Id. Let me describe a simple example. 

There are two lists, Projects and Project Statuses. The Project Statuses list has a lookup column named Project, and it looks up to the Title column of the Projects list.

 Projects listProject Statuses list

In this example, I want to have PowerApp that filters the Project Statuses based on the selection from the Projects list, thus using a gallery Filter to show the items from Project Statuses. 

As described above, I have no problems if I do a filter like this using the Value attribute of the lookup column:

Filter('Project Statuses', Project.Value = "Project MNO")

Filter by Project.Value; no delegation warning

However, if I want to use the Project.Id this would then throw a delegation error. In the simple example that I'm using this is less of an issue if I just use the SharePoint settings to require a unique value for the title field, but this would not be a viable option if I had a list that couldn't enforce unique values on the column used in the lookup column. Here's the delegation error when I change to Project.Id: 

Filter('Project Statuses', Project.Id = 5)

Delegation warning with Project.Id

For the purposes of the solution that I'm managing, the 2000 item limit will probably be met fairly quickly, and requiring  unique values on the source list column is not an option. Does anyone have any ideas on how to work around this limit, or suggestions on what could be done differently? 

[gebhardd]

Greetings,

I understand this post is old; however, delegation has not changed in all these years either and this is the first search engine hit when attempting to work with PowerApps and delegation.

Through some testing, and I would love if you could prove me wrong because I cannot stand SharePoint > Power App delegation, this does NOT work and here is how I tested it.

**EDIT: My following test, tests the delegation utilizing a Listbox; however, it appears that delegation will work with a Gallery and a DataTable but does not work with the Listbox, Dropdown box, or the Combobox.  I understand the Combobox because the Search function is not a delegate function, but why the same data source performs differently in the Listbox and Dropdown box, I did not see those mentioned specifically in the Microsoft documentation.

Create a "LookupSharePointList" with 2 items.

Create a "MainSharePointList" with 15 items.  Added a Lookup Column to the "LookupSharePointList" using the ID.  Just for fun, I also added the lookup's "title" reference column as well.

Created a new, blank, Canvas app in PowerApps and added my two SharePoint lists as data sources.

Added a new Listbox control to the Screen

Updated the Items property to filter the MainSharePointList based on the LookupColumn.Value equal to the Text value of 1.  If you do it as a numerical value of 1, as pointed out in previous comments, you receive an "Incompatible types for comparison. These Types cannot be compared: Text, Number." warning.  With this filter, I get the 10 items I am expecting to get and all *appears* right with the app because I do NOT receive a delegation warning.

However, this result is expected because the default Data row limit in Power Apps is 500 rows; therefore, as long as I am returning under 500 rows, I am good to go.  We can test for delegation issues, however, if we modify the Data row limit.  Under File > Settings > General, you can set the data row limit to be from 1-2,000 currently. We will set our data row limit to 5.

Now, if we go back to the Items property of our Listbox, remove the end bracket ")", re-add it, and then tab out of the property window to "refresh" our data source. We now only receive 5 items back. Does this show that the Filter function can not be delegated or does this show that any SharePoint List is a 'server-based connection where delegation is not supported'?

Going back into File > Settings > General, I modified the Data row limit to 7

Back in the Items property of the Listbox, remove and add the last bracket back to the Filter function and tab out of the property window to update, and see that we now have 7 items listed. 

I also tried to use the LookupColumn in a different way, equaling the Id to 1; however, this does provide the delegation warning and still only produces the 7 data row limited number of columns.

So what is the verdict here?  It appears that the Filter function on a SharePoint list via the Lookup column will not delegate.  I am building an app for our non-IT departments to use as a help desk solution and fear that this will not be a viable long-term solution without using Power Automate to pull in larger datasets which is just a mess and shouldn't be the solution.  I understand the logic behind delegation, but let me decide if the "lag time" is worth it or not.

So hopefully this test will aid someone else in their quest to make sure the app they are building works as they expect it to.  Do your own test(s) and let me know if you get this to actually work. Finally, don't get me wrong, delegation is pretty awful, but I really love Power Apps and what it, with a little imagination, has brought to my company.