cancel
Showing results for 
Search instead for 
Did you mean: 
WarrenBelz

With() Statement to include non-Delegable filters

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!!

Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • Jill of All Trades (Power Apps, SharePoint, PowerShell, Exchange, Power Automate and odds & ends of assorted other things)
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • I'm Tim Leung - a PowerApps builder. In addition to PowerApps, my main interests include building software with SQL Server and Microsoft .NET. I'm the author of 'Beginning PowerApps' - the first published book on PowerApps. You can buy this from Amazon and other leading bookstores.
  • Systems developer
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim