Power Apps: Search A SharePoint List (No Delegation Warning)

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 AppSetup The SharePoint ListCreate A Search Bar And Gallery To Display ResultsSearch The SharePoint List Using Power AutomateReturn the Search Results From Power Automate To Power AppsConvert The Flow Output To A Collection Using ParseJSONShow Unfiltered Results When Search Bar Is EmptyBonus 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:

IDCarYearCarMakeCarModel
12009MazdaRX-5
21985HondaAccord
32001FordWindstar
41994MitsubishiEclipse
52006LamborghiniGallardo
62005SubaruLegacy
71997FordExplorer
30002011 Honda Ridgeline

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.







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.

Matthew Devaney

Subscribe
Notify of
guest

36 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Ramesh Mukka
Ramesh Mukka
7 months ago

What is the maximum number of items the Get Items can get me through pagination?

Ramesh Mukka
Ramesh Mukka
7 months ago

Thanks Matt

Gerald Dahl
Gerald Dahl
7 months ago

So useful and well presented, as usual!

Roelof Meijer
Roelof Meijer
7 months ago

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

André Moraes
André Moraes
7 months ago

Great post Matthew!
I’ll give it a try!

👍🏼

Jeremy Stokes
Jeremy Stokes
7 months ago

What is the performance like?

Geraldine
Geraldine
7 months ago

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

Paul
Paul
7 months ago

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?

PowerApp-PowerAutomate-SearchIssue.png
Paul
Paul
7 months ago

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.

PowerApp-PowerAutomate-SearchIssue-GetItems.png
Paul
Paul
7 months ago

Second image shows the Get items parameters I’ve entered. So we don’t quite get to the response steps just yet unfortunately.

PowerApp-PowerAutomate-SearchIssue-GetItems-PA.png
Paul
Paul
7 months ago

Here is my Filter Query code when selecting the Show more option

substringof(5262207,CustomerNumber) or substringof(5262207,ContactEmail) 
Paul
Paul
7 months ago
Reply to  Paul

Error message that was returned after test.

PowerApp-PowerAutomate-SearchIssue-ErrorMessage.png
Paul
Paul
7 months ago

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

Paul
Paul
7 months ago

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?

PowerApp-PowerAutomate-SearchIssue-ErrorMessage-02.png
Akshay Khanna
Akshay Khanna
4 months ago
Reply to  Paul

Hey Paul, even I get the exact same issue, any suggestions on how you solved it?
Thanks mate!

Ryan
Ryan
5 months ago

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.

Michael Rose
5 months ago

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…

Helen
Helen
4 months ago

Great post thanks. Does this work with multiline text SharePoint columns? Thanks

Kirk
Kirk
4 months ago

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
Kirk
4 months ago

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?

Mike
Mike
4 months ago

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

Marc
Marc
1 month ago

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

Tyler
Tyler
4 days ago

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