Preloading more than 2000 items from a SharePoint List, how to create an index-like ID?

[Anonymous]

Hi everyone,

So I'm pre-loading more than 2000 items to the gallery from a SharePoint List.

I'm using the approach where I created an "ID" column in my SharePoint List to be able to load my items per 2000 items without having the delegation warning. I have no problem with this approach and everything works correctly.

Below is the code on how I do it. 

However, since the users will keep deleting and adding new data to the SharePoint List, I can see that the "ID" is getting bigger because every time a user adds a new data, the new "ID" will be the (Last ID + 1). This will cause issue if I don't manually increase the "ID" ranges of the above code.

Let's say the users have been deleting and adding data for a long time, so that the smallest "ID" has gone over 4000, then my above code will not be able to load this data to the gallery.

I'm trying to find a way to make this "ID" behaves like an index so that it fills the empty spot, where if let's say the current data have "IDs" of 5, 6, 7,

When user adds a new data the "ID" will start again from 0, 1, 2, 3, 4 and after that 8, 9, and so on.

I hope my question makes sense, and thank you before!

[Irfan]

Hi,

I've got an app where I am pre-loading approx 80K records from Sharepoint list into a collection. It slows down the app a bit when it loads - entire load takes approx 20-25 seconds - but I've put in a loading page to keep the user engaged.

Now coming back to the limitations in Sharepoint.

There are 2 limitations that you'll need to worry about:

1) Max records fetched from Sharepoint is 2000 which you are well aware of

2) Sharepoint has a limitation of 5000 records on a view. This limitation is native to sharepoint but it impacts the way how records are collected in PowerApps as well (through ClearCollect Filter). If your filter is not precise than it will only filter based on the first 5000 records it fetches. You will not see any error/warning for this issue until your record count in Sharepoint is greater than 5000 records. You will than notice this as an error or warning(cant remember) in PowerApps. In Sharepoint, when you go to view your records, you will notice this coming up right in your face - there is a limit of 5K records for default view.

Support - Office.com

Workaround

There are few different things that you need to do to get your app working

1) Sharepoint - Modern Experience Site

Always use Modern Experience Site.

2) Sharepoint - Extra Column

In your sharepoint lists, have an extra column whcih stores row numbers Eg "RowNo" and define it as a Number. When you are patching from your PowerApps, the patch command will first get the last RowNo (i.e. highest RowNo from your list) , add 1 to it and patch the details. In this way, you will always have a next number from your Sharepoint List.

Eg: Set(maxRowCount,First(Sort(coll_AllUnitsAndEquipments,RowNo,Descending).RowNo));
coll_AllUnitsAndEquipments is the collection which stores my records (approx 80K). I'll show you how to collect that as well

3) Sharepoint Indexing

The next part is indexing. Add indexing to the custom column created above. This will speed up your process

The column “ID” is non-delegable in PowerApps. Creating an extra column such as above, chaning its type to number and indexing it will fix the issue of limitation of 5000 records on a view and will massively improve performance in your app. Alternatively, you can create another column which will be a copy of your ID column and use that in your app.

4) Collecting records in PowerApps

Its best to collect records in Sharepoint when the app loads. This will make your app run much faster.

But how do we collect more than 2000 records in a single collection? The answer is you need to collect data in different collections and than merge all your collections into a single collection.

Eg: In the below example, I am collecting 8000 rows in different collections. Since I am running the collection inside concurrent command, the process is much faster than expected. Finally I have merged the collections in a single collection. You can see the collection name referenced in point no 2. Below is an example of just 8k records but I have been able to strech it to 100k records easily. Sharepoint can store uptp 30 million records. We just need to find a way to fetch those records 🙂

Concurrent(ClearCollect(
CollectionA,Filter(LOTO_UNITS,RowNo<2000)),
ClearCollect(CollectionB,Filter(LOTO_UNITS,RowNo>=2000 And RowNo<4000)),
ClearCollect(CollectionC,Filter(LOTO_UNITS,RowNo>=4000 And RowNo<6000)),
ClearCollect(CollectionD,Filter(LOTO_UNITS,RowNo>=6000 And RowNo<8000))
);

ClearCollect(coll_AllUnitsAndEquipments,CollectionA,CollectionB,CollectionC,CollectionD);

Hope this helps.

Warm Regards,

Irfan

[RandyHayes]

@Irfan 

I think you stated your own dilemma - " SharePoint can store up to 30 million records. We just need to find a way to fetch those records"

If you move to the point where you are trying to pull in 30 million records into your app...you're in for a world of issues.

Start with delegation in mind.  You should be able to get what you need from a datasource without pulling in all the records if you have introduced the proper delegable criteria.  

Consider using delegable pre-filters on your data and then narrowing with non-delegable.

Ex.

With({_prefilter: Filter(dataList, Status="Open" && Created > Date(2021, 1, 1))},
    Search(_prefilter, TextSearch.Text, "Column1", "Column2")
)

The prefilter is delegable and will return records (always make sure your prefilter returns less than your record limit).  The Search function is non-delegable, but, since it is applied to a table that was gathered with a delegable filter, there is no issue with it.

There is a whole list of ways to make data delegable, and it has to be planned in properly.

Things like Distinct on large lists, for example, needs to be planned out properly because Distinct is not delegable.  But this can easily be overcome.  And so can many other scenarios.

As for the RowNo, you can run into issues with this because of deleting records and records changes over time.  If you MUST put in this kind of "index" column, it is best to do so based on the already existing index - the ID.

This can be done in PowerAutomate as a flow that runs when a record is created to simply set the RowNo to the ID.  If immediate results are needed in the app for the RowNo, then consider a double hit to the data to first submit the record and then update the record with the ID that was created.  It's a double hit, but it is faster in the long run over refreshing the data or constantly rebuilding a 30 million record collection - or even 80k.

[Irfan]

Thanks @RandyHayes . My issue was around the prefilter which may sometimes have more than 5k records. No matter what I tried, I was not able to get a workaround that issue and thus had to resort to creating collections during app load.

List has 5 columns - Unit, UnitFlag, Equipment, EquipmentFlat, RowNo

There are approx 120 units and the UnitFlag determines whether if the record is unit or not.

Rest in 80K records are equipments. EquipmentFlag determines that its an equipment. In the same row, it also hads UnitName.

Each unit can have multiple equipments. Most of the units have less than 5K equipments but there are some units which have more than 5K equipments. And this is where I started seeing missing data.

Also, my usecase is simple - I am not going to delete records from the sharepoint list. Might add new records but that too would only be a couple of dozen records every year. Its more of a static list. Thus avoided going to PowerAutomate. 

I have tested the app with 80K records. The initial load takes around 20 - 25 seconds. But once the app loads, its superfast.

Any ideas on how I can remove the collections in this scenario ?? This will cut down the load time significantly.

-Irfan

[RandyHayes]

@Irfan 

Actually in this case because you are only working with the data as static, then it is acceptable to use your approach.

The problem usually comes in with cases where there is CRUD work to do in the app.  That is where things start to break apart.  But if only dealing with viewing data, use what works for sure!!

[BUApps]

Hi Guys if you want to make the load event dynamic then watch this video 

(1685) PowerApps 4 Access Devs - Load 10,000 Records - YouTube

[RandyHayes]

@BUApps 

Still begs the question - why would one want to pull that much information into a low-end app? 

It's kind of like having a Google search app that needs to download all of the data from the Internet into the app memory so that when you search it can find what you are looking for.  

The concept is to push (delegate) to the source what you want so that it returns as little data as possible.  

This harkens back to even the early days of data (databases) - you once had to pull all the data, but that was a problem for slow networks.  So a query language was developed so that you could "say" what you want and get back smaller amounts of data.

A PowerApp should work with the same concepts.  Showing a user 10k records is not all that valuable if they only need a few.

$0.02

[BUApps]

Well in my case we have a gallery that works like a Gantt chart, so with over 5000 items in the gallery which is quite common in large timelines then I need the ability to show the last item as much as the first item. Or what about a Stock list where you need a sort on it. This also negates the threshold limits. And keeps it away from a premium service.

[Azalia20]

Hi there, I think you could use FirstN and Sort function for this...

FirstN(Sort(DB_Transportation, IDColumnName, Descending),2000)

[FernandoRosiere]

Hi! 

Is there any way to use "Distinct" with more than 2k records without giving delegation problem?

Thanks!