web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Search on SQL data exc...
Power Apps
Unanswered

Search on SQL data exceeding 2000 records

(0) ShareShare
ReportReport
Posted on by Gundi76 87

Hi,

I've managed to get around the issue with 2000 record limit by creating a collection of all 13600 records from SQL.

However, this is making the user wait for approx 3 minutes (sometimes page becomes unresponsive/locks). This collection is happening on the start-up of the app.

 

Does anyone know if it's possible to serach on views of max 2000 records directly in SQL without collecting all the data in the app?

 

For example,
If I have 10 views of say 1500 records. That it will look in "View 1" first and then in "View 2" etc. based on where the search-item is located.

 

Any help would be much appreciated

Categories:
Building Power Apps
  • cha_cha Profile Picture
    cha_cha 4,932 Moderator on at

    Hello @Gundi76 

     

    What kind of data does your SQL table have and what columns are the users searching?

     

    This is untested but wanted to share how I would do it if I was in your case and the search is only for certain columns.

     

    Utilise SQL View to combine every 30 or 50 rows of data in 1 record. The string would look like this in one row of data with the ID. 1##John Smith$$$2##Jane Smith$$$3##Jean Smith

    Each record is separated by '$$$' and each column for each record is separated by '##'

     

    During the search, it will return all possible rows with the keyword and would then use the Split function in PowerApps to splits the selected text into rows and filter only those that have the search keyword. You can use then another split to detach the name and ID then use the ID to return the values.

     

    I hope you get the idea.

     

  • Danj_b Profile Picture
    Danj_b 76 on at

    Is it necessary to add every record into the collection, or could you consider limiting the size of the collection? 

     

    Also by creating the collection at AppStart will likely cause performance issues, plus the records will not be up-do-date if the user(s) make changes whilst using the app - could you consider moving to another screen and use delegated formulas to limit the number or records?

     

  • timl Profile Picture
    timl 37,287 Super User 2026 Season 2 on at

    @Gundi76 

    The most performant way is to not use collections, but to search directly against the SQL data source using delegable operators.

    What formula are you using, or what type of search are you trying to perform that requires you to use a collection?

  • Gundi76 Profile Picture
    Gundi76 87 on at

    @timl ,

    The reason why I (thought) need to use collection is that the table in SQL consists of 13600 records and the users needs to be able to search on both ID and NAME. 

     

    The formula I'm using for search (after creating collection colAllList) is this:  

     

    ShowColumns(
    Filter(
    AddColumns(
    colAllList,
    "ProspectText",
    Text(PROSPECT_ID)
    ),
    ComboBox8.SearchText in PROSPECT_NAME ||
    ComboBox8.SearchText in ProspectText
    ),
    "PROSPECT_NAME",
    "ProspectText"
    )

  • Gundi76 Profile Picture
    Gundi76 87 on at

    @Danj_b ,

     

    The collected data will not be updated in the app. The data in Collection is data to be added to different tables. 

    All data need to be able searchable, unfortunately.

  • timl Profile Picture
    timl 37,287 Super User 2026 Season 2 on at

    Hi @Gundi76 

    The answer to this is to create a SQL Server View that converts PROSPECT_ID to a text value.

    CREATE VIEW vwYourViewName 
AS
SELECT ProspectText, 
 CAST(PROSPECT_ID AS nvarchar(max)) AS PROSPECT_ID
FROM tblYourTable;

    From Power Apps, you can then search against this view.

    Search(vwYourViewName, 
 ComboBox8.SearchText,
 "PROSPECT_ID",
 "ProspectText"
)

    This solution avoids the use of collections and will be completely delegable so that works against your 13k+ records.

  • Gundi76 Profile Picture
    Gundi76 87 on at

    Thanks @timl ,

     

    I will check and see if this will end my pain 🙂

    Would it also be possible to search by NAME as well? 

  • timl Profile Picture
    timl 37,287 Super User 2026 Season 2 on at

    Hi @Gundi76 

    Yes, if NAME is a column in your underlying table, you can add it the view and the formula in Power Apps would then look like this:

    Search(vwYourViewName, 
 ComboBox8.SearchText,
 "PROSPECT_ID",
 "ProspectText",
 "NAME"
)
  • cha_cha Profile Picture
    cha_cha 4,932 Moderator on at

    Hi @timl

     

    I just wanted to thank you! I have always thought that Search is non-delegable, And it wasn't for SQL String columns.  Thank you! 

  • timl Profile Picture
    timl 37,287 Super User 2026 Season 2 on at

    You're welcome @cha_cha! I think part of issue is that Search is non-delegable for SharePoint, so there's an assumption that it's also non-delegable for SQL too.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 409 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 328

#3
WarrenBelz Profile Picture

WarrenBelz 252 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans