cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexclavijoN
Level: Powered On

How can i manage to filter more than 2000 records

I'm working from an excel file that has 15000+ records. When i try to filter some items it won't show it all. I did some research and found that the max records that powerapps will allow is 2000. But i don't think this is the issue cause it is filtering data beyond the first 2000 records. 

Can someone help me please?

1 ACCEPTED SOLUTION

Accepted Solutions
kscharber
Level: Powered On

Re: How can i manage to filter more than 2000 records

You could run a multiple collections to bring in the data in chunks of 2000, then group it into a larger collection.

Powerapps just doesn't like dealing with all that data from an outside source, but temp storing within the browser  works alright.

 

Step 1.)

 

***
Concurrent(

ClearCollect(CollectionA,Filter(NameofTable,ID<2000)),
ClearCollect(CollectionB,Filter(NameofTable,ID >=2000 And ID<4000)),
ClearCollect(CollectionC,Filter(NameofTable,ID >=4000 And ID<6000)),

ClearCollect(CollectionD,Filter(NameofTable,ID >=6000 And ID<8000)),

ClearCollect(CollectionE,Filter(NameofTable,ID >=8000 And ID<10000)),

ClearCollect(CollectionF,Filter(NameofTable,ID >=10000 And ID<12000)),

ClearCollect(CollectionG,Filter(NameofTable,ID >=12000 And ID<14000)),

ClearCollect(CollectionH,Filter(NameofTable,ID >=14000 And ID<16000))

)

 

Step 2.)

Then Collect your Collections together=)

;ClearCollect(NameofCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH)

 

You should now be able to Filter your NameofCollection with all 15000 records.  It just takes a bit of preloading first to get there.

 

***Edited to add a Concurrent function to make this load much faster***

9 REPLIES 9
kscharber
Level: Powered On

Re: How can i manage to filter more than 2000 records

You could run a multiple collections to bring in the data in chunks of 2000, then group it into a larger collection.

Powerapps just doesn't like dealing with all that data from an outside source, but temp storing within the browser  works alright.

 

Step 1.)

 

***
Concurrent(

ClearCollect(CollectionA,Filter(NameofTable,ID<2000)),
ClearCollect(CollectionB,Filter(NameofTable,ID >=2000 And ID<4000)),
ClearCollect(CollectionC,Filter(NameofTable,ID >=4000 And ID<6000)),

ClearCollect(CollectionD,Filter(NameofTable,ID >=6000 And ID<8000)),

ClearCollect(CollectionE,Filter(NameofTable,ID >=8000 And ID<10000)),

ClearCollect(CollectionF,Filter(NameofTable,ID >=10000 And ID<12000)),

ClearCollect(CollectionG,Filter(NameofTable,ID >=12000 And ID<14000)),

ClearCollect(CollectionH,Filter(NameofTable,ID >=14000 And ID<16000))

)

 

Step 2.)

Then Collect your Collections together=)

;ClearCollect(NameofCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH)

 

You should now be able to Filter your NameofCollection with all 15000 records.  It just takes a bit of preloading first to get there.

 

***Edited to add a Concurrent function to make this load much faster***

Eelman
Level: Powered On

Re: How can i manage to filter more than 2000 records

@kscharber  

Does this method work for you? I tried it using a SharePoint list but powerapps still only collects 2000 item, total.

 

Stats :

- my SP list has 4500+ items

- I collect into 3 collections using your method

- items collected per collection

   - collection1 = 1989

   - collection2 = 11

   - collection3 = 0

   - Total = 2000

 

To check if powerapps has a collection limit of 2000 total, I pointed collection2 at a different SP list and got these numbers:

 

- collection1 = 2000

- collection2 = 2000

 

Seems powerapps will only take the first 2000 items in a SP list THEN Filter/Sort/etc. The functions appear to need a slight tweak to make them work as intended.

 

Cheers

ZePowerDiver
Level 8

Re: How can i manage to filter more than 2000 records

Not sure, but maybe the filter is not able to delegate.. do you get the blue underline in your Filter() function?

kscharber
Level: Powered On

Re: How can i manage to filter more than 2000 records

I also wonder if delegation is affecting your filter. 

I too pull from SP for all of my tables.  For me I use Indexed columns, and almost always number values to apply this method. 

 

It took me a while to find ways to work around delegation at first, but now that I'm further along in my program, I never let that blue underline ever affect anything anymore =P  I learned the hard way how messy my data can get.

I have 3300 records in a collection that uses this method, so I'm unsure where else the issue could be. 

Maybe someone else will have more ideas.

Eelman
Level: Powered On

Re: How can i manage to filter more than 2000 records

@ZePowerDiver  yes I do get the blue line but I thought it was OK to ignore because my data source is Sharepoint as per this https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...

 

Maybe I'm wrong? Further digging into the docs I found Choice columns and '=' operator are delegatable to SP but I get the blue line when attempting this operation

 

Eg. ClearCollect( colMyCol,  Filter(  mySPlist  ,  choiceCol.Value = dropdown1.Selected.Value  ) ) 

- the blue line for me appears under 'Value' part of choiceCol.Value as well as under the '=' operator. I've just been ignoring it because it's SP.

 

It apears from my testing that PAs creates a 2000 item instance before applying my filters, like the one above. 

 

ie The above code only returns those items that meet the criteria from the first 2000 items (by ID ascending order). 

 

My current idea is Sort Ascending/Descending and grab the top and bottom 2000 items THEN use Min/Max ID numbers from these collections to build an array of 'missing IDs' which I use ForAll & Collect and loop through the missing IDs to collect those. A very crazy work-around ...

 

Any other ideas would be handy though.

 

Cheers

 

 

ZePowerDiver
Level 8

Re: How can i manage to filter more than 2000 records

Shouldn't be applying your filter on the ID column as a first step?

In your example you're filtering on the ChoiceCol which is the step you should be doing on the sum of all collections, no?

Eelman
Level: Powered On

Re: How can i manage to filter more than 2000 records

The end goal is to filter on the choicecolumn, as you say, the problem I'm having is collecting the entire 4500+ items to be able to do that. Powerapps just can't do it - well not for me anyway

mhines
Level: Powered On

Re: How can i manage to filter more than 2000 records

I'm a novice to PowerApps and this is one of the biggest stumbling blocks to one of the big "Quick Wins" I could get with this. Monthly one department exports a list from another database and makes it an Excel Document with about 20000 lines.

This solution appears to be what I need so that we could make it easy for someone to enter in a five-letter Symbol and to then display the six related columns.

I have tried this but I believe that I have the code in "Step 1 and Step 2" in the wrong place. I tried it on the "App Object" in the OnStart Action. That didn't work. 

I have tried it on the BrowseScreen1 in the "OnVisible", but that hasn't worked.

Maybe it's pretty clear where to insert this code - but I am  hoping someone has the quick answer so I can get a quick win on this solution.

Eelman
Level: Powered On

Re: How can i manage to filter more than 2000 records

@mhines 

Try using the code on some buttons instead. Just insert a random button somewhere in your App then add and run the code in it's OnSelect.

 

 

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 429 members 4,855 guests
Please welcome our newest community members: