Unanswered

Passing search string of searchable combobox to itself

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi All,

Running into the datalimit issue of 2000 records for a dropdown being populated from excel. So i was thinking if we could show only the list of values which matches the search of the combo box itself?

for e.g I still have access to 10,000 records of excel table, doesnt display to begin with but the results get displayed only based on string match of text i enter into combobox.

I have tried looking at delegation but

is this possible or how do we overcome the situation of 2000 limit when my data set is 10k.

Thank you

Categories:

Building Power Apps

All responses (12)

Answers (0)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous
Using Excel as your datasource, you can pull 15,000 records into your app with one call eg
ClearCollect(colMyRecords, myExcelFile)
Then use this collection as the data source of your combo box. If you have more than 15,000 records use two (or more in lots of 15K) calls, eg
// collect groups of 15000 records ClearCollect(colFirst15K, Filter(myExcelFile, ID <= 15000)); ClearCollect(colSecond15K, Filter(myExcelFile, ID > 15000 && ID <=30000)); // combine into one collection ClearCollect(colMyRecords, colFirst15K, colSecond15K)
Above, 'ID' can be some attribute in your data set.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @Anonymous ,

where would I put this code? I am using the combobox in a form which has a submit.

would it be onSelect of the combobox itself or Items?
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous
The usual way/s to do this is to put this code either in the App OnStart or the Screen OnVisible properties.

If App OnStart, add code to the 'App' property, then click the ellipse beside 'App' and click Run OnStart to get the data.

If using OnVisible, navigate away from then back to this screen and the code should run.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Got it. thanks for that, @Anonymous

how are you able to pull 15k records? I just tried it on appStart and it still shows 2000 limit for some reason as part of a single collection.

if I am able to achieve 15k, i just need one collection instead of multiple but some how it doesnt seem to work.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous
Are you using Excel as a datasource? I have an Excel file with 18K rows of data saved locally but I'm sure this could also be on OneDrive and I can collect 15K rows using this in the OnSelect of a Button
ClearCollect(colExcel, myExcelTableName)

myExceltableName is the Table name of the data in the Excel file.
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Firstly, please notice Excel is not a delegate data source.

So using formula like this will also have delegation problem.

ClearCollect(colFirst15K, Filter(myExcelFile, ID <= 15000));

That's the reason why you could only get 2000 records by using this formula.

Secondly, if you insist in using Excel as data source, there's only one solution:

save data in collection.

There's no delegation limit in collection.

So please use this formula in your app's OnStart:

ClearCollect(colMyRecords, myExcelFile) //replace with your excel table name

However, this has limit too. Collection's storage is not unlimited. I don't have 15k records to make a test. You could try this solution to see whether collection could save so many records. If could, it will be the best solution in your issue.

Then in your app, please use the collection as your other formulas' data source, instead of the Excel file.

Or else, you will meet delegation problem.

Thirdly, if one collection could not save15k records, the only way to solve your problem is to change data source.

PowerApps have three delegate data sources:

Common Data Service

SharePoint

SQL Server

By using delegate data sources, you could use delegate functions to avoid the effect of delegation.

I more suggest you use sharepoint as data source.

You could use "Export to sharepoint list" directly in your Excel file.

In sharepoint, "Filter",">+<" are all delegate.

Then you could save data in several collections.

ClearCollect(collection1, Filter(sharepoint listname, ID <= 10000)); ClearCollect(collection2, Filter(sharepoint listname, ID > 10000 && ID <=20000)); ....

Then use these collections as your data source in your app.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@v-yutliu-msft
Actually, I had no issues filtering my Excel file by ID. I have an ID column in my Excel Table with the formula

=Row(A2)-1, copied down around 18,000 rows (Note Row 1 is for headers)

I was able to create a collection of 4599 records using this formula in PowerApps
ClearCollect(colExcelSetSize, Filter(myExcelTable, ID>1000 && ID < 5600))
- this was carried out using the 'Data row limit for non-delegable queries' = 6

Also, I created a collection of 180,000 records in a couple of seconds using this PowerApps formula
// same collection of 15,000 records collected into one collection 12 times ClearCollect(colLargeCol, colExcel, colExcel, colExcel, colExcel,colExcel, colExcel, colExcel, colExcel,colExcel, colExcel, colExcel, colExcel)

Unfortunately, a SharePoint ID cannot be filtered using <, +, > ... only =. Here's the link, see Note 2. Here's what it says:
"2. The SharePoint ID field for a table is a number field in Power Apps. However, SharePoint only supports the equal ('=') operation for delegation on an ID field." I wish they were supported ... would save me heaps of dramas!!

I do agree though, Excel is a poor database. It's only good for static data as you need to remove the connector to refresh any data you have changed, but I guess it is good for large, unchanging datasets, like those set out above.
v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Anonymous , @Anonymous ,

Two things:

1)"Imported from excel" and connect with excel in One Drive is different.

If you choose "Imported from excel", you will only get static table, which means that you could only read data, you can not write data.

In this way, you import all the data in powerapps cache, which will make your app run slowly

Actually, we rarely use this. But yes, you will not have delegation problem in this situation.

If you connect with excel in One Drive, which we use more often, you will have delegate limit.

So please answer my question:

whether do you "Imported from excel" or connect with Excel in a cloud service ?

2)Sorry that I missed "<>" being not delegate in sharepoint list.

I suggest you use Common data service as data source instead.

On one hand, you could easily import excel to Common data service.

On the other hand, "<=>" are all delegate in this data source.
https://docs.microsoft.com/en-us/connectors/commondataservice/

Best regards,
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-yutliu-msft
Ah that explains it. I was confused by you mentioning delegation when I was getting none. It's because I wasn't using a OneDrive file, makes sense.

Thanks for pointing out the difference.
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you @Anonymous @v-yutliu-msft

was able to get it to work with ClearCollect with sharepoint list instead of excel itself.

i used the app onStart with pointing to SP list and splitting into 2000. But the SP list also had limit of 2000. so created multiple collections and combined them.