Using Dataverse views to simplify filter queries in Canvas Apps in Power Apps Microsoft Power Apps istockphoto 1255090147 612x612 1

Using Dataverse views to simplify filter queries in Canvas Apps in Power Apps

Do you find that your queries in filters become complicated? In this post a way to simplify your queries using Dataverse views.

Simplifying queries

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).

Using Dataverse views to simplify filter queries in Canvas Apps in Power Apps Microsoft Power Apps Show all items in the Fruit table

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 to simplify filter queries in Canvas Apps in Power Apps Microsoft Power Apps Filter on items created today and without the colour set

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:

Using Dataverse views to simplify filter queries in Canvas Apps in Power Apps Microsoft Power Apps image 15

And now i can use this view in my filter.

Filter(Fruits, 'Fruits (Views)'.'Today''s fruit, Colour = Blank())
Using Dataverse views to simplify filter queries in Canvas Apps in Power Apps Microsoft Power Apps Mix of View and query in filter

And of course you could also create a view that includes the colour filter, making the filter query in the app really simple.

Filter using Dataverse views

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.

2 thoughts on “Using Dataverse views to simplify filter queries in Canvas Apps in Power Apps

  1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.