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:

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.

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.