With() Statement to include non-Delegable filters

[WarrenBelz]

In this article, 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() and sets the variable in the same manner.

As an example, if your 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, you could then use Search (a non-Delegable operator) to look for the content of Text Input SearchBox.Text in both the fields FirstName and LastName. 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(
   {MyExpenses:MyList},
   Sum(MyExpenses,Expenses)
)

Expanding this further, you can have a collection on a list up to 4,000 items that can be used with any filters (my acknowledgement to my colleague Drrikryp on this one)

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" 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)
      )
   )
)

I hope this information has been useful and. Happy Power Apping!!