Delegation
Power Apps Delegation – SharePoint
In this blog, the content is focused on users who have decided to use SharePoint as the data source for their Power Apps suite (generally for licensing costs). Other factors also to be considered, include whether any direct SharePoint direct editing control will be given to users and if so, if any of this needs data sheet “quick edit” access.
This discussion assumes SharePoint datasheet editing is not needed and any interaction will be on Power Apps integrated forms. I mention this specifically as some field types (Choice, Lookup) require different controls in SharePoint datasheets for user input.
I have also assumed that the Data row limit for non-delegable queries in Advanced settings has been set to 2,000 when I refer to that figure.
With() Statement managing Delegation
In this blog, I will discuss a method that will allow the combining of non-Delegable filters with Delegable items in a way that will remove Delegation warnings and combine the process into one statement.
There are some important things to remember in this: –
- The With() statement is effectively creating a (very) temporary variable/collection within the statement which only “exists” for the life of the statement execution.
- It is not Delegable in itself, meaning that it cannot “hold” more than 500-2000 records depending on your settings (I will refer to 2,000 here).
- However, if you can isolate less than this number with Delegable filters, then any statement using the resulting data is Delegable.
- You can also use this on an entire list (no Filter) with less than 2,000 records for the purpose of removing the Delegation warning.
- NOTE: If your With() statement potentially produces more than the 2,000 records, you will not get a Delegation warning and the second filters will produce results on only the first 2,000 records matching in the With().
Distinct values for a large list field in Combo Box Items
This idea is an extension of a workaround in Combo Boxes dealing with large lists and the Delegation issues surrounding these and the Distinct function. I have tested this on a large list (30k items) and it works perfectly.
Two caveats here – the number of items displayed in your combo box and the number of filtered items subject to the Distinct function (after the leading letters are entered) will both be subject to your Delegation Limit, but these should not generally be an issue.
This example is using a big list Aircraft with a field Airline. The idea is to start typing the Airline name and all Distinct values starting with the input will then be avalable for selection in the Combo Box. There are two “tricks” here – one is to use the Combo Box SearchText to limit the fields available and then avoid the Delegation issue using a With() statement to “pre-filter” the output subject to Distinct.
Delegation Management – Multi-Choice Combo Boxes
This article looks at some degree of Delegation Management where a Multi-select Combo Box is used to filter a Data Source for any matching values in either a Text or Single Choice field. The output is a Table and can be used as the Items of either a Gallery or a subsequent cascading value Combo Box.
In the below, I have a list of Devices and a field (Text) called DeviceType. I have a Combo Box (cbType) containing a Distinct list of the DeviceType (see this blog on how to do that in a Delegable manner).