Power Apps Delegation - SharePoint

Blog Index

Author: Warren Belz

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.

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 two fundamental parts to this – Field Types and Operators. For a query to be Delegable 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.

Blog Content

This is a large subject, and I have not included detail of all the field types and operators, but more the common ones 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. 

Field Types

Some problematic field types: -

• Lookup fields – you do not need them with this structure as the same information can be managed in Power Apps and I would not encourage their use. 
• Choice fields - to a lesser extent, although not as problematic in writing to, are not necessary in SharePoint with a Power Apps Interface.
• Boolean fields – for the purposes of this discussion, regard them as non-delegable.
• Date field queries are non-delegable.

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? 

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.

Newest Records in List

Firstly, if the newest 2,000 records will do the job

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

 and inset two labels with 

Double Size

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

ClearCollect(
  colA,
  Sort(
     MyList,
     ID
  )
);

ClearCollect(
   colB,
   Sort(
      MyList,
      ID,
      Descending
   )
);

ClearCollect(
   colMyCollection,
   colA,
   Filter(
      colB,
      !(ID in colA.ID)
   )
);

Clear(colA);
Clear(colB)

Bigger than 4,000

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 IDRef below, you could do this on the OnSuccess of a new record form. 

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

If you are using Patch

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

)

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.

ClearCollect(
   colMyColl,
   MyList
);

If(
   CountRows(colMyColl) = 2000,
   Set(
      vID,
      Max(colMyColl,IDRef)
   );
   Collect(
      colMyColl,
      Filter(
         MyList,
         IDRef>vID
      )
   )
);

If(
   CountRows(colMyColl) = 4000,
   Set(
      vID,
      Max(colMyColl,IDRef)
   );
   Collect(
      colMyColl,
      Filter(
         MyList,
         IDRef>vID
      )
   )
);

Then keep going in batches of 2000

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.

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.

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.

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

If(
   Parent.Default = "Yes",
   true,
   false
)

DataCard Update

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

Date Fields

These are a bit more complex and if you have absolutely no other work-arounds as mentioned above, one solution is to have a “Shadow” numeric field in the “yymmdd” format in your list. The value of this as related to a DatePicker control would be

Value(Text(DatePickerName.SelectedDate,”yymmdd”))

August 31, 2020 would result in the number 200831.
You would need to update this on the OnChange of the DatePicker and covert any date you are comparing it with, an example for the current day

UpdateContext({varToday: Value(Text(Today(),”yymmdd”))

So then a Filter

Filter(
   MyList,
   MyShadowDate >= varToday
)

would be a fully delegable query and able to be used on any data set size. 

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.

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

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

Other non-Delegable Operators

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

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

Power Apps SharePoint integration

This is a perceived barrier that really is not one. A lot of people envisage having to maintain two apps or go for the integration option and have to manage the limitations this brings. Any form created in a stand-alone app (SA) can be directly copied and pasted into a SharePoint Integration (SPI) screen doing the following: -

1. Open both apps on separate screens.
2. Connect any additional lists to the SPI app.
3. Paste in any OnStart code from the SA to the SPI.
4. Adjust the dimensions (particularly the width) of the SPI app to match the SA dimensions.
5. Rename the Form of the SPI to match the SA Form name. This automatically takes care of the built-in SPI functions  OnView, OnEdit, OnNew and OnSave.
6. Delete the SPI form.
7. Copy and paste the SA form into the SPI screen - some position adjustment may be necessary.
8. Change the Items of the SPI form now pasted to SharePointIntegration.Selected and it all should work.
9. If you update the SA form, repeat step 7

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 and Numeric 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.