500 item limit in CDM entity search filter(need to switch to asp.net app if this exists for long)

[AmitLoh-Powerap]

500 item limit in CDM entity search filter, this makes it very dofficult to use for any business scenario(export, data analysis)

because I have 50k records and search filter may return sometimes 5k or 20k and I need to analyze this data(so export)

Currently its only exporting 500 first items which does not meet any business criteria(imagine you are doing google search and it returns only 3 items), sadly if this is permanant issue like sp list 5k limit I will have to inform this to our sponsors of the project and most likely as it does not meet business need to filter and export we will have to do asp.net app which we did not want to do.

I will atleast need some good workaround. One thing I observed is there is export data link in CDM screen(can you give me some workaround based on that?)

[poweractivate]

@liad-nizri 

1. Do you get the Yellow Triangle (delegation warning)?

2. Usually the limit is 500. Did you change the limit to 2000?

3. The limit does not apply unless you are using query that does not support delegation to the data source. That means PowerApps is doing the work itself instead of using the data source, so PowerApps has to impose a limit on those types of queries. Do you get the Yellow Triangle warning? Check by clicking the Stethoscope Icon (App Checker).

4. How to get rid of the warning - this depends on your specific case - first, do you get the warning?

[sarveshshinde15]

Hello Mr-Dang,

Thank you so much for this traditional paging logic. 

I had a scneario where I needed to use this with SharePoint list datasource. It doesnt work with it. Do you have any idea or alternative for SharePoint list?

I need to use one of the non-delegable operator In while fetching data from large sharepoint list. But it doesn't work as expected because of delegation. Could you please show some pointers?

[Mr-Dang-MSFT]

Hi @sarveshshinde15,

I start with an approach of using the right data source for the right job. If your data needs are very heavy, then your data should reside in SQL, CDS, or something on the same level. These connectors support more delegable queries like the in operator.

If the data needs of a problem you are solving are simpler, then you can work within the boundaries of the SharePoint connector, which can be browsed with StartsWith and EndsWith today.

[lseow]

Hi @Mr-Dang-MSFT ,

I appreciate your input.  Our dataset is around 4000 records or less and our source would have to be from Excel. Would your recommended approach using Flow work?

Thanks,

Lilian

[amitnaik]

@Mr-Dang-MSFT  This is the code, I have used, but the collection is frozen at 2000. I tried both with 1000 and 500 maxiters but it does not go above 2000 records. Please help. 

UpdateContext({FR: First('Road Map')});
UpdateContext({LR: First(Sort('Road Map',ID,Descending))});
UpdateContext({MaxIter: RoundUp((LR.ID-FR.ID)/1000,0)});

ClearCollect(HundredChart,1,2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100);
ClearCollect(iter,
AddColumns(AddColumns(Filter(HundredChart,Value<=MaxIter),"mins",(Value-1)*1000),"maxx",Value*1000)
);

Clear(RM);

ForAll(iter,
Collect(RM,
Filter('Road Map',ID>=FR.ID+mins && ID<FR.ID+maxx)
)
)

[bsoleti]

Hello Mr.Dang,

How do we use this code for the situation where I am using Sharepoint Library to pull into a Collection (powerapps) instead of CDM as the datasource . I added a Column (ID) in Sharepoint lib.

Any help on this is appreciated to resolve the 500 row limit i am having.

Thanks & Regards

[pranavnaidu24]

Partial fix for fetching SharePoint List items up to 4000.

Concurrent(

ClearCollect(CollDescending,Sort(DataSource,ID,Descending)),
ClearCollect(col1, Sort(DataSource,ID,Ascending))

);
ClearCollect(col2, Filter(CollDescending, ID >= Last(col1).ID+1 && ID <= First(Sort(DataSource,ID,Descending)).ID));
ClearCollect(MergedCollection, col1, col2);

Hope this helps

[bmassmann]

@pranavnaidu24  Any chance you can explain what is needed for that to work?  I have a SP List that is 2400 rows and I'm trying to use your code for that but I max out at 2000.  

Thanks!

[pranavnaidu24]

@bmassmann : Can you please force clearing merged collection.

Clear(MergedCollection);
ClearCollect(MergedCollection, col1, col2);

Please check:

1. Data Row Limit is set to 2000 in Advanced Settings of PowerApps.

2. Add Indexed Columns for your list/ library in settings

[bmassmann]

@pranavnaidu24 

I was able to clear the merged database (SharePointDatabase).

Data row limit is set to 2000.

What are the specifics of the index columns, assuming they have to be a number and just uniquely sequenced 1-2400.  Does the default ID column from SharePoint work or does a new one need to be created?  My Title column is 1,2,...2400, but currently text.

Thanks so much for your help!