With() Statement managing Delegation
Content
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().
So how does this work?
With() can contain a single Value, a Record or a Table equally well. In this case, it is filtering a Table with a smaller Table being the output. If you look at the syntax, it is almost identical to UpdateContext().
Pre-filtering
As an example, if my list was 10,000 records, but if after Filtering to show only the Status values Planned and In Progress, the result was always less than 2,000 records, I could then use Search (a non-Delegable operator) to look for the content of Text input SearchBox.Text in both the fields FirstName andLastName. This would work perfectly, and no Delegation warning would be received.
With(
{
_Status:
Filter(
MyList,
Status = "Planned" ||
Status = "In Progress"
)
},
Search(
_Status,
SearchBox.Text,
"FirstName",
"LastName"
)
)Looking at some other non-Delegable filters, if I wanted a Count of the items with Status of In Progress
With(
{
_Status:
Filter(
MyList,
Status = "In Progress"
)
},
CountRows(_Status)
)And if I was looking for the Sum of Revenue of items with Status of Planned
With(
{
_Status:
Filter(
MyList,
Status = "Planned"
)
},
Sum(_Status,Revenue)
)Obviously, the same thing can be done with a Collection then the Filter
ClearCollect(
colStatus:
Filter(
MyList,
Status = "Planned"
)
)
Then the filter
Sum(colStatus,Revenue)But this requires a trigger when the Collection is to be done. Also I have found the With() statement resolves the data quicker than doing a Collection.
Also as mentioned, you can use this on smaller lists to remove the Delegation warning.
If my list has 1,200 records and I wanted the Sum of Expenses without a warning.
With(
{_List: MyList},
Filter(
_List,
IsBlank(MyFieldName)
)
)Bigger Collections
Expanding this further, using the “4,000 item” solution from my general Delegation blog, you can have a collection on a list up to 4,000 items that can be used with any filters.
With(
{
_Asc,
Sort(
MyList,
ID
),
_Desc,
Sort(
MyList,
ID,
SortOrder.Descending
)
},
ClearCollect(
colMyCollection,
_Asc,
Filter(
_Desc,
!(ID in _Asc.ID)
)
)
)Let’s take this a step further – what if I have 3,500 items of the Status “Planned” as above and I want them in a Collection for further filtering? The below will work with any size list and will extract up to 4,000 matching records.
Clear(colMyCollection);
With(
{
_Desc:
Filter(
Sort(
MyList,
ID,
SortOrder.Descending
),
Status = "Planned"
),
_Asc:
Filter(
Sort(
MyList,
ID
),
Status = "Planned"
)
},
Collect(
colMyCollection,
_Asc,
Filter(
_Desc,
!(ID in _Asc.ID)
)
)
)Collect all records in list
This goes the whole way (also with thanks to my colleague @Eelman for the inspiration on the concept) and can take some time to run. Also be aware of potential device (particularly mobile) limitations if the list is over about 10,000 items. A essential element in this model is that it must be run on a unique numeric identifier other than the ID (which is not Delegable for this purpose). Creating a “Shadow ID” field is generally the best way and the process is described in the Delegation discussion on this site.
There are two values to replace in the code below – the list name (SPListName) and the numeric field (SeqNoField) mentioned above. It will create a collection called colAllList (call it whatever you want).
Clear(colAllList);
With(
{
_Sets:
With(
{
_Limits:
With(
{
_Limit:
Sort(
SPListName,
SeqNoField,
SortOrder.Descending
)
},
RoundDown(
First(_Limit).SeqNoField / 2000,
0
) + 1
)
},
AddColumns(
RenameColumns(
Sequence(
_Limits,
0,
2000
),
Value,
LowID
),
HighID,
LowID + 2000
)
)
},
ForAll(
_Sets As _MaxMin,
Collect(
colAllList,
Filter(
SPListName,
SeqNoField > _MaxMin.LowID && SeqNoField <= _MaxMin.HighID
)
)
)
)Relational LookUps
Another scenario is the fact that SharePoint is not a “relational” database, meaning that Lookups to another List are not Delegable, even if the data type and operator are both Delegable. It is common to use small (under your Delegation limit in number) “reference lists” and you might want to include a field from one of these in a Filter. The With() statement is very useful here. In the below
- The main list name is Projects
- You are wanting to add the Supervisor‘s email
- Reference list is Staff where the person’s name is StaffName
- The Email is in EmailAddress
With(
{_Staff:Staff},
AddColumns(
Projects,
SuperEmail,
LookUp(
_Staff,
StaffName = Supervisor
).EmailAddress
)
)
This type of query is fully delegable providing your key field match is either Text or Numeric.
I hope this information has been useful. Happy Power Apping!!
34 Comments
Carl Williams
Hi Warren. Is this With statement the same as using While?
I have a flow that appends a parent record picture url with a chosen child picture. I do this by way of a yes/no toggle. So if I choose yes for one pic then that url is copied to the parent hyperlink image field as the “Item Image” type of thing. BUT, only one child photo related to the parent should obviously have it’s toggle set to true. The url would be overwritten if there was already one set but when this new one is chosen I was going to use a
While(),
With(), or
For Each() method to set any existing ‘trues’ for related to the parent item to false.
Not everyone can be a true. Only one 🙂
Cheers
CW
Warren Belz
Hi Carl,
No – With() is a Power Apps (not Flow) function and sets a (very) temporary Variable (it can be a Value, Record or Table) that is then available for reference only during the execution of that piece of code. It is a similar function in a lot of cases to using Set, UpdateContext or ClearCollect prior to the code in question, except it is far more flexible in how and where it is used.
In Power Apps, ForAll() is the “looping” function (in Flow you would use Apply to each), although it is not as powerful as the VB While/Wend or Do Until/Loop.
Isaac
This function sounds awfully familiar……………CTE! Very cool. Great article thank you for posting.
richard c
For me, this is a difficult concept to latch on to. Your responses to the Power App community are invaluable. Keep up the great work.
JRN
If the ID starts with 10000 instead of ID: 1 because the first 10k items were deleted, would setting the following work?
Clear(colAllList);
With(
{
wSets:
With(
{
wLimits:
With(
{
wLimit:
Sort(
SPListName,
SeqNoField,
Descending
)
},
RoundDown(
(First(wLimit).SeqNoField-10000) / 2000,
0
) + 1
)
},
AddColumns(
RenameColumns(
Sequence(
wLimits,
0,
2000
),
“Value”,
“LowID”
),
“HighID”,
LowID + 2000
)
)
},
ForAll(
wSets As MaxMin,
Collect(
colAllList,
Filter(
SPListName,
SeqNoField > (MaxMin.LowID-10000) && SeqNoField <= (MaxMin.HighID-10000)
)
)
)
)
Warren Belz
You do not need to do that – just use the code the way it is and it will do some empty loops until it gets to your numbers
JRN
I was trying to speed it up since the initial first 40k items were deleted so it looping through the first 20 sets with no results was taking some time before getting to actual data.
Guy Shepperd
Warren,
Thank you for this Blog! thsi concept it very hard to get a grip on, and you have broke it down very nicely.
I have tried to modify your code, but ran into some issues.
i put the question out on the community. (https://powerusers.microsoft.com/t5/Building-Power-Apps/Large-Table-in-Dataverse-Filtering-down-to-a-collection/td-p/1470199)
any help is greatly apprecaited, and if there is a suggestion on how to change the schema to better facilitate the forming of the collection, i am open to it.
Thanks
Guy
Warren Belz
Hi Guy
I have responded on the forum – in my Delegation blog are more processes for collections including a process for getting the newest nnn records.
Stuart Moore
Hi Warren, this is fantastic, is there a way of also doing a Add Columns from another large list. I have 2 lists both have the same number of rows with a 1-1 implied relationship, ultimately we will be moving our data into a Dataverse or equivalent location, but for now we are using SharePoint lists, my main list has all the key fields, the second list has the project delivery area and sub area and a field that is the same value in both lists. lists are approx 2,500 rows currently. I would like to produce a gallery or data table tat effectively combines the data (much in the same way a sql query would be used to create a view)
Warren Belz
Hi Stuart,
I am not entirely clear on your question, but AddColumns is also a “hidden” Delegation limitation (as is With). You would need to pre-filter the data so your resulting output to add the columns in under your Delegation limit.
Gus Chessman
Hi Warren,
Fantastic content in your site.
I have a question in the “4000 items” solution.
As ‘MyList’ is very wide, will the solution works as expected if rather than ‘MyList’ I use a ShowColum function within ‘MyList’?
Warren Belz
Hi Gus,
No reason it would not – ShowColumns is not Delegable, but neither is Collect.
Gus Chessman
I figured out a way which responds at ‘warp speed’ to my wide list, using filter + ShowColumns:
With(
{
wAsc:
Sort(MyList;ID);
wDesc:
Sort(MyList;ID;Descending)
}
;
ClearCollect(
MyCollection;
ShowColumns(
wAsc;
“Colum1″;”Colum2″;”Colum3”
);
Filter(
ShowColumns(
wDesc;
“Colum1″;”Colum2″;”Colum3”
);
!(ID in wAsc.ID)
)
)
)
Warren Belz
Yes – ShowColumns() will make it faster – the amount will depend on the number of fields in the list. Note that ShowColumns is also a “hidden” Delegation limitation (the output numbers are restricted to your Delegation Limit), however this is the same as the collection, so it does not matter.
pg
Hey Warren, is It possible to use the solution in the “Collect all records in list” section while restricting the number of columns? I tried changing the “SPlist” in your code with a “ShowColumns(SPlist)” but I get only the 2000 records (my delegation limit setting). My list has many columns so I’m trying to limit the number columns to make it faster. Thanks in advance for your time and for this blog post. It’s amazing.
Warren Belz
Hi PG,
ShowColumns is a “hidden” delegation limit and fill “cap” the records accessed by the filter (as you have discovered) – you can try this (I cannot test presently, but it should work
ForAll(wSets As MaxMin,
Collect(
colAllList,
With(
{
wData:
Filter(
SPListName,
SeqNoField > MaxMin.LowID && SeqNoField <= MaxMin.HighID ) }, ShowColumns( wData, "Column1", "Column2", . . . . ) ) ) )
Pg
Hey Warren, it works almost perfectly. All the columns are still getting fetched, but their data is not, only the columns that I specified have data. It’s definitely an improvement. I don’t know if it’s possible not to include the other columns in the same code, I’m doing a DropColumns after everything to get rid of them.
Thanks for your prompt response. Because of it, I also discovered a few other bugs that neeed ironing out.
PG
“…I’m doing a **another ShowColumns** after everything to get rid of them.”
Daniel
Works great!
Thanks!
Anthony C
Hi Warren,
I have a SP List with over 700,000 records (I know, it’s huge – not my list, I’ve just been asked to support someone else’s app) and I need to count the records in there where the ‘Created By’.Email is a specific person and the Created Month is the current month and the previous month’s record count too.
Filtering for the Created By.Email is working fine, but as soon as I add the Month(Created)=Month(Today()), it won’t retrun any records.
Is it possible to use With’s functionality to be able to get past the delegation issue for the Month portion?
Warren Belz
Hi Anthony,
The With() statement is unfortunately not a “magic bullet”, but more a tool to assist with (in the Delegation concept) pre-filtering with Delegable filters so non-delegable functions can then be done locally. Your issue is that the Month() function is not Delegable. If however you wanted to do this a year at a time and have a Text ox to put the year in (I have called it txtYear below)
With(
{
_Start:
Date(
Value(txtYear.Text),
Month(Now()),
1
),
_End:
DateAdd(
Date(
Value(txtYear.Text),
Month(Now()) + 1,
1
),
-1,
TimeUnit.Days
)
},
Filter(
YourBigList,
'Created By'.Email = User().Email &&
Created >= _Start &&
Created <= _End ) )
this will look in the current month for the year specified.
SUBHU_123
Hi Warren,
We are using PowerApps for some kind of approval operation of data. That’s why we are daily loading and deleting more than 4k data in SharePoint list. So my issue is because of daily loading and deleting The new ID start from 1 million or similar huge number like that, So is your collection solution will apply in this scenario.
Warren Belz
Assuming you have created the “Shadow ID” field on all records, then yes it will work.
Lena
Hi Warren!
Highly appreciate this solution, thanks for sharing it
I used it in my apps with SP lists with up to 4500 items and it worked perfectly
Now I’m building and app based on SP list with more than 5000 items, and unfortunately it doesn’t work – collection is empty
If I remove nr of items to 5000 it works perfectly
Can you reccomend something to make it work with SP lists with 5000+ items?
Thank you in advance for your reply
Warren Belz
Hi Lena,
When a SharePoint list gets over 5,000 items, you need to index any columns (in SharePoint) that are used in queries (you are allowed 20 per list)
LENA
Warren, thank you very much! It worked!
Sabrina
I tried the solution under Collect all records in list, but I am getting an error on the following line:
Sort(
SPListName,
IDFilter,
SortOrder.Descending
)
saying the attempted operation is prohibited because it exceeds the list view threshold.
I am not sure how to solve this. my sharepoint list has 150k rows, and the ID filter is a number column with the same values as the ID.
Warren Belz
You need to Index the IDFilter column in SharePoint when record numbers get over 5k, but if you are trying to collect 150k records, I doubt that will even run in Power Apps – I have found anything over 10k is basically unusable from a performance / response point.
Kash
Hey Warren,
Thank you for this great code, I would love to get some help – as I am trying to do this for a dataverse table and unable to get past the RenameColumns step you have.
It says Expected Identifier name in the location of Value and HighID.
I have the column with only numbers in dataverse, but unable to get away from this error to test.
With(
{
_Sets:
With(
{
_Limits:
With(
{
_Limit:
Sort(
DataverseTable,
columnName,
SortOrder.Descending
)
},
RoundDown(
First(_Limit).columnName/ 2000,
0
) + 1
)
},
AddColumns(
RenameColumns(
Sequence(
_Limits,
0,
2000
),
“Value”,
“LowID”
),
“HighID”,
LowID + 2000
)
)
},
Any help would be appreciated..
Warren Belz
Hi Kash,
I am not a Dataverse user, however providing that column contains unique numbers it should work. Note that you no longer need quotes around AddColumns and RenameColumns fields.
Mitch
Hi Warren, thank you for this thorough explanation/testing of With(). I think this function might help me with a delegation problem I’m facing, but I’m not quite sure how, or if at all. I could use your technical expertise if you don’t mind.
I’ve got two Dataverse tables with a 1:N relationship (Table1 and TableN for reference). Power Apps makes it really easy to delegate: Filter( TableN, ThisRecord.Table1LookUp.Column = “text” ), where I filter TableN based on some value in Table1. But what I’m finding difficult to delegate is a filter on Table1 based on some value in TableN. Essentially, this is what I’m trying to do:
Filter(
Table1,
PrimaryKey in Filter(
TableN,
Color.Name = varColor.Name
).Table1PrimaryKey
)
Both filters and the in operator are fully delegable in Dataverse. If I managed to type out 5,000 values on the right side of the in operator, Dataverse could fully delegate the operation. However, as I’ve recently learned, PowerFX treats:
Filter(
TableN,
Color.Name = varColor.Name
).Table1PrimaryKey
as
ShowColumns(
Filter(
TableN,
Color.Name = varColor.Name
),
Table1PrimaryKey
)
making it non-delegable. Its important to me to get as many records out of this filter as possible. Do you think a With() function could help me with this?
Warren Belz
Hi Mitch,
Maybe
With({
_Data:
Filter(
TableN,
Color.Name = varColor.Name
)
},
Filter(
Table1,
PrimaryKey in _Data.Table1PrimaryKey
)
)
Mitch
That was more simple than I was expecting, thank you Warren. Unfortunately, it appears to have made that opertion less delegation efficient. Where it would have delegated:
Filter(
TableN,
Color.Name = varColor.Name
)
it now doesn’t delegate at all, making it worse than before. I know you warned of this not being a magic bullet, but I was hoping for at least a more effecient non-delegable opertion for this instance.
Thank you again for your help!