cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bynumhoekstra
Frequent Visitor

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

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 listProjects listProject Statuses 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 warningFilter 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.IdDelegation 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? 

10 REPLIES 10
gebhardd
Frequent Visitor

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.

 

gebhardd_1-1658496625695.png

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.

gebhardd_11-1658498039639.png

 

gebhardd_2-1658496736516.png

 

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

gebhardd_4-1658496784123.png

Added a new Listbox control to the Screen

gebhardd_5-1658496844282.png

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.

gebhardd_6-1658497124940.png

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.

gebhardd_7-1658497409171.png

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'?

gebhardd_12-1658498306489.png

 

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

gebhardd_8-1658497672898.png

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. 

gebhardd_10-1658497863179.png

 

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.

gebhardd_9-1658497847871.png

 

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.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,950)