How to pull more than 3000 SP list items to PowerApps

[sasidhar_mudra]

@v-siky-msft ,

Hi Sik,

I have a SP list with more than 4000 items. The app is pulling only 2000 as there is the limit.

Even in the collection. Is there a way that i can pull all 4000 items into collection? Tried looking at different posts here and could not understand how to do it.

Would you be able to help me on this?

Thanks

Sasidhar

[yashag2255]

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!

[MatthewInwards]

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]

@ datamaster, Yes, i did pull the first 2000 items using Collect. What about the rest? Thanks Sasidhar

[sasidhar_mudra]

@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

[yashag2255]

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]

@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.

Thanks

Sasidhar

[yashag2255]

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]

@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.

Thanks

Sasidhar

[yashag2255]

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]

@yashag2255 ,

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

Thanks

Sasidhar