cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dual Super User
Dual Super User

Re: How to pull more than 3000 SP list items to PowerApps

Hi @sasidhar_mudra 

 

Here is one of my blogpost, where I collected more than 2000 records in the collection, by creating a calculated string column in SharePoint. This gets auto calculated based on the ID, but since this was an integer field, I created one for storing string value.

With the help of StartsWith function this was collected easily. Can you give this a try:

https://www.appliedis.com/how-to-use-galleries-in-powerapps/ 

You can follow the same steps, I did for the OrderCollection.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Re: How to pull more than 3000 SP list items to PowerApps

You can collect more than 2000 records by collecting them in batches for 2000 and then putting the batches together into one large batch that contains all of your records. This can be done by adding another column to your database that is called BatchID. The first 2000 records will have a 1 in this column, the next 2000 a 2 and so on. You will then collect 2000 at a time by filtering by the BatchID column.

You can loop through however many batches are required by using timers and variables. If you require more explanation please message me directly and I will do my best to help. 

 

sasidhar_mudra
Level: Powered On

Re: How to pull more than 3000 SP list items to PowerApps

@ datamaster, Yes, i did pull the first 2000 items using Collect. What about the rest? Thanks Sasidhar
Highlighted
sasidhar_mudra
Level: Powered On

Re: How to pull more than 3000 SP list items to PowerApps

@MatthewInwards ,

 

Hello Mathew,

 

Lets say i have batch number for first 2000 records as B1, and next 2000 records as B2.

Since using Collect will collect first 2000 records, how should i tell PowerApps to collect remaining 2000 items from SharePoint using Batch IDs. 😞

 

Also, how should i proceed if i have more than 5000 items in the future. 

 

Ex: Collect(myCollection1, DataSource) - for first 2000 records.. 

      Collect(myCollection2, DataSource) - for next 2000 records.
      Collect(myCollection3, DataSource) - for next 2000 records.

 

Does PowerApps understand the above example to collect all 6000 records from the SP list? or is there any other code that i should add to do so?

 

 

Thanks

Sasidhar

Dual Super User
Dual Super User

Re: How to pull more than 3000 SP list items to PowerApps

Hi @sasidhar_mudra 

 

Using three separate collections and referring to the same datasource won't save the batches directly. all the three collections will have same data.

Ex: Collect(myCollection1, DataSource) - for first 2000 records..

Collect(myCollection2, DataSource) - for next 2000 records.
Collect(myCollection3, DataSource) - for next 2000 records.
This will collect same date in all the three collections as the DataSOurce will get only first 2000 records everytime.

Did you get a chance to look at the blog post I shared?
For this, you need to create a calculated type column in your SP list, which is a text field but get autoincremented based on ID.

Once that field is configured, you can follow the steps in Blog post to create the collection based on the entered values.

https://www.appliedis.com/how-to-use-galleries-in-powerapps/ You can follow the same steps, I did for the OrderCollection.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

sasidhar_mudra
Level: Powered On

Re: How to pull more than 3000 SP list items to PowerApps

@yashag2255 ,

 

May be I should explain more about my scenario.
I am creating an app for our team to do a group discussion with different teams. I have a employee head count with me which is almost above 5000.
I am trying to create a DropDown (Manager Name) and ComboBox (Employee Name) for multiple selection of employees. 
I created an app and since the app gets only first 2000 records available in the SP list, i am not able to see some of the employees names who are sitting in 4001st row or 4500th row.

Coming to your point, yes, I did created an AutoIncrement column in my SP list with the column name as Batch_ID.

 

I created something like below but i am still getting the Blue Line (Delegation Error). Pics for your reference with the collection i created using Batch_ID and SP list .

 

Though i checked your blog on this, i still could not understand. May be because I am not from programming background 😞
Can you please help me in understanding maybe in layman terms.

 

Thanks for your response.

 

Collection.jpgCollection_Batch_ID.jpg

Thanks

Sasidhar

 

Dual Super User
Dual Super User

Re: How to pull more than 3000 SP list items to PowerApps

Hey @sasidhar_mudra 

 

The ID column gives the Delegation error and hence is not able to get more than 2000 records. For this, you need to use the newly created column and I am assuming that it is a string type calculated column.
Formula on Calculated Column: ="Item"&TEXT([ID])

Clear(MyCollection);
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item1")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item2")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item3")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item4")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item5")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item6")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item7")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item8")));
Collect(MyCollection,Filter(DataSource,StartsWith(Title,"Item9")))

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

sasidhar_mudra
Level: Powered On

Re: How to pull more than 3000 SP list items to PowerApps

@yashag2255 ,

Hi Yash, 
The Batch_ID column in the previous image i created was a new column. Below is the screenshot how i created the Batch_ID column with option as Single Line of Text.

Formula in the field was based on the Manager Name appearing.

COllection_Batch_ID1.jpg

 

Thanks

Sasidhar

Dual Super User
Dual Super User

Re: How to pull more than 3000 SP list items to PowerApps

Hey @sasidhar_mudra 

 

Okay, even if the new column is created, the formula still refer to ID column in PowerApps. Can you try to update this to:

Clear(MyCollection);
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"1")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"2")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"3")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"4")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"5")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"6")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"7")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"8")));
Collect(MyCollection,Filter(DataSource,StartsWith(Batch_ID,"9")))

As per Microsoft documentation, StartsWith is delegable so if you will use the above expression, you will be able to get the records.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

sasidhar_mudra
Level: Powered On

Re: How to pull more than 3000 SP list items to PowerApps

@yashag2255 ,

 

I still see the delegation as an issue. Pic below.

Collection_Batch_ID2.jpg

 

Thanks

Sasidhar

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,247)