A common requirement is to filter records by month and year, and a practical way to accomplish this is to provide month and year drop down boxes.
A natural way to build this feature is to call the Filter function and to utilize the Month and Year functions. The problem with this method is that it results in an expression that is not delegable, as shown in the screenshot beneath.
To avoid this delegation problem, we build a condition that filters records between the start and the end date of the month, instead of calling the Month and Year functions.
As the following screenshot shows, this defines a delegable expression that accurately returns all matching records.
Demonstration - how to filter records by month and year dropdowns
To demonstrate the typical application of this technique, let's walk through the steps to build a screen that filters records by month and year drop downs.
First, let's add a dropdown control called drpMonth. We set the Items property to the following formula to populate the drop down with a sequence of numbers from 1 to 12.
Next, we'll add a dropdown control called drpYear and set the Items
property to the following formula. This populates the drop down with a range of year numbers, starting from the current year minus 6 years.
Sequence(12,Year(Now()) - 6,1)
We can now add a gallery or table table control, and set the Items property to the following formula. This formula derrives the start and end dates based on the dropdown values, and filters the "aquisition date" column by these values.
AquisitionDate >= Date(drpYear.Selected.Value,
AquisitionDate < DateAdd(Date(drpYear.Selected.Value,
The screenshot beneath highlights how this formula works as expected.
To filter a set of records that match a selected month and year, we
can construct a filter expression that returns all records.that fall between
the start and the end date of the selected month. This technique will work in a delegable way.