Home

Welcome to the sp_delegation_solution wiki!

How this solution works:

• Uses Power Automate + SharePoint API to get around most delegation issues
• Dynamically generates OData queries from Power Apps to retrieve data
• Handles pagination gracefully by using the next link provided by the SP API

For these reasons, the way you retrieve data into Power Apps will be different! Don't worry you can continue to write data the same way (Patch and SubmitForm) and you only need the OData style for the large lists in your projects. Filter should still be used with other lists.

Power Apps converts these functions to OData queries but it, unfortunately, does not take advantage of its full power. The idea is to generate it ourselves.

Tutorial

• Get the initial sample code from here
• Start a new app the demo solution
• Add the SearchFlow to the app
• Add a button to the screen and update the OnSelect property with the code in the sample file
• Add a gallery to the screen and update the Items property with the code in the sample file'
• Add the TicketingSystem_ServiceTickets and TicketingSystem_Teams data sources to the app.

So let's look at some examples of some common patterns and how they can be re-interpreted to a dynamic OData query.

Let's start with some delegable queries:

Note

All column references are the internal names

Example 1: No Query

• Normal variant: TicketingSystem_ServiceTickets
• Solution variant:

Example 2: Basic Filter

• Normal variant: Filter(TicketingSystem_ServiceTickets, ServiceType.Value = "Problem")
• Solution variant:

Example 3: Multiple conditions

• Normal variant: Filter(TicketingSystem_ServiceTickets, ServiceType.Value = "Problem", ServiceCategory.Value = "Networking")
• Solution variant:

Example 4: Filtering with a control

• Normal variant: Filter(TicketingSystem_ServiceTickets, Or(ServiceType.Value = cmbServiceType.Selected.Value,cmbServiceType.Selected.Value = Blank()), ServiceCategory.Value = "Networking")
• Solution variant:

Example 5: Limiting returned columns

From the previous examples, it is clear that the solution versions of the queries are more verbose, and therefore, the normal expressions are easier to write. So why are the solution versions better for your large data sets? Well, this is the very first benefit: you can limit the number of columns returned. Typically, delegation and performance are discussed in terms of the rows of data retrieved but in some cases, the number of columns to be returned is just as important because it contributes to the size of the returned data set especially for wide lists.

There is no OOB way to limit the columns returned in Power Apps with SharePoint as the data source. Note that ShowColumns is not delegable

• Normal variant: ShowColumns(Filter(TicketingSystem_ServiceTickets,Or(ServiceType.Value=cmbServiceType.Selected.Value,cmbServiceType.Selected.Value=Blank()),ServiceCategory.Value="Networking"),"ID","ServiceType","TicketID")
• Solution variant:

Example 6: Selecting complex column types

• Normal variant: ShowColumns(Filter(TicketingSystem_ServiceTickets,Or(ServiceType.Value=cmbServiceType.Selected.Value,cmbServiceType.Selected.Value=Blank()),ServiceCategory.Value="Networking"),"ID","ServiceType","TicketID","Author","TeamToImplement")
• Solution variant: