Delegable functions

 Delegable functions

Reference

The next step is to use only those formulas that can be delegated. Included here are the formula elements that could be delegated. However, every data source is different, and not all of them support all of these elements. Check for delegation warnings in your particular formula.

These lists will change over time. We're working to support more functions and operators with delegation.

Filter functions

Filter, Search, and LookUp can be delegated.

Within the Filter and LookUp functions, you can use these with columns of the table to select the appropriate records:

  • And (including &&), Or (including ||), Not (including !)
  • In

     Note

    In is only delegated for columns on the base data source. For instance, if the data source is Accounts table then Filter(Accounts, Name in ["name1", "name2"]) delegates to the data source for evaluation. However, Filter(Accounts, PrimaryContact.Fullname in ["name1", "name2"]) does not delegate since Fullname column is on a different table (PrimaryContact) than Accounts. The expression is evaluated locally.

  • =, <>, >=, <=, >, <
  • +, -
  • TrimEnds
  • IsBlank
  • StartsWith, EndsWith
  • Constant values that are the same across all records, such as control properties and global and context variables.

You can also use portions of your formula that evaluate to a constant value for all records. For example, Left( Language(), 2 ), Date( 2019, 3, 31 ), and Today() don't depend on any columns of the record and, therefore, return the same value for all records. These values can be sent to the data source as a constant and won't block delegation.

The previous list doesn't include these notable items:

Sorting functions

Sort and SortByColumns can be delegated.

In Sort, the formula can only be the name of a single column and can't include other operators or functions.

Aggregate functions

Sum, Average, Min, and Max can be delegated. Only a limited number of data sources support this delegation at this time; check the delegation list for details.

 Note

If an expression is not delegated, it'll only work on the first 500 records (configurable up to 2000, see Changing the limit) retrieved from the data source rather than delegating the processing of all data at the data source.

Counting functions such as CountRows, CountA, and Count can't be delegated.

Other aggregate functions such as StdevP and VarP can't be delegated.

Table shaping functions

AddColumns, DropColumns, RenameColumns, and ShowColumns partially support delegation. Formulas in their arguments can be delegated. However, the output of these functions are subject to the non-delegation record limit.

As in this example, makers often use AddColumns and LookUp to merge information from one table into another, commonly referred to as a Join in database parlance:

Power Apps
AddColumns( Products, 
    "Supplier Name", 
    LookUp( Suppliers, Suppliers.ID = Product.SupplierID ).Name 
)

Even though Products and Suppliers may be delegable data sources and LookUp is a delegable function, the output of the AddColumns function isn't delegable. The result of the entire formula is limited to the first portion of the Products data source. Because the LookUp function and its data source are delegable, a match for Suppliers can be found anywhere in the data source, even if it's large.

If you use AddColumns in this manner, LookUp must make separate calls to the data source for each of those first records in Products, which causes a lot of network chatter. If Suppliers is small enough and doesn't change often, you could call the Collect function in OnStart to cache the data source in your app when it starts. As an alternative, you could restructure your app so that you pull in the related records only when the user asks for them.

Non-delegable functions

All other functions don't support delegation, including these notable functions:

Non-delegable limits

Formulas that can't be delegated will be processed locally. This allows for the full breadth of the Power Apps formula language to be used. But at a price: all the data must be brought to the device first, which could involve retrieving a large amount of data over the network. That can take time, giving the impression that your app is slow or possibly crashed.

To avoid this, Power Apps imposes a limit on the amount of data that can be processed locally: 500 records by default. We chose this number so that you would still have complete access to small data sets and you would be able to refine your use of large data sets by seeing partial results.

Obviously care must be taken when using this facility because it can confuse users. For example, consider a Filter function with a selection formula that can't be delegated, over a data source that contains a million records. Because the filtering is done locally, only the first 500 records are scanned. If the desired record is record 501 or 500,001, it isn't considered or returned by Filter.

Aggregate functions can also cause confusion. Take Average over a column of that same million-record data source. Average can't be delegated in this case since the expression isn't delegated (see the earlier note), so only the first 500 records are averaged. If you're not careful, a partial answer could be misconstrued as a complete answer by a user of your app.

Changing the limit

500 is the default number of records, but you can change this number for an entire app:

  1. Select Settings.
  2. Under General, change the Data row limit setting from 1 to 2000.

In some cases, you'll know that 2,000 (or 1,000 or 1,500) will satisfy the needs of your scenario. With care, you can increase this number to fit your scenario. As you increase this number, your app's performance may degrade, especially for wide tables with lots of columns. Still, the best answer is to delegate as much as you can.

To ensure that your app can scale to large data sets, reduce this setting down to 1. Anything that can't be delegated returns a single record, which should be easy to detect when testing your app. This can help avoid surprises when trying to take a proof-of-concept app to production.

Delegation warnings

To make it easier to know what is and isn't being delegated, Power Apps provides warning (yellow triangle) when you create a formula that contains something that can't be delegated.

Delegation warnings appear only on formulas that operate on delegable data sources. If you don't see a warning and you believe your formula isn't being properly delegated, check the type of data source against the list of delegable data sources earlier in this topic.

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)