Do you find that your queries in filters become complicated? In this post a way to simplify your queries using Dataverse views.
Simplifying queries
Table of Contents
When you develop more complicated apps and you filter data coming from your data source, then you might find that those queries become very long.
In this post I will build up my queries on my fruit table. This table keeps track of my fruit and the colours of each fruit. As shown in the app below, I have 4 fruits lists in s gallery. All I need to do is reference my table (yes Dataverse adds an ‘s’ to the singular fruit).
Now, if i wanted to get all the records added today and that don’t have a colour field set then I could create a query like this
filter(Fruits, 'Created On' > DateAdd(Today(), -1) && IsBlank(Colour)Ok, in this case my query isn’t too complicated. But with a bit of imagination the query could become complicated without much effort.
Using Dataverse Views
One way to simplify the expression is by using a view that I created in Dataverse, called Today’s Fruit. I set the filter ion the view like this:
And now i can use this view in my filter.
Filter(Fruits, 'Fruits (Views)'.'Today''s fruit, Colour = Blank())
And of course you could also create a view that includes the colour filter, making the filter query in the app really simple.
Considerations
There are however a few things that should be considered. Should we replace every query in a filter with a view? Well, you could try, but there are a few limitation that you might hit.
For example there isn’t a way to pass parameters from the app to a view. And creating a separate view for each possible option might also not be something that you want to do, however if you have a table that keeps track of a status, or a limited number of categories or anything that your would filter on on a regular basis, such as the logged in user then you could consider using Dataverse views.
Sometimes using views may help with delegation issues, and sort out delegation warnings, however the simplification of complicated filter expressions could already be worth it. . Adding the complexity to the database rather than the end user app.
In this post I have only looked at filtering items, but this same method can also be used to sort the items that are returned by a query.
It seems the only “date/time” columns that can be used in filters are “created” and “modified”. I have a table with two date/time columns (CheckOutFrom and CheckOutTo), but when I add a filter, they don’t appear in the drop-down. This is a table to reservations, so I’d love to create basic views of “upcoming”, “ongoing” and “past” for use in a canvas app, but all would need to filter using comparisons to those two columns.
In your view have you included these two fields as displayed within the view?
The most important part of this is showing the odd syntax: Filter(myTable, ‘myTable (Views)’. viewname, …….