09-03-2023 16:42 PM - last edited 07-09-2024 20:00 PM
No Delegation Limit - SharePoint List Power App
This template app & flow set-up presents a method of filtering, searching, sorting, & loading a large SharePoint list data without delegation limitations.
https://youtu.be/EH-YndEPIiI?si=sy5Tb72c7amzeelX&t=94
(Each Filter & Search in the video is a new query of the datasource. It is operating over 20,000+ items in 1-14 seconds each time depending on how much the datasource has been narrowed down by filtering & searching.)
Usually one could not get a Power App to get more than 2000 items across a large SharePoint list with more advanced filters and searches, especially on non-standard fields like a Created By column, multiline text column, or on many columns at once. This template does that & more using a Power Automate flow and the ParseJSON( ) expression in Power Apps.
Say goodbye to SharePoint list delegation limitations & issues.
Load a much larger number of SharePoint records into a Power App, up to 100MB worth.
And avoid the SharePoint interface 5000 list view threshold with an app interface that can display many more than 5000 records on a screen. All without premium connectors.
Setting-Up A Demo App
Download the Excel workbook data & solution package at the bottom of the post.
First we are going to use the Excel workbook to import a large set of data into SharePoint.
Then go to the home page of the SharePoint site where you want to create the demonstration large SP list. Get the site address from the url.
Insert the site url into the address input of the Excel pop-up & enter "Large SP List" for the new list name. Select Next. Check over the columns on the next menu & select Finish.
That will start loading the Excel rows into a new SharePoint list on the site.
Next go to the main Power Apps page. Select Solutions on the left-side menu. Then select the Import Solution button.
On the next menu to appear select Browse, then find the solution zip file you downloaded from this post & select that zip file for the import. Select next on the following 2 menus.
On the following menu check that the connection listed is the one you want to use for this template app & flows.
Once the solution is successfully imported, find the solution in the table of solution display names & click its name. Then once in the solution, select Apps in the left side menu to see all the apps for this solution. Select the 3 vertical dots next to the Large SP List Delegation Workaround Demo app item & select Edit from the dropdown.
Once the Power Apps Canvas editor loads select the Power Automate logo on the left menu to bring up the flows menu. Then select Add flow. Then select each of the flows, Large SP List Query & Large SP List Query Sorts, to connect them to the app.
After connecting the flows, select the Datasource icon on the left side menu. Select Add data & search for SharePoint. Select the SharePoint option. Select a SharePoint connection.
Then on the right-side menu that pops up, select the site where you loaded the demonstration list. Then for the list name select the Large SP List from the options & select Connect.
Next, make sure the ParseJSON preview feature is active. So go to the 3 horizontal dots on the top command bar, select Settings. Then on the Settings pop-up menu, select Upcoming features, & scroll until you see the ParseJSON function and untyped objects entry. Make sure the toggle below the entry is turned to On.
Then go to the Tree View on the left-side menu & select the App input at the top of the other left-side menu. Select the OnStart parameter, extend the formula bar, & set the SiteAddress and ListName variables to use the site address url & list name of your SP list.
Then to load & view the items from the new flows & connections, on the tree view select the 3 horizontal dots next to the App input & Select Run OnStart to run the query flow to grab the initial set of items from the SP list.
Once the items load, then you are ready to start exploring.
There are a few things you may want to note...
The OnStart property runs the query flow with default & blank parameters when the app initially loads. It saves the string of the JSON array returned into a variable. Then the Items property for the main gallery with all the records makes a Table( ) from the ParseJSON( ) of the variable with the string of the JSON array.
Notice how each app component referencing something from the Gallery datasource must include an extra .Value before referencing the actual field name. This is because the table made from the JSON array actually only has one column called Value that then holds the JSON object for the entire record.
And Lookup & People columns actually have their own sub-object that they must reference, so it becomes .Value.LookupColumnName.LookupFieldName.
And again, see that the main gallery can use both filters & a search of all the columns in the large SP list to find items (including a multiline text column).
You can see on the OnSelect of the Search & Filter button that this is done by creating a set of Odata filter queries for the Get items section of the flow & by providing the flow with the search box input term that it uses in a Filter array action to search for that term across all the columns of each record. It then saves any newly filtered & searched output to the variable that is used for the main gallery's ParseJSON( ) & Table( ) expressions.
These resources may help in making dynamic odata queries: 1. https://www.youtube.com/watch?v=I8FdUmECAn8 | 2. https://www.spguides.com/power-automate-odata-filter-query/ |
Note that odata filters can use related lookup column too, like Author/EMail.
The template is also already set to navigate to a details screen with an edit form whenever a user selects an item Title value. The Item property of the edit form shows how the app leverages the direct connection to the SharePoint list & the main gallery built off the JSON array of SP data from the flow. It uses the ID field that came with the JSON array flow data of the selected item in the gallery to then filter the direct SP list connection to just that item for the edit form. So if you ever adjust this template for your own use, make sure to include the SharePoint ID column & values in the OnStart SelectColumns variable input of the query flow. That way you can use this set-up to make editing data the same as any other Canvas Power App where you use a direct datasource connection on a form.
Also...
If you are trying to display the values of a multiple choice field using this method, see this post.
If you are trying to GroupBy( ) on a large number of items using this method, see this post.
Thanks for any feedback,
Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).
And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.
Version 1.5 Speed & Ease of Use Adjustments
Changes the flow query app inputs to accept more parameters so less edits need to happen in the flow. Things like SiteAddress & ListName can now be edited from the Canvas App builder. Developers can now input a SelectColumns query to further narrow down the data they are querying to only the columns they list, which improves performance on larger data reads.
The default read batch size was also reduced from 5000 to 2500 and the default batch reading Apply to each loop concurrency was increased. This also improves the data read speed a little more.
And some additional odata columns are now removed early in the flow before it gets to other actions or to the app. This should make another small speed improvement & reduce the total data size transferred to the app.
Version 1.7 Expand Parameter for Lookup & People Columns
The flow inputs on the app now include a parameter ExpandColumns where developers can input the Lookup or Person ColumnName/ID in a comma separated list to identify which Lookup or Person columns should include their related fields. Then each related field needs to also be listed in the SelectColumns flow parameter for it to be included in the outputs. For example for the Created By & Modified By columns, ExpandColumns includes "Author/ID,Editor/ID" as those are the column back-end names & ID references. Then the SelectColumns parameter includes "Author/Title,Author/EMail,Editor/Title,Editor/EMail" so the query knows to get the Created By & Modified By Title (display name) & email values.
Also further reduced the ReadBatchSize from 2500 to 2000 to try to get a little more query speed. And adds an app flow parameter to adjust the batch size from the app itself.
Version 2.1 Large Reduction in Action API Calls Per Query
I started doing some scaling calculations from the perspective of the most likely users of an app with such a set up. Office A1-E5 licensed users get 6000 action API calls per day per license across Power Apps & Power Automate (https://learn.microsoft.com/en-us/power-platform/admin/api-request-limits-allocations). If this set-up is used on a 1 million item SP list with a 5000 ReadBatchSize, then each flow query in previous versions would take (1,000,000 / 5,000) * 4 = 800 action API calls. So each of those standard licenses would have only had 6000 / 800 = 7.5 queries a day on a 1 million item list before it exhausted their daily action limit. I've gone back through & cut the number of actions in the most intensive part of the query in half & used some other adjustments to keep the overall speed/performance of the query the same. So with this version 2 the average A1-E5 license should be able to query a 1 million item dataset up to 15 times per day or a 100,000 item dataset 150 times per day with a 5000 ReadBatchSize before hitting their daily action API call limits.
Now, one can always increase users' total queries / reduce the API calls used by reducing the MaxItemCheck parameter in the app, but the trade-off is the lower the MaxItemCheck the less most recent items the queries will filter & search over. So if you have something like a 10 million item list & you are fine with user queries only searching over the most recent 250,000 items, then you can reduce the MaxItemCheck to around 250,000 & all standard license users will then get up to 60 queries per day on the 250,000 most recent items of that 10 million item dataset.
Version 2.3 More Performance & Speed Improvements
I figured out the main thing holding back the query performance isn't the amount of calculations involved, it's just the amount of memory involved moving any large block of data between the actions after the SharePoint HTTP Get items loops. So reducing the number of calculations in the expressions for those actions won't do as much as just removing as many actions post-data load as possible, even if it means putting more complicated expressions in the remaining actions.
Also the odata = nometadata header parameter that most blogs suggest does not actually remove all the odata metadate. I've corrected the HTTP call healders to actually remove all the odata properties and this has also reduced the query time.
In testing I now have the 20,000 item example data loading in 7-11 seconds and filters & decent searches loading in 1-5 seconds. Bad searches that return most of the list items are the only queries that may see performance issues & can go on for 18 seconds. This is because the flow query is optimized to increase speed by using more action API calls when it anticipates a large load without a search term, but if given a search term, it expects that search to significantly reduce the amount of data going through the flow so it uses a method that is slower but that uses significantly less action API calls.
Version 2.8 Nested Sorting & Fix To Sort On Null Values
The flows can now take an additional column name value for nested sorting. So if you have a lookup or person column then you can input the name of the lookup or person column for the 1st value, then input its nested column name for the 2nd value. This helps sort on things like Created By columns because now it can take in "Author" as the 1st column value and "Title" as the second column value. However, nested column sorting will take longer on larger queries. For the best large query speeds avoid sorting on lookup or person columns.
Next, the flow(s) will fail if the Sort( ) expression is attempted on a column with null values. I have input an expression to replace any null values with a blank text string "". So the flow and app can sort on any string/text column with or without null/blank values. But if one tries to sort on any number type column that contains null values, then the flow & app will error.
Anyone who has issues with the Solution import can also try using the legacy Power Apps & Power Automate import methods to import each component of the solution here.
watch?v=EH-YndEPIiI
By backend I mean the actual SP list. This is an image of the list now. AFTER I renamed the columns in SP to remove the spaces. When there were still spaces in the SP list names, putting them in the ONStart code with spaces seemed to cause this process to fail. And I tried with various formatting on those 3 lines in my other post. Using Double and single quotes around them.
The only way I could get it to work in the code was to RENAME the fields on the Sharepoint list to NOT have spaces (as shown in the image). I am asking, is there a way to make the code below work on a SharePoint list that does have spaces in the field names.
I did NOT try ProviderName in the code below until AFTER I udpated the SP site, but I will give that a go!
This is the line from the on start. I could NOT figure out how to make it work using the SP names with spaces in the names. IE: the original SP field had Provider Number. When this code ran with Provider Number it errored saying it was not a valid name. I then tried adding it as "Provider Number" and again as 'Provider Number' in the highlighted area.
@BDumm Back-end SP names does not refer to the display names on the SP list. Again, the back-end names are in the url of the column edit pages of the list settings.
Go to the gear icon on the top right, go to List settings, select a column with special characters in it, then go to the end of the url on that column edit page to see the back-end name.
Ok, forgive my ignorance. I am new at this part 🙂 I was referencing SHAREPOINT itself as like the backend that the Power Apps (Front end) was using.
I NEVER noticed that other name formatting in the url before.
This is awesome! I thank you so much for the clarity.
So I have this other field I am looking at:
And yes, I see it now in the URL
Field=Preferred_x0020_Name
So, I am thinking then to format this in the Code I would do it like this and use that name from the url.
If so, that makes sense now why when I removed the spaces in the description, it updated the name shown on the url to just match the description with no special characters.
So thank you again!!!
Set(SelectColumns, "ID,Title,Created,AuthorId,Author/Title,Author/EMail,Modified,EditorId,Editor/Title,Editor/EMail,Preferred_x0020_Name"); //Comma separated
I've been trying for hours to get it to work.
How much do you charge for teaching / consulting?
Can you send me a DM please.
I don't have a lot to spend but this is such a unique way to get around delegation I really want to understand it.
If it is something like this that can be covered in a <20 minute call I don’t charge.
But please share screenshots of the error you are getting.
And did you see this…
”As for the lookup columns you should follow the example of the Created & Modified lookups on the example. So in expanded columns put the LookupName/ID like Author/ID or Course/ID, then in the SelectColumns include the expanded lookup columns you want like Author/Title or Course/Title.”
This is the exact error:
{"odata.error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The field or property 'Course_x003a__x0020_Course' does not exist."}}}
clientRequestId:
serviceRequestId:
It happens in this part of the flow:
Here is how I have the variables.
I am not sure how to Reference the lookup column for Course_x003a__x0020_Course:
(I have tried every combination of exapandcolumn Course/ID Course/ClassTitle... everything I could think of)
This is the LIST SETTINGS of the Course_x003a__x0020_Course:
You can see it is a lookup column for Course List/Course
This is the "Course List" "Course" column... (I also tried setting it to ClassTitle... but it still gave the same error):
I have no idea what I am doing wrong.
I have tried setting the expandcolums and selectcolumsn to every combination I can think of... and keep getting the "does not exist error. I tried
----------------------- extra screen shots
Screenshot of the Course Status landing page.
The Course List screenshot
I think you are trying to reference the additional column created in your base list off the Course lookup “Course: Course”.
You can’t reference things that way.
If you have a lookup column Course & the column you wanted to see from that lookup list was also named Course, then in the SelectColumns you would need to put Course/Course after putting Course/ID in the ExpandColumns.
@takolotaThank you for this post its been most helpful and I appreciate your work. I have 1 thing I haven't been able to work out.
I have a My Request screen that needs to filter based on the current user. On that screen I have a Filter Gallery to create buttons so users can filter their requests by certain parameters. I just can't get it to filter.
For the main gallery I used your formula, which works great. I see everything. But that's the issue I need to filter it out.
Items: Table(ParseJSON(QueryResult))
For the Filter Gallery
I have Filter Button
Text= ThisItem.Filter
OnSelect = Set(_requestTypeFilter, ThisItem.Filter); UpdateContext({varFilterBy:ThisItem.Filter});
For the Filter Gallery
Items: Table({Filter: "All"}, {Filter: "Draft"}, {Filter: "Submitted"}, {Filter: "Approved"}, {Filter: "Rejected"}, {Filter: "Batched"})
OnSelect: UpdateContext({varFilterBy:ThisItem.Filter});
I tried to add the following but its not working, and I am not sure if its correct.
Sort(
Filter(
Table(ParseJSON(QueryResult)),
ThisRecord.Value.Author.Title = varCurrentUser.Email,
Switch(
varFilterBy,
"All",
ThisRecord.Value.Author.Title = varCurrentUser.Email,
"Draft",
ThisRecord.Value.Status = "Draft",
"Approved",
ThisRecord.Value.Status = "Invoice Approved" || ThisRecord.Value.Status = "PR Approved",
"Submitted",
ThisRecord.Value.Status = "Invoice Submitted" || ThisRecord.Value.Status = "PR Submitted",
"Rejected",
ThisRecord.Value.Status = "Invoice Rejected" || ThisRecord.Value.Status = "PR Rejected",
"Batched",
ThisRecord.Value.Status = "Batched",
true
)
),
Text(Value.ID),
SortOrder.Descending
)
-----------------
Previously to avoid delegation limits I had it set up collections, one in ascending order then descending order then combining them. Which worked but it was slow
Concurrent(
ClearCollect(
colChunk1,
Sort('IT Purchasing', ID,SortOrder.Ascending)
),
ClearCollect(
colChunk2,
Sort('IT Purchasing', ID,SortOrder.Descending)
)
);
ClearCollect(
colPurchasing,
colChunk1,
Filter(colChunk2, Not(ID in colChunk1.ID))
);
Clear(colChunk1);
Clear(colChunk2);
Then in the Items of the main gallery I used:
Sort(
Filter(
colPurchasing,'Requested By'.Email = varCurrentUser.Email,
Switch(
varFilterBy,
"All",
'Requested By'.Email = varCurrentUser.Email,
"Draft",
Status.Value = "Draft",
"Approved",
Status.Value = "Invoice Approved" || Status.Value = "PR Approved",
"Submitted",
Status.Value = "Invoice Submitted" || Status.Value = "PR Submitted",
"Rejected",
Status.Value = "Invoice Rejected" || Status.Value = "PR Rejected",
"Batched",
Status.Value = "Batched",
true
)
),
ID,
SortOrder.Descending)
Again it worked. But it was slow. So am trying to apply what you did in your example. Is it possible?
I think you are trying to filter this as if it was a collection. But how I show it filtering in the demo, the app forms a filter query string that can be applied on the flow side to pull in only the relevant records using the SharePoint HTTP action in the flow.