Unanswered

ComboBox that show over 2000 records.

(1) Share

Report

Posted on by jd38

Hi all.

I have establishments to implement in a combobox in order to select 1 establisment by his with search option and when i select one establishment, it must show me his adresse and other informations in text fields.

The problem is the Establishment data source (Onedrive) contains more that 300 000 records and Power Apps show me only 2000 first records (limit for perf calls)

I saw in a previous post than we can create collections with pagination process (0 to 2000, 2001 to 4000, ....) and merge collections into a final collection.

The problem is that i have more than 300 000 records and as you understand i will not create 150 collections it will be just an horror !

So i'm looking for a way to get my 300 000 records and show it in a combobox.

Do you know how can i do that ?

Many thanks for your help.

regards,

Jean-Michel

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

Hi jd38

Firstly collections are client-side (local device) operations and I find much more than double (4,000) starts slowing down. I have a blog on big collections that requires a unique numerical identifier, however even if the device even had the resources for 300k records (I highly doubt it), the time for collection would be huge and the performance glacial. I also have a blog for dealing Combo Boxes and large lists, however if you are using Excel here, I am not at all confident it will handle the query.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
jd38 20 on at

Like (1)

Report

Hi WarrenBelz

Thanks for your reply.

I'm using an excel file in onedrive.

The problem is that the connector is retrieving only 2000 records. If i do a simple CountRows(Table1), it shows me 2000 records only.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

Hi jd38

Not much I can add to my previous post - Excel is a terrible data source and one of "last resort" at the best of times, but you are not going to get 300k records into a combo box selection using it. SharePoint may handle the process in the blog I posted earlier, but I am not confident even with that you will do it. Dataverse should have the capability for the Search if you want to pay the licensing fees.

Was this reply helpful? Yes No
Suggested answer

CamiloLuque 157 on at

Like (1)

Report

Hi @id38, I mimic your situation and I founded that is no necessary use collections, simply set the combobox Items property with the datasource. The search functionality of the combo filter the establishments and it don't worry about the 300.000 records. See below:

My Excel has 300.000 rows and the table is Table1:

In the app I used the Excel Online Business connector for Table1:

In the combobox, in its Items property I set Table1:

When I type to search in the combobox, it founds the records that I need:

Only show five records because for this test I set the data row limit to five:

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (1)

Report

CamiloLuque

Yes of course it will work in the native Excel interface - this is a Power Apps Combo Box question.

Was this reply helpful? Yes No
Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (1)

Report

Creating a reference table can efficiently manage large datasets in Power Apps. Instead of loading all 300,000 records, create a smaller table with key details like the establishment's name and ID. Use this reference table for quick searches and selection in the combobox. When a user selects an establishment, fetch the full details (like address) from the main data source using the selected ID. This approach ensures your app remains fast and responsive by only pulling in detailed data when needed, without overwhelming the system.

Was this reply helpful? Yes No
jd38 20 on at

Like (1)

Report

Thanks guys for the responses.

The idea was just to test and adapt the content of that combobox with the best performances for end users.

For today, i've made a test with Excel and SharePoint and use a filter with StartWith.

Tomorrow the idea is to use a connector "MySql" or "Amazon Redshift" because the 300k rows are stored in an Amazon Redshift database.

There's is 2 points to test on my side :

1. The way to communicate with AWS redshift from Power Apps

2. The performances to fill the datas loaded via the connector.

Both connectors are premium and we need a gateway. So lets see the next steps :)

Thanks guys for your support.

Was this reply helpful? Yes No