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
Highlighted
Community Support
Community Support

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
Highlighted
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?

Highlighted
Community Support
Community Support

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

Highlighted
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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (4,625)