Overcoming the 500 record limit locally

[mr-dang]

UPDATE 2/19/17: I had written a new solution for this method here. It works more efficiently for CDS.

____________________

A few days ago I shared the solution I have been using to pull in all records of an Entity into a temporary collection to overcome the 500 record limitation. You can read about it here.

Now that I've had some time to play with ForAll, I have a more elegant solution for pulling in 500 records at a time for reading. I still do not have a good solution for writing though. For this to work, you will still need a column in your Entity which describes which set of 500 it belongs to.

Big idea: 

1. Find out what the maximum n value is that describes how many sets of 500 you have. The formula I included also repairs the datasource if the last entry did not correctly have an n recorded.
2. Create a dummy collection that includes whole numbers that are less than or equal to the n value you found in step 1.
3. Use the dummy collection in step 2 as an argument in ForAll--"For each n in the dummy table, collect the 500 records from the datasource which are equal to that n."

UpdateIf(datasource,IsBlank(n),
	{n: RoundDown(Value(PrimaryId)/500,0)+1
	}
);

UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))});

ClearCollect(iter,
	Distinct(Filter(HundredChart,Num<=maxn.n),Num)
);

Clear(datasource_temp);

ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,n=Result)
	)
)

This can be done in a Button, Toggle, Timer, or whatever you want to trigger.

The only requirement is that you create a Table of whole numbers in a column [1,2,3,4,5, etc.] from which to pull your dummy collection. You can connect it to PowerApps as static data. I just used an existing "Hundred Chart" from a datasource I already connected. I do not know another way of making a collection with such whole number sets.

EDIT: Unfortunately, you will need to create an n value in your entity. I tried the following formula to try working around writng a column for n, but it has service limitations:

ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,(RoundDown(Value(PrimaryId)/500,0)+1)=Result)
	)
)

EDIT2:

Since UpdateIf does not delegate, you will not be able to fix all n that are blank. Instead, when writing a record, write n as 0 instead of blank so you can fix it. The change below can only fix the last record and may miss any others that do not have an n.

UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))});

If(IsBlank(maxn.n) || maxn.n=0, 
Patch(datasource,First(Filter(PrimaryId=maxn.PrimaryId)),
	{n: RoundDown(Value(maxn.PrimaryId)/500,0)+1
	}
)
UpdateContext({maxn: First(Sort(datasource,PrimaryId,Descending))})
);

ClearCollect(iter,
	Distinct(Filter(HundredChart,Num<=maxn.n),Num)
);

Clear(datasource_temp);

ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,n=Result)
	)
)

@Meneghino, @hpkeong, @AmitLoh-Powerap: I think you would be interested in this.

[AmitLoh-Powerap]

I will try few things as well. I need export to csv with 5k to 10 records based on filters.(I have timer based solution working with 5k but its super slow) 

I am getting error at

Filter(datasource,n=Result)

as result is not in any variable. 

[AmitLoh-Powerap]

I have done below code and its kind of working butnot 100% sure

UpdateContext({maxn: First(Sort(PRCProducts,PrimaryId,Descending))});ClearCollect(iter,
Filter('Hundred Chart',Num<=20)
);Clear(datasource_temp);

ForAll(iter,
Collect(datasource_temp,
Search(PRCProducts,TextInput1.Text,"ProductGroup")  --- this is based on search filter on the textinput
)
)

this is is doing 20 additions with good speed but with duplicates as well, will try few things and will update you.

[tianaranjo]

@hpkeong

Greetings, I am trying to search a SP list of 33k items.  I have tried numerous options with no luck.  Will the workaround described above (for CDS) work with a SP list?

[panand99]

Hello @mr-dang ,

It`s a very nice post. 

But this doesn`t work at my end.

Question ) what is - HundredChart & Num in your example?

[panand99]

Hello @mr-dang ,

This solution just created repeated 500 records every time i clicked on that button. It does not display next 500 records.

Could you please help.

[Haggishk]

This thread from Mr Dang certainly put me on the right track for getting the records from my SP list but i was still getting errors thrown at me in powerapps.  there may be some tiny differences using the CDM to using SP lists and i finnaly got the following to work (everytime) for my large SP Lists.

UpdateContext({firstrecord: First(SPListName)});

UpdateContext({lastrecord: First(Sort(SPListName,ID,Descending))});

UpdateContext({maxiter: RoundUp((lastrecord.ID-firstrecord.ID)/500,0)});

ClearCollect(iter,
             AddColumns(AddColumns(Filter(IterationCount,ItNum<=maxiter),"StartID",(ItNum-1)*500),"EndID",ItNum*500)
);
Clear(datasource_temp);
ForAll(iter,
Collect(datasource_temp,
Filter(SPListName,ItemID>=StartID && ItemID<EndID)
)
)

the items in bold are my changes to the original script, however i have found you cannot use the ID of the SP list item, directly, for this kind of filtering.  The workaround for this is to use SP designer and create a 2010 workflow attached to the list to autoupdate a field in the list item with the value of the ID (my field is in Red).  making the workflow run whenever an item is created in the list makes sure you always have the ID available as a filter here's where i found the workflow workaround.

I stuck to the 500 import number as a safety, however as the limit has increased to 2000 in the latest powerapps this is possibly more efficient, but I am sticking with whats working for now.

Hope this helps someone scratching the head like i was.

[NigelP]

Hi

I am not using CDS, but SharePoint Lists.

However, I am getting the blue swiggly lines under the ID>= and the &&  :-

ForAll(iter,

              Collect(datasource_temp,

                             Filter(Impacts,ID>=firstrecord.ID+min && ID<firstrecord.ID+max)

              )

)

Is there a solution for this ?

Regards

Nigel

[Haggishk]

it seems you always get the lines when getting items from SP Lists, this seems to be default for anything that MS sees as a possible deligation issue, but using the workaround, this overcomes the deligation problem as the data is being collected in stages and not in one big lump.

[vbharath]

Instead of creating The Hundred Chart for iteration - when you have maxN value, you can use Sequence(), which creates an array of numbers from 1 to N, and iterate through that using ForAll().
Just my 2 cents to slightly build upon you're already awesome solution, @mr-dang!

[TechnicalPanda]

How To Get More Than 2000 Items Sum In PowerApps Using Power Automate And SharePoint

https://technicalpandas.blogspot.com/2023/09/how-to-get-more-than-2000-items-sum-in.html

**************************************************************************************************

Performing Easy CRUD Operation In PowerApps Using Modern Gallery Control On SharePoint List

https://technicalpandas.blogspot.com/2023/09/performing-easy-crud-operation-in.html

**************************************************************************************************

How To Create Editable Grid Using Gallery & Modern Controls In Power Apps

https://technicalpandas.blogspot.com/2023/09/how-to-create-editable-grid-using.html