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

[diegomarino]

Hi, i'm figure out now to get all my 6500 records. Can you share tour solution?

[Anonymous]

I get the same issue, have you fixed this?

[diego_marino]

i just made a collect on start, so i collect every 7k rows and i can filter my collection without problems

[Anonymous]

Hi @diego_marino  

Thank you for the quick reply. Can you provide more detail about how you "made a collect on start". Do you mean OnStart property of the App? It would be nice if you can share some sample code as reference. Thank you in advance

[diego_marino]

well, i made it a lot of time ago.

in every sharepoint list i made an RN field that is an autonumber using the max value function, so i have records from 1 to "my last record"

then i used concurrent so i can make every action simultaneously, that's faster, and (aside to set two variables, "data inizio" and "data fine" (start and end date), i collected in various collection (candi1, candi2 etc) every record in my list

then, out of the concurrent i made another collection that contains every small collection (candi1, candi2 etc) ,so i have my entire list.

firstRun is just to not make the operation every time, just in my first opening of the screen

Concurrent(UpdateContext({DataFine: Today()});UpdateContext({DataInizio: Today()}));;If(FirstRunCandidati = true;Concurrent(ClearCollect(Candi1;Filter(Candidati;RN<2001));ClearCollect(Candi2;Filter(Candidati;RN>2000;RN<4001));ClearCollect(Candi3;Filter(Candidati;RN>4000;RN<6001));ClearCollect(Candi4;Filter(Candidati;RN>6000;RN<8001)));;ClearCollect(CandidatiCL;Candi1;Candi2;Candi3;Candi4);;Set(FirstRunCandidati;false))

[Anonymous]

Thank you @diego_marino for the workaround 😀 This works well and it is flexible to append more collection if once the datasource exceed the 8000 rows (e.g. in your case). You made my day!

[GrischkePro]

You can get any number of items really. I was a follower of this thread from the early days. what I realised after a while is that you need to use filters and get the results you want in no time filtering the results at the data source level and instead of delegating it to the app. The filter needs to return less than 2000 records and it will be quick, otherwise, delegation kicks in and you need to wait. Even SharePoint can be quick. There's nothing wrong with SharePoint as a data source.

There will be scenarios where you might want more records returned even if it takes a few (or many more) seconds.

You can do something like this:

I would just get the item number from the last item, i.e., 22856, divide this by 2000, equals 11.428. Round up to 12.

Create three variables like varIDlow, varIDhigh and varIDinc.

Clear(col);
Set(varIDlow,1);Set(varIDhigh,2000);Set(varIDinc,2000);
Collect(col,Filter(Shifts,ID >= varIDlow && ID < varIDhigh));Set(varIDlow,varIDlow + varIDinc);Set(varIDhigh,varIDhigh + varIDinc);
Collect(col,Filter(Shifts,ID >= varIDlow && ID < varIDhigh));Set(varIDlow,varIDlow + varIDinc);Set(varIDhigh,varIDhigh + varIDinc);
Collect(col,Filter(Shifts,ID >= varIDlow && ID < varIDhigh));Set(varIDlow,varIDlow + varIDinc);Set(varIDhigh,varIDhigh + varIDinc);
Collect(col,Filter(Shifts,ID >= varIDlow && ID < varIDhigh));Set(varIDlow,varIDlow + varIDinc);Set(varIDhigh,varIDhigh + varIDinc);
Collect(col,Filter(Shifts,ID >= varIDlow && ID < varIDhigh));Set(varIDlow,varIDlow + varIDinc);Set(varIDhigh,varIDhigh + varIDinc);
Collect(col,Filter(Shifts,ID >= varIDlow && ID < varIDhigh));Set(varIDlow,varIDlow + varIDinc);Set(varIDhigh,varIDhigh + varIDinc);
//repeat the above line 12 times

You can go more extreme and use ForAll to retrieve any number of records. Create a collection with 12 records and get ForAll to run 12 times. You can also use an existing filtered collection to only retrieve the first 12 records from the data source so it can run 12 times. These are the workarounds since you can't increment a variable inside the ForAll function.

[diego_marino]

is "ID" a column in sharepoint? 

[GrischkePro]

Yes it is.

[diego_marino]

it is a good point, but i think i'll stay on my method...it is easier, i've already set RN field to calculate automatically

thanks for the insight, it is a very good point