cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexclavijoN
Frequent Visitor

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?

43 REPLIES 43

@rajivk ,

Can you please listen to @Drrickryp 's advice and read my blog section on this.

You will not do this with @gitamooney 's suggestion as ID is not Delegable for anything other than equals =.

@gitamooney , I suggest you also may benefit from reading this.

rajivk
Regular Visitor

Thanks a lot, @Drrickryp and @WarrenBelz ! It finally solved my issue. (I used the "below 4000 record" approach). For "above 4k record" approach, it still gives me the issue of delegation warning. Can we use calculated column for that approach to apply the filters? It's not working for calculated column for me.

@rajivk 

If you have a Number type column setup in your list that mirrors the ID column, you can use this method to get all your records.

 

Calculated columns should always be avoided in PowerApps, they are for SharePoint use only.

@rajivk ,

Please note @Eelman ‘s comments here there is only one way of collecting large lists and that is to have another unique numeric identifier other than ID. You cannot avoid this, so please follow the process if this is what you need to do.

KotiSaga
Frequent Visitor
User7
Frequent Visitor

I know this is an old thread but for everyone using SHAREPOINT LISTS that still have the Delegation warning (which in this case is caused by the ID comparison) you can use a simple workaround. Create a Number type column that is going to have the same value of the ID. Don´t use the calculated type, is not going to work with that, just update this column, after the item creation, with the ID value. And that´s it! I was able to load 13,5k in 5 seconds.

 

Delegation warning using ID but not when using Number ColumnDelegation warning using ID but not when using Number Column

@User7 ,

That is exactly what @Eelman posted and I have a blog all about it . . . .

There are some other workarounds in there that may assist you.

@User7 

The best thing about my method (if you ask me 🙂 ) is that you don't need to maintain 7 lines of code batches every time your list grows beyond the next 2000 limit. The use of the Sequence() functions takes care of growing list so that you only need to write the code once, then walk away ie no need to further adjust it.

 

You can even get creative with it by limiting the Max number of records return to a certain amount eg 10, 000 records. The method is very flexible.

KotiSaga
Frequent Visitor

Issue resolved finally.

powerapps, we observe by default 500 item limit has set with in the app level for any data source.

In order to overcome, we have option to set the limit of 2000 item limit maximum in the app.

Now using code, MaximoFacility is my data source name contains 3000 items.

ColFacilityDropdown is the collection storing the count of items from data source. We expect to see more than 2000 items.

 

Based on StartWith function filtering the data with respective to the charectors and numbers as mentioned below.

Code: Place the below code in a button on select property.

Add label with code CountRows(ColFacilityDropdown)

ClearCollect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "A" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "B" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "C" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "D" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "E" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "F" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "G" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "H" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "I" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "J" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "K" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "L" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "M" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "N" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "O" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "P" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Q" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "R" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "S" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "T" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "U" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "V" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "W" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "X" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Y" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Z" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "Z" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "0" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "1" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "2" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "3" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "4" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "5" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "6" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "7" )));

Collect(ColFacilityDropdown,Filter(Filter(MaximoFacility, Status = "ACTIVE"), StartsWith( Title, "8" )));

 

@KotiSaga ,

That is assuming you have a field that always has something in it. The performance overhead of doing 36 collections (when 3,000 records can be done in two is also something to consider seriously).

Also if you have less than 4,000 records, you can do this as per my blog.

If you plan your list from the start to have the numeric ID field, it all becomes quite easy and quite efficient resources wise.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,155)