Delegation

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().

Back to top

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(
   {
      MyStatus: 
      Filter(
         MyList,
         Status = "Planned" ||
         Status = "In Progress"
      )
   },
   Search(
      MyStatus,
      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(
   {
      MyStatus: 
      Filter(
         MyList,
         Status = "In Progress"
      )
   },
   CountRows(MyStatus)
)

And if I was looking for the Sum of Revenue of items with Status of Planned

With(
   {
      MyStatus: 
      Filter(
         MyList,
         Status = "Planned"
      )
   },
   Sum(MyStatus,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(
  {wList:MyList},
   Filter(
      wList,
      IsBlank(MyFieldName)
   )
)

Back to top

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(
   {
      wAsc,
      Sort(
         MyList,
         ID
      ),
      wDesc,
      Sort(
         MyList,
         ID,
         Descending
      )
   },
   ClearCollect(
      colMyCollection,
      wAsc,
      Filter(
         wDesc,
         !(ID in wAsc.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(
   {
      wDesc: 
      Filter(
         Sort(
            MyList,
            ID,
            Descending
         ),
         Status = "Planned"
      ),
      wAsc: 
      Filter(
         Sort(
            MyList,
            ID
         ),
         Status = "Planned"
      )
   },
   Collect(
      colMyCollection,
      wAsc,
      Filter(
         wDesc,
         !(ID in wAsc.ID)
      )
   )
)

Back to top

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(
   {
      wSets: 
      With(
         {
            wLimits: 
            With(
               {
                  wLimit: 
                  Sort(
                     SPListName,
                     SeqNoField,
                     Descending
                  )
               },
               RoundDown(
                  First(wLimit).SeqNoField / 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 && SeqNoField <= MaxMin.HighID
         )
      )
   )
)

Back to top

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(
   {wStaff:Staff},
   AddColumns(
      Projects,
      "SuperEmail",
      LookUp(
         wStaff,
         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!!

Back to top

3 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.

Leave a Reply

Your email address will not be published. Required fields are marked *