cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rohitsbots
Resolver II
Resolver II

Work with 5000 records in my powerapp

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.

10 REPLIES 10
Pstork1
Dual Super User
Dual Super User

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

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.

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

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
Resolver I
Resolver I

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

 

 

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.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

Ramole
Super User
Super User

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.

 

CaptureDelegation.PNG  

 

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.

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.

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 ?

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (4,015)