How can i manage to filter more than 2000 records

[Anonymous]

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?

[mabl4367]

I have the same problem but the primary key in my case is a column with the datatype char(255). This means that the comparison operators =< and > cant be used.

Also I would like a more general solution that starts by checking the total number of records and then reads them in to a collection in chunks of lets say 500 records in each.

I tried to do that by filtering the original data source an get the next 500 records where the Id is not already in my collection but neither the "not"-function nor the "in" operator can be delegated to the sql data source so I'm out of luck.

I'm not allowed to create a view in the database, otherwise I could do that and add an index column using row_number to filter on.

Any tips?

[Hor]

I can't seem to get this work. I put the clear collect function at App start but the collection only stores up to ID 2000. FYI I have a table with columns ID, Name, Code and there is a total of 12k rows (ID is 1 to 12000). I tried to put the function Concurrent(ClearCollect(Store1,Filter(Table1,Value(ID)<2000)),ClearCollect(Store2,Filter(Table1,Value(ID)<4000 && Value(ID)>=2000...

Any idea what could be the issue? was it because of the ID column type?

[Anonymous]

@Hor 

If your ID column is a Sharepoint List ID column then you cannot use '>' or '<' with it, only '='. Which is pointless when trying to get a lot of records. 

If using SharePoint, you will need a numeric type column, or you can also use a text type column. Then these columns can accept the '>' or '<' in your ClearCollect. Note, you cannot use a Calculated column in case you were thinking of using that to convert your ID column, this doesn't work either.

Because you have 12k records there are 2 ways to populate a numeric column, ie:

1. Use Excel to make a list of numbers 1-12,000 and copy & paste these into a new SP column, or

2. Use Power Automate (MS Flow) to do it for you. This would be my preferred method.

If you are not using SharePoint then ignore the above but you will need to research the limitations of delegation that relate to your data source here.

[gitamooney]

I tried that.  ClearCollect(Test1,Filter(DateTimeSelectionListFinal,ID>4000));

This is in the App OnStart and I am using a SharePoint list.  I am not getting back any records for the Test1 collection.

Please help

[Drrickryp]

Hi @gitamooney 

Since you are using SharePoint, you can use Filter() and StartsWith() to avoid delegation.  If you have to search within the text of a column then you are out of luck and must use a way to get around the delegation issue. There are ways to get around it but there can be performance issues and limitations on the memory available in handheld devices and tables for large tables. 
If your list is already more than 5k records and is likely to grow further, you should consider using either SQL or CDS instead.  Unfortunately, these connectors have a cost while SharePoint is free.  However, gimping the search functions is the price you pay.  

[Ninad]

Hi, I tried that but I am getting error, "Invalid Argument Type Expecting a Number Value",

the ID after And condition changes to small Id and this is where it shows the above error. Can you please advise.

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

the highlighted ID changes to Id when i save. And gives error

[bulprosbibg]

Hello,

I have created the collections:

Concurrent(

ClearCollect(CollectionA,Filter(Gallery1.AllItems,BatchID<2000)),
ClearCollect(CollectionB,Filter(Gallery1.AllItems,BatchID >=2000 And BatchID<4000)),
ClearCollect(CollectionC,Filter(Gallery1.AllItems,BatchID >=4000 And BatchID<6000)),

ClearCollect(CollectionD,Filter(Gallery1.AllItems,BatchID >=6000 And BatchID<8000)),

ClearCollect(CollectionE,Filter(Gallery1.AllItems,BatchID >=8000 And BatchID<10000)),

ClearCollect(CollectionF,Filter(Gallery1.AllItems,BatchID >=10000 And BatchID<12000)),

ClearCollect(CollectionG,Filter(Gallery1.AllItems,BatchID >=12000 And BatchID<14000)),

ClearCollect(CollectionH,Filter(Gallery1.AllItems,BatchID >=14000 And BatchID<16000))

)

;ClearCollect(NameofCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH);
ForAll(NameofCollection,
FCST_Automation.Run(DataLevelCode,
AdjustmentLevelCode, LegalEntityCode, LegalEntityCurrency,
FiscalYear, Period, TransactionCurrency, VALUE,Dim_GLAccount, Dim_CostCenter,
Dim_LegalEntityPartner, Dim_TransactionType, PostingDescription))

But Im still able to extract only 1,000 records... I applied 2000 records in Advanced Options of the App, but still I need to extract more than 16k records, not 1k... Any ideas?

[gitamooney]

i created a batch id for my long list with batch ID as 1 for the first 1500, 2 for the next and so on.  Then i used this statement to pull them all in.

Concurrent(ClearCollect(Test1,Filter(FinalInputList_2,BatchID=1)),
ClearCollect(Test2,Filter(FinalInputList_2,BatchID=2)),
ClearCollect(Test3,Filter(FinalInputList_2,BatchID=3)),
ClearCollect(Test4,Filter(FinalInputList_2,BatchID=4)));
ClearCollect(Success,Test4,Test3,Test2,Test1);

Please let me know if you have any questions

[bulprosbibg]

Hi @gitamooney ,

From where you take this "FinalInputList_2", because Im using a Gallery1.AllItems from Power BI and Im thinking that the problem should be in my FILTER. I dont know why my collections are not working. Here is my code again:

Concurrent(
ClearCollect(CollectionA,Filter(Gallery1.AllItems,BatchID<2000)),
ClearCollect(CollectionB,Filter(Gallery1.AllItems,BatchID >=2000 And BatchID<4000)),
ClearCollect(CollectionC,Filter(Gallery1.AllItems,BatchID >=4000 And BatchID<6000)),
ClearCollect(CollectionD,Filter(Gallery1.AllItems,BatchID >=6000 And BatchID<8000)),
ClearCollect(CollectionE,Filter(Gallery1.AllItems,BatchID >=8000 And BatchID<10000)),
ClearCollect(CollectionF,Filter(Gallery1.AllItems,BatchID >=10000 And BatchID<12000)),
ClearCollect(CollectionG,Filter(Gallery1.AllItems,BatchID >=12000 And BatchID<14000)),
ClearCollect(CollectionH,Filter(Gallery1.AllItems,BatchID >=14000 And BatchID<16000))
)
;ClearCollect(NameofCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH)

Thanks. BR.

PS: Ive just created some labels with CountRows(CollectionA) and etc. The problem is that only 1,000 records are in the first collection. All the other collections are empty.

[gitamooney]

What is your source?  Mine is a SharePoint list called FinalInputList_2.  It would not work for me when I used the > or <.  That is why  I set the batch id for the first 1500 or so rows to be "1", the next 1500 all had the same batch Id "2" etc.  that way i could use BatchId=1 etc and that worked.