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:
I also tried:
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
Filter Using Dataverse Views
Now, instead of filtering using the IsBlank() function as previously, filter using the newly created View instead. Intellisence will help guide you
Voila! You have it. All the warnings and delegation issues have gone and the filter will work correctly no matter what the size of the dataset