cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Search Multiple fields on a local list

Here's the situation:
For technical reasons I'm forced to load a very heavy Sharepoint list into a local Collection and display the said local collection. The funny thing is when I set the Item property of the Browse Gallery to MyList it works just fine but I can't search it. And when I use the command line below:

SortByColumns(Search(MyList;SearchInput.Text;"Column1";"Column2";"Column3");"Column1")

(disregarding the sorting order) then if the list was previously loaded it works fine, but while browsing the pp if I ever have to reload it (and I'm going to have to reload it) then I simply get nothing at all, full blank. Any idea why it's acting this way ? I'm pretty sure the formula is correct since it works for any other Sharepoint List, it's just on this local list that it doesn't work.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Search Multiple fields on a local list

Hi @Anonymous ,

Could you please share a bit more about the "MyList" that you mentioned? Is it the Local collection that you mentioned?

How many records stored in your Original SP List? More than 2000 records?

 

If the "MyList" that you mentioned is actually the local collection, I think your issue is related to it. Currently, within PowerApps, when saving data from other data source into a collection in an app, the collection could only contain 2000 reocrds at most once time, the rest of your SP List records would be missed.

 

If the records you are searching are in Row Index which are more than 2000 in your SP list, it could not find them in your app.

 

As an alternative solution, you could consider bulk-load your SP List records into multiple separated collections in your app, then merge the multiple collections into single one collection, tthen use the merged collection as data source in your app.

 

Please check and see if the alternative solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M71518

 

On your side, please set the OnStart property of the App control to following (I assume that there are 10000 records stored in your SP List😞

Concurrent(
    ClearCollect(col1, Filter('YourSPList', ID >= 1 && ID <= 2000)),
    ClearCollect(col2, Filter('YourSPList', ID >= 2001 && ID <= 4000)),
    ClearCollect(col3, Filter('YourSPList', ID >= 4001 && ID <= 6000)),
    ClearCollect(col4, Filter('YourSPList', ID >= 6001 && ID <= 8000)),
    ClearCollect(col5, Filter('YourSPList', ID >= 8001 && ID <= 10000)
); ClearCollect(MergedCollection, col1, col2, col3, col4, col5)

then modify your Filter formula as below:

SortByColumns(Search(MergedCollection; SearchInput.Text;"Column1";"Column2";"Column3"); "Column1")

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Dual Super User
Dual Super User

Re: Search Multiple fields on a local list

Hi @Anonymous 

 

Firstly, if you are loading a large list into a collection in PowerApps please note that the collection itself will only store data upto 2k records (depending upon the non-delegable limit set in your App - default 500)

 

Second, when are you loading data into your collection?

Community Support Team
Community Support Team

Re: Search Multiple fields on a local list

Hi @Anonymous ,

Could you please share a bit more about the "MyList" that you mentioned? Is it the Local collection that you mentioned?

How many records stored in your Original SP List? More than 2000 records?

 

If the "MyList" that you mentioned is actually the local collection, I think your issue is related to it. Currently, within PowerApps, when saving data from other data source into a collection in an app, the collection could only contain 2000 reocrds at most once time, the rest of your SP List records would be missed.

 

If the records you are searching are in Row Index which are more than 2000 in your SP list, it could not find them in your app.

 

As an alternative solution, you could consider bulk-load your SP List records into multiple separated collections in your app, then merge the multiple collections into single one collection, tthen use the merged collection as data source in your app.

 

Please check and see if the alternative solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M71518

 

On your side, please set the OnStart property of the App control to following (I assume that there are 10000 records stored in your SP List😞

Concurrent(
    ClearCollect(col1, Filter('YourSPList', ID >= 1 && ID <= 2000)),
    ClearCollect(col2, Filter('YourSPList', ID >= 2001 && ID <= 4000)),
    ClearCollect(col3, Filter('YourSPList', ID >= 4001 && ID <= 6000)),
    ClearCollect(col4, Filter('YourSPList', ID >= 6001 && ID <= 8000)),
    ClearCollect(col5, Filter('YourSPList', ID >= 8001 && ID <= 10000)
); ClearCollect(MergedCollection, col1, col2, col3, col4, col5)

then modify your Filter formula as below:

SortByColumns(Search(MergedCollection; SearchInput.Text;"Column1";"Column2";"Column3"); "Column1")

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Search Multiple fields on a local list

Thanks for the given solution, I'll try merging several collections since my list is quite heavy

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,819)