Question regarding delegation and in what order those 500 rows are retrieved, or how did you fix your search problems using SPO as a datasource for big lists?

[dperez13]

So I understand if I use a function like mid, or len and my datasource (in all cases Sharepoint online list) has 501 line items, it will only look at the first 500 line items, correct? or is it that it will only return 500 line items. meaning if im looking for apple or pineapple, and the spo has 1000 line items with that searchterm, it will only return 500, or will it only look at 500 and therefore only return 500?

and in my example, I am only using search. nothing else. does that also mean that im limited to a certain limit of line items and face performance issues (something i want to avoid) for the power app? or am I safe because search can bring back 10,000 rows no problem, its only search in conjunction with non delegable terms that is the issue"?

the distinction for me being, is there anyway to have it look at the last 500 line items rather than the first?

so as my list grows, and the user of the power app wants to search for 'apple like' items, startswith apple, or contains apple (pineapple) , can I atleast bypass delegation in a way by letting the user search the 500 most recent rather than the 500 oldest?

or is that behind the scenes stuff that we can't control?

and what is the solution to this?  i use terms that can bring back an infinite number of rows from SPO (so delegable terms) and then once I have those items retrieved unlimited, so 1000 items with Apple in them, store those in a collection, and have the user then do non delegable search stuff on them afterwards from wthin power apps?

meaning use delegable terms only to my SPO list, then store that in a collection, and then do the non-delegable stuff that POWER FX can do, like mid/len as the aforementioned examples?

edit:  from my understanding, SEARCH is a delegable term. 

Filter functions

Filter, Search, First and LookUp can be delegated.

yet im  seeing a delegation warning. meaning the issue isnt that im trying to do non-delegable search terms, which flat out dont work, but that im trying to return too many items . so the solution first is to let the user pick a category. and then type into the gallery search box. so first use filter to narrow down the dataset so the search function is always going to accurately pull in everything. is this right? 

[mmbr1606]

hey @dperez13 

you can increase the delegation limit up to 2.000 (in the app settings in the studio). It means that it will only search the first 500 or 2000 items of your list. if they are 501+ oder 2001+ all the above wont show up.

but it also depends which column types you are setting up. not all column types are delegabel. Which column types are you using?

These 3 are delegabel:

• Single Line of Text
• Number
• Date and Time

Problematic fields/Complex:

Lookup,Choice,Boolean,Person or Group,Calculated,Image

non delegabel operators:

search,in,Not(),Isblank

Even if you have less than 2,000 records, you will still get a Delegation warning.

Collections is also a delegation issue workaround. 

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings