Delegation enhancements for lookups, Today/Now, and AddColumns

I’m happy to announce we’ve made another batch of delegation enhancements for Canvas apps.

Delegation refers to an app’s ability to “delegate” work to the data source instead of doing it locally, resulting in more scalable and higher performance apps.  In short, Delegation = Good.  For more details see Understand delegation in a canvas app.

We are far from done with delegation.  If you have suggestions on which queries and operations we should improve next please leave a comment or use the community forum.

Filter on lookup non-primary key fields

This one is specific to the Common Data Service.  Until now we have only supported filtering an entity based on the primary key of a Many-to-One lookup.  We can now filter based on other fields in the related entity, dramatically expanding the possibilities when working with lookups.

For example, consider the following screen, built on top of the standard Accounts and Contacts entities:

Note that there are no blue wavy lines or warning symbols – this formula is fully delagable.  Let’s type a value in the text input box to perform our filtering:

If we watch the network traffic from our app, and URL decode the query, we see:

$filter=(primarycontactid/address1_city eq 'Issaquah')&$expand=primarycontactid

This is OData speak for the formula we wrote with “primarycontactid” and “address1_city” being the logical names for our entity and column references in the formula.  The IsBlank test does not appear here because it does not depend on data in the records being filtered and was handled before talking with the data source.

We can also delegate the StartsWith and EndsWith functions for our search:

But we can’t yet delegate the in operator, it is on our to-do list.  Although definitely not the same, StartsWith can be used for many scenarios until we can delegate in.

Because these Filter calls can now be delegated, we can now search through millions of records and return the first page of results quickly with the ability to go back for more as the user scrolls through them in the gallery.

Today and Now

In the lookup filter example above, the IsBlank test didn’t need to be delegated because it could be evaluated locally before going to the data source.  We will be doing more of these optimizations over time.

Case in point: we just added the Today and Now functions to the list of things we can do locally.  For example, this filter of the Accounts entity:

expands the Today function call into a constant to send to the data source:

$filter=createdon ge 2019-04-01T16:16:03.013Z

Similarly, we already supported delegation for the Date function:

But, we don’t yet support constant folding with the DateAdd function:

We’re on it.

AddColumns

Lots of people use the AddColumns function to bring in data from across data source, effectively performing heterogeneous joins.

For example, imagine that you have a list of real estate agents in SharePoint and a table of home listings in SQL Server that contains tens of thousands of rows. You could combine this information with AddColumns:

AddColumns( RealEstateAgents, "Listings",  Filter(  '[dbo].[AllListings]', ListingAgentName = AgentName ) )

Until now, AddColumns would not delegate the Filter call to SQL Server in the third argument and thus only the first 500 listings would have been searched.  No longer, we can now delegate all the arguments of the AddColumns function.

But we aren’t done here yet. What we haven’t done is make the outside of the AddColumns delegable and pageable, the output is still limited by the non-delegation limit.  If for example you attach the result to a Gallery control through its Items property, it will be limited to 500 records.  Something for our next installment.

A word of caution on using AddColumns in this manner.  Each Filter call above is another round trip to the data source.   If performance is an issue, check the network activity in your browser to understand what is happening behind the scenes.  If the number of related rows is small enough, you may want to bring them into a collection and do the filtering locally.  You may also want to restructure your app to only grab the related records when a user specifically asks for them.   We can and will make this better in time, for example we could batch these calls for multiple rows to make this more efficient.