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

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-apps-search-a-sharepoint-list-no-delegation-warning/. Author: Matthew Devaney.

Power Apps: Search A SharePoint List (No Delegation Warning) Posted by - Matthew Devaney on - January 29, 2023 61 Comments

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' Code language: JavaScript ( javascript )

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}" Code language: JavaScript ( javascript )

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) Code language: JavaScript ( javascript )

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) } ) ) Code language: CSS ( css )

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" ) ) Code language: JavaScript ( javascript )

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) } ) ) ) Code language: JavaScript ( javascript )

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 Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up 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 Power Automate

Save Docusign Document To SharePoint With Power Automate