Power Apps: Search A SharePoint List (No Delegation Warning)
I have found a way to search a SharePoint list in Power Apps and return any records with a matching substring. The Power Apps Search function does not support delegation for the SharePoint connector. Instead, we can use the SharePoint – Get Items action in Power Automate, return the result to Power Apps as a text string and then ParseJSON to convert it from text to a table (collection). This method does not require a Power Apps premium license.
Table of Contents:
• Introduction: The Car Inventory App
• Setup The SharePoint List
• Create A Search Bar And Gallery To Display Results
• Search The SharePoint List Using Power Automate
• Return the Search Results From Power Automate To Power Apps
• Convert The Flow Output To A Collection Using ParseJSON
• Show Unfiltered Results When Search Bar Is Empty
• Bonus Content: YouTube Video Walkthrough
Introduction: The Car Inventory App
Salespeople at a car dealership use the Car Inventory App to search for vehicles for their customers. The salesperson types a word into the search bar and any vehicles with a full or partially matching name appear in the search results list.
Setup The SharePoint List
Create a new SharePoint list called Car Sales Inventory with the following columns:
- ID (auto-number)
- CarYear (number)
- CarMake (single-line text)
- CarModel (single-line text)
Include this data in the list:
ID | CarYear | CarMake | CarModel |
1 | 2009 | Mazda | RX-5 |
2 | 1985 | Honda | Accord |
3 | 2001 | Ford | Windstar |
4 | 1994 | Mitsubishi | Eclipse |
5 | 2006 | Lamborghini | Gallardo |
6 | 2005 | Subaru | Legacy |
7 | 1997 | Ford | Explorer |
… | … | … | … |
3000 | 2011 | Honda | Ridgeline |
Create A Search Bar And Gallery To Display Results
Open Power Apps Studio and create a new app from blank. Add a label and a rectangle shape and a label to the app to make a titlebar that says “Car Inventory”.
Insert a text input below the app title to be used as a search bar. Name it txt_Search. Then place a button with the word “Search” beside the text input and name it btn_Search.
Connect the Car Inventory SharePoint list to the app using the Data menu. After that, add a new vertical gallery below the titlebar.
Use this code in the Items property of the gallery to display the Car Inventory SharePoint list.
'Car Inventory'
Now we will update the gallery to show the details for each vehicle.
Write this code the Text property of the gallery’s label to show the year, make and model of each vehicle.
$"{ThisItem.CarYear} {ThisItem.CarMake} {ThisItem.CarModel}"
Search The SharePoint List Using Power Automate
The Power Apps Search function does not support delegation so we make a Power Automate flow to perform the search instead. Go to the Power Automate menu and create a new flow.
Start the flow with the Power Apps (V2) trigger. Create a required text parameter named searchTerm. The search term will store the search bar value passed in from Power Apps.
Add a SharePoint – Get Items action. Select the site address and choose Car Inventory as the List Name.
Use this code in the filter query field. It searches the CarMake and CarModel columns of the Car Inventory SharePoint list and returns any values with a substring matching the search term. For a complete set of instructions on how to create your own filters using SharePoint Rest API check out this excellent article.
substringof('triggerBody()['text']',CarMake) or substringof('triggerBody()['text']',CarModel)
The Top Count field is set to 100 in this example but we can increase it up to 5,000 records without any issues. However, only retrieve the amount of search results we want to display in our app.
Open the SharePoint – Get Items action settings and turn on Pagination. The threshold must be greater than the number of items in the SharePoint list. (example: if the SharePoint list has 10,000 items the threshold must be at least 10,000)
Return the Search Results From Power Automate To Power Apps
Use the Data Operations – Select action to extract the following columns from the Parse JSON action value. The JSON contains many more columns but we only need these 4 in Power Apps.
- Id
- CarYear
- CarMake
- CarModel
Store the Output of the Select action in a Data Operations – Compose action.
Finally, insert a Power Apps – Respond to a PowerApp or flow action at the end of the flow. Add a text output parameter named searchResults. Load the searchResults parameter with the Outputs from the Compose action.
Enable the ParseJSON Experimental Feature
The flow result is being passed back to Power Apps as a text string. But we need to convert it into a table data type so it can be stored in a collection. To do this, we must enable the experimental ParseJSON function. Go to the Advanced Settings menu and turn on ParseJSON in the Upcoming Features menu.
Convert The Flow Output To A Collection Using ParseJSON
The Power Automate flow we built to get search results from SharePoint appears in the Power Automate menu of Power Apps Studio.
When the user presses the search button it will trigger the flow and return any search results.
Use this code in the OnSelect property of the button. The ParseJSON function interprets the text string returned by Power Automate. Then the ForAll function creates a new record in the colCarInventory collection for each search result.
ClearCollect(
colCarInventory,
ForAll(
Table(ParseJSON(SearchCarInventorySPList.Run(txt_Search.Text).searchresults)),
{
ID: Value(Value.ID),
CarYear: Value(Value.CarYear),
CarMake: Text(Value.CarMake),
CarModel: Text(Value.CarModel)
}
)
)
Replace the Items property of the gallery with the collection name.
colCarInventory
Test the search feature we built by typing a value into the search bar and clicking Search. The search results will appear in the galler.y
Show Unfiltered Results When Search Bar Is Empty
The last step is to show an unfiltered list when the screen first loads or when search bar is empty.
Use this code in the App’s OnStart property to store the Car Inventory SharePoint list in a collection.
ClearCollect(
colCarInventory,
ShowColumns(
'Car Inventory',
"ID",
"CarYear",
"CarMake",
"CarModel"
)
)
Then update the search button to an unfiltered list when the search bar is blank.
Write this code into the OnSelect property of the Search button.
If(
IsBlank(txt_Search.Text),
ClearCollect(
colCarInventory,
ShowColumns(
'Car Inventory',
"ID",
"CarYear",
"CarMake",
"CarModel"
)
),
ClearCollect(
colCarInventory,
ForAll(
Table(ParseJSON(SearchCarInventorySPList.Run(txt_Search.Text).searchresults)),
{
ID: Value(Value.ID),
CarYear: Value(Value.CarYear),
CarMake: Text(Value.CarMake),
CarModel: Text(Value.CarModel)
}
)
)
)
Bonus Content: YouTube Video Walkthrough
This video shows how to Search A SharePoint list in Power Apps using the same steps outlined in the article above.
Did You Enjoy This Article?
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
Questions?
If you have any questions or feedback about Power Apps: Search A SharePoint List (No Delegation Warning) 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.
What is the maximum number of items the Get Items can get me through pagination?
Pagination can be set to a maximum of 100,000. This means the query will evaluate the 1st 100,000 records.
The Top Count can be set to a maximum of 5,000 records. I don’t suggest bringing that many records into Power Apps. But that’s the limit.
Thanks Matt
So useful and well presented, as usual!
Gerald,
Thanks for the compliment. I have wanted this feature for a long time and I’m glad we finally have it!
Gerald,
Thanks for saying that
Thank you for this idea. I have one question. Does it give a good response time? Is it instantanious or might it take ten seconds or more to run the flow and get the respons
Roelf,
The speed is acceptable. SharePoint will not match DataVerse or SQL in terms of performance.
Single column search was very fast. Searching multiple columns took longer.
I have only tried on 6,000 rows. You may want to try a higher number.
Great post Matthew!
I’ll give it a try!
Andre,
Wonderful. Hope it helps
What is the performance like?
Jeremy,
Pretty darned good
I mean Dataverse is faster. But still good!
Sweet! You are the bomb, Sir. I wish I had this a few weeks ago because I have two fields with those pesky delegation warnings and have had a devil of a time trying to figure out workarounds. Thank you as always for the great work you are doing on the Power Platform.
P.S. Where the heck do you get these ridiculously cute cat pics? Besides the value from your content, they always put a huge smile on my face. #catsAreTheBest
Matthew, you don’t know how happy I was stumble on this article. I was in deep research mode reading your informative Collections Cookbook articles and afterward found myself perusing your homepage. Lo and behold this article was a couple of posts in. Ecstatic, I had to try it out right away. I followed your excellent and clear instructions but in the App I want to see this function implemented. However I am getting an error in the OnSelect of the button (see attached image). Did you ever encounter this?
Paul,
Can you show what your flow looks like? I am especially interested in the response step. I’ve fixed this type of error before in other flows but I forget how I did it. Maybe you will jog my memory.
Hi Matthew, thanks for the reply.
The first image is the view when looking at the flow in Power Automate. Seems to falling at the Get items stage.
Second image shows the Get items parameters I’ve entered. So we don’t quite get to the response steps just yet unfortunately.
Here is my Filter Query code when selecting the Show more option
Error message that was returned after test.
Paul,
It looks like you are trying to use substringof on a Customer Number. Substringof only works on text. I believe that is the problem. You will need to convert the customer number to text if you want it to be searchable.
Hmm, I seem to get the same issue when tweaking the code to only look up email address instead of customer number. I will have to chalk this up to internal org issues as you seem to have got this working fine.
Thanks Matt.
Paul
I rewrote the code in the Filter Query and only used one search criteria instead of the two used in your examples. The ‘substingof’ error is now been replaced and now the flow doesn’t see that my ‘CustomerNumber’ column doesn’t exists
What on earth am I doing wrong here?
Hey Paul, even I get the exact same issue, any suggestions on how you solved it?
Thanks mate!
Ashkay,
Check your SharePoint column settings to make sure it’s the correct logical name. It’s possible the logical name is different from the display name.
Hi Matthew,
Great work as always. I was able to create my own test using your example above.
Quick question though, what about if the list item has an attachment? I assume it isn’t possible using the method above? I got all the columns retrieved in my search result except for the attachment.
Thank you so much for this really awesome article. Do you know how to handle a choice field with the ParseJSON?
ClearCollect(
colCarInventory,
ForAll(
Table(ParseJSON(SearchCarInventorySPList.Run(txt_Search.Text).searchresults)),
{
ID: Value(Value.ID),
CarYear: Choice(Value.CarYear),
CarMake: Text(Value.CarMake),
CarModel: Text(Value.CarModel)
}
)
)
Any help is greatly appreciated…
I was able to achieve this using the following code for the field in the ParseJSON:
CarYear:
{
Value: Text(Value.CarYear.Value)
}
This way the field has the same structure as the choice field returned from the table when there is no search term.
Brian,
Again, thanks for making the effort to help others on my blog. It is truly appreciated.
Great post thanks. Does this work with multiline text SharePoint columns? Thanks
Helen,
Unknown. I’ve not tested it on multiple line SharePoint columns.
The Filter Query in the Get Items action does not handle mutiline SharePoint text fields. You can instead use the Filter Array action step in your flow after the Get Items step to further reduce the results by the multiline field.
Brian,
That is an excellent workaround. Thank you for sharing it!
Thank you so much for this – I’m excited to get it deployed.
However, when the flow tries to run, the “Get Items” action fails with “Bad Request” as the only error.
Any insight as to why?
Kirk,
Nope, not sure. Bad Request is a very unhelpful error message in my opinion. Show your flow action screenshots if possible.
The problem I am running into is that the Query Filter is erroring out on my multi-line text field I’m trying to search. Do you know any way to get around this?
Kirk,
What error does the Query filter show? Screenshots are best.
Hi Matthew, This is great, is there a way of using this method for filtering a document library larger than 2000 items? Thanks for your advice
Mike,
I assume the same technique applies to document libraries except you would need to use a Get Files action instead of Get Items.
Hello,
great Job!
Your instructions are very helpful when you have to search in large lists.
What does it look like when you want to edit an entry from the search results?
For this, the original SharePoint list (Car Inventory) would have to refer to the selected data record.
It is not quite clear to me how this should be done without ending in delegation errors…
Regards Marc
To see a full search and filter set-up built off Matt’s Power Automate workaround demonstration, see this Community App Sample template: https://powerusers.microsoft.com/t5/Community-App-Samples/No-Delegation-Limit-SharePoint-List-Power-App/td-p/2330721
Hi Matt- excellent video and the Search function works great! I’m a little confused about one thing. The unfiltered list is still non delegable- correct? Is there any way to work around this? I tried a workaround with an If statement and variable that shows the collection once search button is clicked, but returns to the raw non-collected list by default. However this doesn’t work. Does this make sense? Any ideas? Thanks!
Paul,
I suppose the way to go here would be to put the IF statement directly inside the gallery Items. IF no search terms then ‘SharePoint List Name’ else Collection Name.
Hey Matt- correct me if I’m wrong, but it looks like switching between a SPList and a collection is non delegable in the Items property=for example If(1+1=2,SharePointList,Collection) is not delegable. I will just have to create another collection via Power Automate with everything and use that.
Paul,
I assume it’s non-delegable because you are adding 1+1 in the 1st argument. If you do something like varSomeText=”abc” there would not be any delegation issue.
It doesn’t seem to work for any expression that evaluates to true.
Hi Matthew
I am trying to create a collection using a flow that gets data from a sharepoint list with lookups to two other lists. The flow works as expected but when I try to use it I get an error message “Incompatible type. The collection can’t contain values of this type”. The ClearCollect from the sharepoint list works in isolation but when I add the ClearCollect from the flow I get the error. Here is the code for the search button:
Project and ProjectManager are the lookup fields. I am guessing that the data from the first ClearCollect is returning ID and Value for the lookups whereas the flow is returning only the values
Any ideas?
Hey,thanks a lot for the video. I have a question, can you make this work and search in multi-line columns?
In the “Get Items” action, when i try the “Substringof” with a multi-line text column, the flow pops an error.
Costa,
Substringof is not supported for multiple lines of text fields in the REST API.
Link:
https://sharepoint.stackexchange.com/questions/260646/mulitiline-of-text-column-not-used-to-filter-particular-word-from-list-using-res
Hi Matthew, thank you for another great blog, will this Flow work to display each “Status” value? Example, if you want to count each value in status, like pending = 4, hold = 2, done =3? I have created a dashboard, but of course its giving me delegation warning, any thoughts, much appreciate it!
Thanks for the very good example. Great job. I had some trouble with the substringof part. I need to type this: substringof( ‘@{triggerBody()?[‘text’]}’, CarMake ) instead of substringof(‘triggerBody()[‘text‘]’,CarMake)