Power Apps Delegation – SharePoint

Content

• What is Delegation
• Problem Field Types
• Problem Operators
• Collections
  • Newest Records
  • Double Size
  • Bigger than 4,000
  • Newest xxx records
  • Option fields less then 2,000 each
  • Delegable Filter
  • Other Collection uses
• Using the With() Statement
• Data field types
  • Boolean
  • Complex
  • User identifiers
• Operators
  • Search / in
  • IsBlank
  • Other non-Delegable
• Summary

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.

Back to top

What is Delegation? 

Delegation can be summarised as “who does what work” when data is requested by Power Apps from SharePoint. Because SharePoint is a “shared” service between all people who access it and some queries are more complex from a server processing point of view, only certain queries are “Delegable”.

• In a Delegable query, SharePoint will do the work for you, sort out what you need and send back just the data requested. This is very efficient and generally quick.
• When a query is non-Delegable, SharePoint will bundle the first 2,000 records of the list and send all this back for Power Apps to then do the query required. You do not have access to any records past this.

What is a Delegable query? 

There are three fundamental parts to this – Functions,  Field Types and Operators. For a query to be Delegable the Function needs to support Delegation and ALL field types and ALL operators need to be Delegable. 
An important thing to note is that ALL queries on Collections (tables temporarily stored in Power Apps) are Delegable (actually not subject to delegation as the query is being done “locally” and not on the SharePoint List or Library).

Blog Content

This is a large subject, and I have not included detail of all the field types and operators, but more the common items users will encounter and practical strategies to effectively manage them. The first thing to consider is to plan your desired outcome before you design the data structure in SharePoint. Too many people design complex structures and then struggle to cause Power Apps to do what they need. 

Back to top

Functions

Examples here that support Delegation Filter and Lookup (not to be confused with SharePoint Lookup fields), whereas GroupBy and With will only return records up to the Delegation limit (these are called “hidden” Delegation limitations.

Field Types

As a brief background explanation, SharePoint has been around for a lot longer than Power Apps and some field types were designed to interact with SharePoint views and forms as well as InfoPath forms. Almost all of this interaction can now be done in Power Apps (including Integrated Forms), so all that you want is somewhere to store the data to read from and write to. In the vast majority of cases, you will be able to store everything you need in three field types

• Single Line of Text
• Number
• Date and Time

Some problematic field types: –

• Lookup fields – you almost always do not need them with this structure as the same information can be managed in Power Apps and I would highly discourage their use. They will cause you a lot if issues with everything from Filtering and Sorting to particularly writing the data back to the list.
• Choice fields – to a lesser extent, although not as problematic in writing to, are not necessary in SharePoint with a Power Apps Interface unless you want multi-choice options. They are also only Delegable with equals (=) but not using StartWith and you cannot Sort by them in a Delegable manner.
• Boolean fields (Yes/No) – for the purposes of this discussion, regard them as non-delegable (they work on their own, but not in conjunction with other Filters).
• You will often need to use Multiple Lines of Text – just restrict them to instances you believe the content will exceed 255 characters. Queries on these are not Delegable.
• Person or Group fields are a complex type field are can be problematic writing back to. If you only want to store a user’s name or email address, consider doing it in a Single Line of Text field and user the Office365Users connector in Power Apps to retrieve the rest of the information.
• Calculated columns are highly problematic and are not really compatible with Power Apps. You can easily do the same calculation in Power Apps.
• Image columns have improved recently and are a viable way of storing these, however hold only one image per field.
• There are much better ways of storing images than the Hyperlink/Picture column. I have a blog on this subject.
• The External Data and Managed Metadata types are historical SharePoint columns and not something Power Apps has been designed for. You can generally easily perform the same functions with Power Apps.
  

A more detailed discussion on this is in my blog on Constructing your First App.

Back to top

Operators

In the Operator space – non-Delegable includes: –

• Search and in
• Not() 
• IsBlank

Even if you have less than 2,000 records, you will still get a Delegation warning as there is nothing to stop the adding of more records afterwards. So how do we make this all work?

Back to top

Collections

The first thing to consider is Collections. Up to 2,000 records can be easily collected out of a data source and then all filters will work and (most importantly) you will not see any Delegation warnings in your code. If you have less than 2,000 records, then this is easy, but there are many other ways of managing this.

Back to top

Newest Records in List
Firstly, if the newest 2,000 records will do the job

ClearCollect(
   colMyCollection,
   Sort(
      MyList,
      ID,
      Descending
   )
)

Back to top

Double Size
If you have between 2,000 and 4,000 records, you can do this

With(
   {
      _Low: 
      Sort(
         SPList,
         ID
      ),
      _High: 
      Sort(
         SPList,
         ID,
         Descending
      )
   },
   ClearCollect(
      colMyCollection,
      _Low,
      Filter(
         _High,
         !(ID in _Low.ID)
      )
   )
)

Back to top

Bigger than 4,000

Unique Numeric Identifier

If you want a bigger collection, the solution is a bit more complex. This is necessary as the SharePoint ID field is only partially Delegable and is restricted to the equals = operator. Any queries using greater than or less than, including combined with equals (<, >, <=, >=) are not Delegable, however other Numeric queries are, so if a “shadow” ID numeric field is maintained with the same value as the ID in the item, you can query on this. 

There are several ways of populating this and it only needs to be done once when the record is created. Using the name IDFilter below, you could do this on the OnSuccess of a new record form. 

Patch(
   MyList,
   {ID:MyNewForm.LastSubmit.ID},
   {IDFilter: MyNewForm.LastSubmit.ID}
)

If you are using Patch

Set(
   vLastID
   Patch(
      MyList,
      Defaults(MyList),
      {....Your Patch code}
   ).ID
);
Patch(
   MyList,
   {ID:vLastID},
   {IDFilter: vLastID}

)

Another way is a simple Flow as below – this also covers the possibility of a user adding a record directly in SharePoint.

NOTE: for existing lists you will need to “back-populate” this, but this can be done quickly in SharePoint “Quick Edit” views. 
Once you have this value in all your records you can collect as many records as you need, but note that larger collections will take a little time to run.

Back to top

Newest xxxx records

There are two methods here – the first still may suit as it “counts backwards” from the newest record and collects in batches of 2,000, so you do not need to collect the whole list if the newest 10,000 will do the job

ClearCollect(
   colMyColl,
   Sort(
      MyList,
      IDFilter,
      Descending
   )
);
If(
   CountRows(colMyColl) = 2000,
   Set(
      gblID,
      Min(
         colMyColl,
         IDFilter
      )
   );
   Collect(
      colMyColl,
      Sort(
         Filter(
            MyList,
            IDFilter < gblID
         ),
         IDFilter,
         Descending
      )
   )
);
If(
   CountRows(colMyColl) = 4000,
   Set(
      gblID,
      Min(
         colMyColl,
         IDFilter
      )
   );
   Collect(
      colMyColl,
      Sort(
         Filter(
            MyList,
            IDFilter < gblID
         ),
         IDFilter,
         Descending
      )
   )
)

Then keep going in batches of 2,000 as required

Back to top

A field criteria where each option is less then 2,000 items

You may have a field like Status where each group is less than 2,000 items. As an example, your status may be either Pending, In Progress, Processed or Finalised, each of which will always be less than 2,000 items. You can then do a collection as follows

With(
   {
      _Pending: 
      Filter(
         SPList,
         Status = "Pending"
      ),
      _Progress: 
      Filter(
         SPList,
         Status = "In Progress"
      ),
      _Processed: 
      Filter(
         SPList,
         Status = "Processed"
      ),
      _Finalised: 
      Filter(
         SPList,
         Status = "Finalised"
      )
   },
   ClearCollect(
      colMyCol,
      _Pending,
      _Progress,
      _wProcessed,
      _wFinalised
   )
)

Collect all records in the List

The second involves extensive use of the With() statement and collects all the records in the list. This direct link to the article will show the code required, but it is suggested you read the entire blog to properly understand it.

Back to top

Delegable Filter

There are however other strategies, in particular you can create a collection of less than 2,000 records using a Delegable filter.

An example is a (Text) column you might call Status, which is updated in Power Apps based on other input. For instance, if the values were “Pending”, Planned”, “In Progress”, Completed” and “Finalised” and you needed to examine only those records that were “Planned” or “In Progress” (and they totalled less than 2,000) and you did this

ClearCollect(
   colMyCollection,
   Filter(
      MyList,
      Status = "Planned" ||
      Status = "In Progress"
   )
)

then you could apply any query to this collection. I am sure there are many other filters that you can use in your model with either text or numeric fields that will do this for you.

Using With() statement

I have a separate blog on this link dealing with this subject.

Back to top

Other Collection uses – Drop-Down or Combo Boxes

You can also use Collections for your drop-down Items and this can be done frequently at App OnStart, but for many that would have been Choices columns, simply hard-code them. Writing them back to a Text field is then easy by simply setting the Update of the Data Card referring to the output of the control as YourControlName.Selected.xxxx where xxxx depends on the Items property of the control.

When you type YourControlName.Selected. (note second dot), valid values will come up underneath. For some guidance of what they mean: 

• If your Items are Choices hard coded as above (or a Choices field if you have retained one) – the reference will be YourControlName.Selected.Value
• If they are Distinct( . . .  .), it will be YourControlName.Selected.Result
• The other option is when they are based on a field value in a list (other than Distinct), then it will be YourControlName.Selected.FieldName (actual field name) and this will be common in Collections.

Back to top

Data Field Types

Planning your data structure to allow for Delegable queries if possible is a better and cleaner outcome. There are two main field types that are fully Delegable – Text and Numeric. If you have data in their fields you want to query on (and the operator is Delegable), then this will work for you on any number of records.

Back to top

Boolean Fields
With Boolean (Yes/No) fields – you can do the following instead: –

• Use plain text fields with the desired content of “Yes” or “No”.
• Set the field Default in SharePoint to “No”.
• Use Checkboxes in Power Apps with the following settings: –

Default

Parent.Default = "Yes"

or

ThisItem.FieldName = "Yes"

DataCard Update

If(
   CheckBoxName.Value,
   "Yes",
   "No"
) 

Back to top

Complex Field Types

In particular, Lookup and Person fields can be done in Power Apps using LookUp or Microsoft365Users and can be written back to Text fields, so they are not necessary in your data source with the model suggested.

Back to top

Operators

Search & in
These are non-delegable. One common alternative is StartsWith (which is Delegable), so if what you are looking for is at the start of the string, the list will start filtering as soon as something is typed in the search box. Collections are probably the best work-around if you genuinely need to find string content anywhere within another string or another set of fields.

One other small “trick” with StartsWith is to make the Default of the text box where the user enters the text “” (empty String), so if this was your Filter

Filter(
   MyList,
   StartsWith(
      MyFieldName,
      MyTextBox.text
   )
)

Not()the full list would display when the text box was empty, and records would start to filter as soon as the user started typing in the box. 

If you must do a Search or In Filter looking for strings inside text or fields values in a list, then consider the Collection alternative.

This is also non-Delegable in any form including <> (does not equal) and the abbreviation !. 

What to concentrate on here is what is (rather than what is not). In the status example above, if this was done

ClearCollect(
   colMyCollection,
   Filter(
      MyList
      Status <>“Pending”

   )
)

it would not be Delegable, but listing what is equal including with Or() || or And() &&  statements as in the example further above is Delegable

Back to top

IsBlank
This is not Delegable, but the workaround is strangely simple. Using this

Filter(
   MyList,
   IsBlank(MyFieldName)
)

is not Delegable however 

Filter(
   MyList,
   MyFieldName = Blank()
)

is Delegable

Back to top

Other non-Delegable Operators / Functions

Not discussed here, but other commonly-used non-delegable operators include: –

• Last, FirstN, LastN
• CountIf, CountRows
• RemoveIf, UpdateIf,
• GroupBy, UnGroup

However, note here that some of these are limitations (“hidden” limits) and if you do not receive a Delegation warning – for instance

UpdateIf(
   Filter(
      ListName,
      Field = Criteria
   ),
   {FieldName: NewValue}
)

If the Filter above is Delegable, then it will update record numbers up to your Delegation limit. RemoveIf() is the same and GroupBy() will group the first records up to this limit.

Back to top

Summary

In conclusion, I will summarise my thoughts on how to consider approaching your Power Apps /SharePoint journey: –

• Plan your data structure with the desired result in mind before you commit to the design and in particular before you enter data.
• If possible, keep to Text, Numeric or Date & Time fields wherever this is practical.
• Use Collections not only for Delegation issues, but also for performance, particularly on mobile devices.
• Decide very early as to user access to SharePoint. You can still edit or create new records with the SPI Form and produce any views as long as they are not data sheets. Also the Export to Excel is highly useful for reporting.

As mentioned, this is a large subject and I have only touched on the more common issues, but I hope it has helped to gain a better understanding of the matter. Enjoy the Power of Power Apps.