Filter using Dataverse Views

Filtering Dataverse tables is usually intuitive and fairly straightforward. Occasionally though, you get hit with a curved ball and what appears to be the obvious solution doesn’t work and you need an alternative – Dataverse Views!

The Importance of Delegation

I have a simple database of cascading tables shown above, with a one-to-many relationship between Region and County

Using a Canvas App, I tried to filter the County table by the Lookup column to Region to only including the records where there was an entry:

Filter(Counties,!IsBlank(County_Region)

I also tried:

Filter(Counties,!IsBlank(County_Region.RegionName)

Both return the correct results (at least for my smallish data set) but both also display the pesky blue lines and orange exclamation triangle that denotes a non-delegable formula. Grrrrr!

Delegation is where Power Apps successfully passes the formula to the data source (in this case Dataverse) to process on it’s behalf. This is important for two reasons

Firstly, its much quicker to get the results as the formula is sent to Dataverse to execute and the results are retuned to Power Apps. A non-delegable formula requires all the candidate records to be transferred from the data source to Power Apps to execute the formula. Clearly, just based on the time to transfer data, a non-delegable formula will take longer to display the results. Additional, it can take longer to execute when the formula is processed locally on a relatively low spec device, such as a mobile phone, rather than on a cloud server

Secondly, by default, the data source only returns the first 500 records. If my dataset has more than 500 Counties, then only the first 500 are sent to Power Apps. These are then processed to identify those that have an associated Region. Because an incomplete data set is sent to Power Apps, my results are likely to be incomplete too. The default delegation limit of 500 can be increased to 2000 in the app’s settings. But this larger dataset will take even longer to send to Power Apps and on a very large data sets may still give incomplete results

For these reasons it’s important to avoid delegation issues if at all possible. With Power Apps there are often several ways to achieve the same result and it’s usually possible to find an alternative formula that works successfully without delegation issues

The Power Apps Delegation reference guide is useful but often finding a way to avoid delegation issues is more of an art than a science and it becomes easier with experience. Reading the Microsoft documentation, using the IsBlank() function inside Filter should be delegable, and it usually is, but not tin this case!

Try as I did, I could find not a way to make those blue lines disappear until I hit on the idea of using a Dataverse View

Create a Dataverse View

A Dataverse View is a specific view of a table’s data. It is built into Dataverse so has full delegation built in. There are standard views that come with Dataverse out of the box, or a bespoke view can be created. I want to create a Dataverse View that simply filters out all of the records in the County table that are not assigned to the Region table

To create such a View, select the table in which you want to create a view, select the ‘View’ tab and then click ‘Add View’

Give your new view a name (I’ve called mine NewView) and select Create

Add a filter by selecting ‘Edit filters’. Then select the Lookup column (in my example County_Region) and then the method of filtering. I want to only include those records that have data in the County_Region Lookup colum. Select OK

Dataverse Views have the advantage that straightaway you can see the results of your filter on screen, to confirm it is working correctly