Power Apps Delegation SharePoint (Warrens Belz)

Prévia do material em texto

D E L E G A T I O N (complemento aos 10 mandamentos lista no Sharepoint) 
Power Apps Delegation – 
SharePoint 
16/01/2021 / 9 Comments 
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. 
https://www.practicalpowerapps.com/category/delegation/
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#comments
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Delegation
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#NDFieldTypes
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#NDOperators
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Collections
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Newest
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Double
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Bigger
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#NewestX
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Option
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#DFilter
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Other
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#With
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#DataTypes
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Boolean
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Complex
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#User
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Operators
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Operators
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#IsBlank
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#NonDelegable
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Summary
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 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
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: – 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
• 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. 
• There are much better ways of storing images than either 
the Hyperlink/Picture or Image columns (which are both read only 
anyway). 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 
https://www.practicalpowerapps.com/images/powerapps-sharepoint-images-storing-and-viewing/
https://www.practicalpowerapps.com/data/constructing-your-first-power-app-before-you-start/https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
 
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 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
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( 
 { 
 wLow: 
 Sort( 
 SPList, 
 ID 
 ), 
 wHigh: 
 Sort( 
 SPList, 
 ID, 
 Descending 
 ) 
 }, 
 ClearCollect( 
 colMyCollection, 
 wLow, 
 Filter( 
 wHigh, 
 !(ID in wLow.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( 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
 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( 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
 MyList, 
 IDFilter, 
 Descending 
 ) 
); 
If( 
 CountRows(colMyColl) = 2000, 
 Set( 
 vID, 
 Min( 
 colMyColl, 
 IDFilter 
 ) 
 ); 
 Collect( 
 colMyColl, 
 Sort( 
 Filter( 
 MyList, 
 IDFilter < vID 
 ), 
 IDFilter, 
 Descending 
 ) 
 ) 
); 
If( 
 CountRows(colMyColl) = 4000, 
 Set( 
 vID, 
 Min( 
 colMyColl, 
 IDFilter 
 ) 
 ); 
 Collect( 
 colMyColl, 
 Sort( 
 Filter( 
 MyList, 
 IDFilter < vID 
 ), 
 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( 
 { 
 wPending: 
 Filter( 
 SPList, 
 Status = "Pending" 
 ), 
 wProgress: 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
 Filter( 
 SPList, 
 Status = "In Progress" 
 ), 
 wProcessed: 
 Filter( 
 SPList, 
 Status = "Processed" 
 ), 
 wFinalised: 
 Filter( 
 SPList, 
 Status = "Finalised" 
 ) 
 }, 
 ClearCollect( 
 colMyCol, 
 wPending, 
 wProgress, 
 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 
https://www.practicalpowerapps.com/delegation/with-statement-managing-delegation/#All
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
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 
 
http://www.practicalpowerapps.com/delegation/with-statement-managing-delegation/
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
Planning your data structure to allow for Delegable queries if possible is a better 
and cleaner outcome. There are two main fieldtypes 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 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
 
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: 
– 
• First, 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 
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Content
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. 
 
DelegationPower Apps 
By Warren Belz 
https://www.practicalpowerapps.com/tag/delegation/
https://www.practicalpowerapps.com/tag/power-apps/
https://www.practicalpowerapps.com/author/admin/