Work with 5000 records in my powerapp

[rohitsbots]

I want to work with 5000 records in my powerapps. I am trying to build an app to pull 5000 employees data and then do a filter to get the results for each manager. Which backend should i use ? Sharepoint ? Excel ? CSV ? JSON ?

Due to delegation issues i don't want to use sharepoint so looking to see if i can use JSON. But how do i get the data from JSOn file in my app. Can you anyone share an example on how they have achieved this ? I get some weird characters when i read the JSON file in a flow in order to send to a collection and when i try to convert it using base64 it does not do anything.

[Pstork1]

Any data source other than a local collection will have delegation issues with 5,000 records.  Some data sources like SQL or Dataverse will have better delegation support than SharePoint, but they all do Delegation.  The key is to use delegable functions so you don't have to retrieve all 5,000 records.  Retrieving all 5,000 records and using them locally is time consuming and will lead to data errors when someone else makes a change while you are working with the local records.

What do you want to filter on once you have the records?  The key is to find a way to do that with a delegable function.

[rohitsbots]

Hey @Pstork1  so it is employee data which is not going to change frequently. I need to filter and pull all the direct and indirect reports of the person they choose. So I have to access and filter on all 5000 records. This is why I’m thinking to load all of the data in a local collection and then do the filter.

[Pstork1]

If you want to use it as a readonly data source then you can do an import from excel.  That runs pretty quickly, but you can't use power Apps to update the data then.

[rohitsbots]

So the new data will be loaded let’s say once in a week by a job not from the powerapp as the app will only read the data. So how do I automatically load data from excel without any manual intervention. I know import from excel will be manual, correct ?

[Bochie]

If you want to pull 5000 items to a collection into Power Apps without delegation issue, you can use Power Automate Flow and SharePoint. Use the Send an HTTP Request to SharePoint and configure it so that you only get the columns that you need. Paul Murana has an excellent blog about it here:  https://www.tachytelic.net/2020/04/use-rest-api-power-automate-get-over-5000-items-from-sharepoint/. By reducing the metadata output in the REST API call, one is able to get 5000 items from SharePoint via Power Automate without delegation issues in mere seconds (of course depending how many columns you specify, but say you just collect DisplayName, EmailAddress and ID, it'll be Very fast). From the result of your REST call, you can then do a filter data operation. Hope this helps.

-Bochie

[Pstork1]

The import from Excel is used just like a Data Source.  But its a Read Only data source.  So it will load the data each time you start the app.  If the data has changed since you last loaded the app it will load a new copy.  It loads quickly because its strictly importing a Read Only copy.

[Ramole]

Hi @rohitsbots 

You can load by filter and get the result back, doesn't matter the size of the data in SharePoint as long as you fellow delegation and use correct formula, even Combo box you can get search result over ID 25000 back in to PowerApps while you Data row limit set 1, will get the result back.

  

For me I don't use collections on data source I stay away from it, it will slow the app and you will have to refresh every new data added  on top of that it depends your device memory, that's my opinion.

[rohitsbots]

Hi @Pstork1 Import from excel is static data. Once imported how will it load the updated data from the excel that i used to import ?

[Pstork1]

I thought it would refresh the data if you closed and re-opened the app.  But I just tested and it doesn't.  Your best bet is still to figure out how to work with the data using delegable queries.