Power Apps Search Function Delegation Warning Workarounds
Search is one of the most requested app features, however, the Power Apps search function has one major limitation. It cannot find any results past the 2,000th row in a table due to delegation. So how do can we perform searches in Power Apps and help users find the information they need? In this article I will show you all known Power apps Search function delegation warning workarounds.
Table of Contents: Introduction: The Car Inventory Search App Make The Car Inventory Search App Write A Search Function With A Delegation Warning Search Function Delegation Warning Workarounds 1. Power Automate Delegation Workaround For The Search Function 2. StartsWith Delegation Workaround For The Search Function 3. Search + Filter Delegation Workaround For The Search Function 4. Dataverse IN Operator Delegation Workaround For Search Function
Introduction: The Car Inventory Search App
The Car Inventory Search app is used by salespeople at a car dealership to determine which cars are currently in inventory.
Make The Car Inventory Search App
Create a new SharePoint list called Car Sales Inventory with the following columns:
- Year (number)
- Make (single-line text)
- Model (single-line text)
Include this data in the list:
|1996||Dodge||Ram Van 3500|
Start a new app from blank in Power Apps Studio. Make a title bar with a text input at the top where the user can type in keywords to perform a search.
Add the Car Sales Inventory SharePoint list as a datasource then insert a gallery onto the screen…
…and use this code in the Items property.
'Car Sales Inventory'
Change the layout of the gallery to Title so there is only one label on each row. Write this code in the text property of the label. Now the gallery will display the every car’s year, make and model.
Concatenate( Text( ThisItem.Year, "0000" ), " ", ThisItem.Make, " ", ThisItem.Model )
Write A Search Function With A Delegation Warning
When a user types their search terms into the search bar the gallery below updates to show only matching results.
Update the code in the Items property of the gallery to include the Search function. The make and model columns can be searched because they are text but the year column cannot be because it is a number.
Search('Car Sales Inventory', txt_Search.Text, "Make", "Model")
The search function will produce a delegation warning because it is not included in the list of delegable functions for SharePoint. This means that by default only the first 500 rows of the Car Sales Inventory will be searched and returned or 2,000 rows if you increased the limit in advanced settings.
Search Function Delegation Warning Workarounds
There are 4 delegation workarounds for the Search Function:
1. Power Automate Delegation Workaround For The Search Function
The Power Apps search function does not support delegation. But we can build a Power Automate flow to perform a search and return the results to Power Apps. Using the SharePoint – Get Items action we can retrieve any records with a matching substring. Then we send the search results back to Power Apps and covert it to a collection with the ParseJSON function. The best part is no premium licensing is required.
Check out the full tutorial for this Search function delegation workaround here.
2. StartsWith Delegation Workaround For The Search Function
While the Search function cannot be delegated in SharePoint the StartsWith function can. This means we can make a search bar that looks at the start of every word and shows all results in the gallery. The trade-off is any words found in the middle of a text column will not be returned when searching.
To use the StartsWith delegation workaround for searching use this code in the Items property of the gallery.
Filter( 'Car Sales Inventory', StartsWith(make, txt_Search.Text) Or StartsWith(model, txt_Search.Text) )
3. Search + Filter Delegation Workaround For The Search Function
Another workaround is to pre-filter the results on some criteria that will return less than 2,000 rows and then perform the search on that smaller chunk of data. The car inventory has 3,000 rows for cars which are located in 5 cities. We know that any city selected will return less that 2,000 rows so its OK to use this workaround.
Place a dropdown control in the top-right corner of the app and use this code in the Items property to display the city names.
["Austin", "Dallas", "Fort Worth", "Houston", "San Antonio"]
Then write this code in the Items property of the gallery. The delegation warning will still appear but you can safely ignore it.
Search( Filter('Car Sales Inventory', location=Dropdown1.Selected.Value), txt_Search.Text, "Make", "Model" )
4. Dataverse IN Operator Delegation Workaround For Search Function
If we require full search capabilities the final workaround is to change the datasource from SharePoint to Dataverse For Teams (which also does not require premium licensing). The IN operator can be delegated in Dataverse and can check the contents of a column for a matching text string. This feature is still in Preview so we must enable it by going to File > Advanced Settings > Upcoming Features > Preview.
After we change the datasource to Dataverse For Teams and upload all of our Car Inventory data…
…we can write a FILTER function like this in the Items property of the gallery to perform a search.
Filter('Car Inventory', txt_Search.Text in Make Or txt_Search.Text in Model)
Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
If you have any questions or feedback about Power Apps Search Function Delegation Warning Workarounds please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.
Unbelievable !!! many thanks Math
Hi Matthew, great article. One question I am struggling to find the answer to – the 3 options you have above are not a replacement for the Search function. The StartsWith function only does half a job, the filter + search works fine with low numbers of records, while the new feature for Dataverse for Teams only works obviously with dataverse for teams. Does this mean that for a SharePoint list, if the number of rows is high, it’s simply not possible to perform a ‘Contains’ or ‘In’ query? Every other example I’ve found on other blogs keeps giving the same answer which is to use StartsWith, which is not viable as a search tactic in a modern app.
Unfortunately, if the number of rows in a SharePoint list is too high then it is not possible to use the SEARCH function or the IN operator to retrieve all results. What I’ve dome here is list the best workarounds that we have available. I don’t know why SEARCH has never been made delegation-friendly but I think many people would benefit if it was.
Hi Matthew, thanks so much for sharing this! I have a question. For (3), I moved my 2000-row SharePoint List to Dataverse for Teams using Dataflow. I can see the table there in Teams. I’m not sure if I missed what you were trying to do, but do you mean you can actually access this Dataverse for Teams table in a Canvas app (non-Teams)? If so, how can I do that? Thanks very much!
The Dataverse for Teams table can only be accessed from within Teams (free). Thanks for your question!
Thanks for this article.
I was wondering if the second option can be used in a scenario where the list is auto-filtered following the text entered by the End-User. (so without to use a button to apply the filter/search function)
Before to understand the delegation in Canvas App, I had the habbit to work with Collections to work in local. Like that, it was possible for me to filter the rows “in direct” following each caracteres entered by the End-User.
It was “good” with the use of the collections but I guess it’s not a best practice if we call the datasource directly. I mean, with this solution, each time the end-user add or remove a characteres in the input box (txt_Search), a call is done to Sharepoint to retrieve the list of rows which are elligible (following the “StartWith”)
If the datasource (= the list in Sharepoint) contains a large amount of rows, these calls in cascade could freeze or give an unintended behavior to the app I guess. What do you think ?
In any case, it changes the vision of the use of collections that I had. Thanks for the tip
I also believe it is best practice to use a button when searching. Live typing causes a performance hit to the app which is not a good experience. I decided not to take this approach in the article because I wanted to focus solely on the search delegation techniques. Sometimes blogging is as much about decided what to leave out as it is what to include.
Thank you for your thoughtful comment. I enjoyed reading it.
I am fascinated by the fact that this limit cannot easily be changed, but your suggestions are cool. Question: Can the StartsWith hack be used to populate a smaller Collection from a big dataset such as an Azure SQL database with many thousands of records? The result set would be 10 or fewer, so being able to find those 10 in the big data set would be great!
You are in luck sir! The Azure SQL database does not require any hack. The ‘in’ operator is delegable for Azure SQL so you can just use tip #3 in my article.
Relevant Link to MS Docs:
Hm… I like it, but I must be implementing it wonky. May I share my ClearCollect code here (in hopes you would better see where I went wrong and direct my brain), or would that be asking too much?
Yes, a screenshot or the code would be great!
Thank you — I hope I can convey this right…
Basically, I have a list of scanned codes. Each code is long, but in the middle of it is a specific value (an 11-character string). I pull that specific value and then use it to look up the one single record in the database that has that same value in “Number”. And then, I populate a new Collection using various values from that identified SQL record.
The ClearCollect command for MailingListExploded (the second one) is what I normally am using (and what occasionally draws a blank on account of my master data source growing in size).
The ClearCollect command for MailingListExploded1 is my (perhaps foolish) attempt to use IN, but it seems to experience the same behavior.
Spiderfood_RITMData is my table in the Azure SQL database. It has a column called “Number”
“Result” is a field in the collection ScanDataCollection.
Mid(Result, 12, 11) is a substring of “Result” that will be located somewhere in the database under “Number”.
Name1, Email, Name2, Email1 are also columns in the database. Once I find that record using “Number,” I need to pull those other items out of that record.
Additional point: I have confirmed that the records do exist in the SQL table.
Additional point: In both ClearCollect blocks, PowerApps double-blue underlines them and declares a delegation warning.
(I tried to format the text, but had a lot of trouble with setting it as a code fragment, so I hope the image helps…?)
First things first. Those MID functions need to be addressed. We can’t use them in a formula because they do not support delegation. Fortunately, they are all consistent.
Can you please update SQL so it stores the result of that MID function in a table field? Then you won’t have to perform a MID because it will already be stored in the DB.
The values of the MID function are what is in the NUMBER column of the SQL table. The value of RESULT comes from bar codes being scanned by a technician.
The collection Scan Data Collection might look something like this:
Technician scans this data from barcodes on the various items. That data is something like: REQ1325860.RITM1467138.01. In the database, one of the columns (Number) contains the RITM values and exactly one of those records exists whose Number value is “RITM1467138”. That’s how I find the relevant record in the database for each physical item. From that record, I then extract the Customer’s name, email, etc. for each item.
Does that help?
This is how the records look in the database:
Okay, you gave me a hint and I tried it. Image below.
In case MID was causing mischief, I pre-mixed ExpandedScanData to include a column exclusively for the RITM (which was what the MID statement was doing).
Then when I built my Collection by reaching into the database table, instead of recalculating the MID each time, I simply used the RITM column from ExpandedScanData.
At the very least, it no longer warns me about delegation errors, which is a small win at least.
I assume “=” is delegable into an Azure SQL table?
Making a column to separate out the RITM in your collection is exactly what the situation calls for here. Good job! I’m glad you were able to take my hint and get it work 🙂
Yes, the equals operator is delegable in every datasource (SP, SQL, DataVerse, Excel)
Hoping that this produced the right result. So far, I’ve used this same technique to eliminate a lot of potential delegation errors, so thank you!
What about nesting filters? For example Filter(Filter(IsCompleted = false), IsSoftDeleted= false)
This returns all records not completed and not soft deleted
Do you see problems with that method? So far, it seems to be giving me the results.
There is no issue with nesting filters.