Avoid Delegation Warnings: Search SharePoint Lists from Power Apps with Power Automate

If you've built a Power Apps canvas app connected to SharePoint, you've probably run into the delegation wall. The built-in Search or Filter functions with string comparisons cannot delegate to SharePoint, forcing the app to retrieve all records and filter locally—breaking at 2,000 items by default. While many workarounds exist, one reliable method that avoids a premium license is to offload the search to Power Automate.

In this article, we'll build a bookstore inventory app that lets users search books by title or author. The search is performed server‑side using a Power Automate flow, which returns the results as a JSON string. Power Apps then converts that JSON into a collection using the ParseJSON function. No delegation warnings, no premium connectors.

Example Scenario

Contoso Books runs an online store with thousands of titles. The inventory team uses a Power Apps app to quickly find books by name or author. The data lives in a SharePoint list called Book Inventory.

The SharePoint List

Create a new SharePoint list named Book Inventory with the following columns:

• ID – auto‑number (default)
• Title – single line of text
• Author – single line of text
• ISBN – single line of text
• YearPublished – number

Add some sample records:

Building the Power Apps Screen

In Power Apps Studio, create a new canvas app from blank. Add:

• A text input named txtSearch – this is the search bar.
• A button named btnSearch with the label "Search".
• A vertical gallery named galleryBooks – this will display the results.

Connect the Book Inventory list via the Data menu (we'll use it to preload data when the search box is empty).

Creating the Power Automate Flow

We need a flow that accepts a search term from Power Apps, queries SharePoint, and returns matching records.

1. Go to Power Automate > Create > Instant flow > Power Apps (V2).

2. Add a text input parameter called searchTerm and mark it as required.

3. Add a SharePoint – Get Items action.

   • Site Address – choose your site.
   • List Name – Book Inventory.
   • Top Count – set to 100 (adjust based on expected result size).
   • Filter Query – use this formula to search both Title and Author columns:
   javascriptFilter Query

   Copy

   substringof('@{triggerBody()['searchTerm']}',Title) or substringof('@{triggerBody()['searchTerm']}',Author)

4. Open the Get Items action settings and turn on Pagination. Set the threshold to a number larger than the total items in your list (e.g., 10,000) so all matches are returned.

5. Add a Data Operations – Select action to pick only the columns we need:

   • From – choose the output of the Get Items action (the value array).
   • Map – define these fields:
     • ID → ID
     • Title → Title
     • Author → Author
     • YearPublished → YearPublished
     • ISBN → ISBN

6. Add a Data Operations – Compose action and set its input to the Output of the Select action.

7. Finally, add a Power Apps – Respond to a PowerApp or flow action.

   • Add a text output parameter named searchResults.
   • Set its value to the output of the Compose action.

Save the flow. It will appear in Power Apps Studio under the Power Automate menu when you return to your app.

Enable ParseJSON

ParseJSON is still in preview (as of mid‑2026). Go to Settings > Upcoming features and turn on ParseJSON under the Experimental section.

Wiring the Search Button

In Power Apps, name the flow something like SearchBooks. Then write the following formula in the OnSelect property of btnSearch:

ClearCollect(colBookInventory,
  ForAll(
      Table(ParseJSON(SearchBooks.Run(txtSearch.Text).searchResults)),
      {
          ID: Value(Value.ID),
          Title: Text(Value.Title),
          Author: Text(Value.Author),
          YearPublished: Value(Value.YearPublished),
          ISBN: Text(Value.ISBN)
      }
  )
)

Set the Items property of galleryBooks to colBookInventory.

Showing Unfiltered Results When the Search Box Is Empty

When the screen loads or the search term is blank, we want to display all books (or at least the first 2,000 due to delegation limits, but we can live with that for an unfiltered view). Use the app’s OnStart to preload a collection:

ClearCollect(colBookInventory,
  ShowColumns(
      'Book Inventory',
      "ID",
      "Title",
      "Author",
      "YearPublished",
      "ISBN"
  )
)

Modify the search button’s OnSelect to call the flow only when a term is entered:

If(
  IsBlank(txtSearch.Text),
  ClearCollect(colBookInventory,
      ShowColumns(
          'Book Inventory',
          "ID",
          "Title",
          "Author",
          "YearPublished",
          "ISBN"
      )
  ),
  ClearCollect(colBookInventory,
      ForAll(
          Table(ParseJSON(SearchBooks.Run(txtSearch.Text).searchResults)),
          {
              ID: Value(Value.ID),
              Title: Text(Value.Title),
              Author: Text(Value.Author),
              YearPublished: Value(Value.YearPublished),
              ISBN: Text(Value.ISBN)
          }
      )
  )
)

Delegation and Performance Considerations

• The substringof filter in the Get Items action is executed server‑side, so it delegates completely. No delegation warnings will appear in Power Apps.
• The Top Count and pagination settings control how many records the flow retrieves. You can raise Top Count up to 5,000 without pagination; with pagination you can retrieve more, but be mindful of the flow’s response size (the JSON string sent back to Power Apps).
• Because the flow introduces a round‑trip, expect a short delay (usually 1–3 seconds). Avoid calling the flow on every keystroke; use a button or a debounce pattern.
• This method works with any SharePoint list and does not require a premium Power Apps license – only a standard Power Automate license (included with many Office 365 subscriptions).

Common Mistakes

• ParseJSON not enabled – Without the feature, ParseJSON will not be recognized and the formula will fail.
• Pagination off – If you don’t turn on pagination and the list grows beyond the Top Count, you may miss matching records.
• Filter query syntax wrong – The dynamic value @{triggerBody()['searchTerm']} must be inside single quotes. Check the expression in Power Automate.
• Column name mismatches – The column names in the ParseJSON formula must exactly match those returned by SharePoint (case‑sensitive). Use Text() for text columns and Value() for numbers.
• Blank search term – If the user clicks Search with an empty box, the flow will receive an empty string. The If pattern above avoids calling the flow unnecessarily.

Troubleshooting

• Flow returns an error – Open the flow run history. The most common cause is a malformed filter query. Ensure the column names exist and the quotes are correct.
• ParseJSON returns an empty table – The JSON returned by the flow might be empty because no records matched, or the column names in the formula don’t match the JSON keys. Use the Monitor tool to inspect the actual JSON string from the flow.
• Gallery shows no data – Check that colBookInventory is populated by using View > Collections after running the flow.

Final Recommendation

This pattern is a solid, delegation‑safe workaround when you need substring search across SharePoint text columns in Power Apps. It requires only a standard Power Automate flow and the experimental ParseJSON function. However, for very frequent searches or extremely large result sets, consider alternatives like:

• Using the SharePoint Search API via an HTTP action (requires premium connector).
• Storing data in Microsoft Dataverse which offers full delegation for many filter operations.
• If you only need a prefix search (begins with), the built‑in Filter function with `"column" &" " in the search. But substring matching still demands the flow approach.

For most moderate‑sized SharePoint lists and occasional searches, the method described here is a reliable choice that avoids both delegation warnings and premium licensing.

References

• Original article by Matthew Devaney: Power Apps: Search A SharePoint List (No Delegation Warning)
• Microsoft Learn – ParseJSON function
• Microsoft Learn – Delegation overview for Power Apps
• SharePoint – Get Items action reference