Blog

Search - How to filter records by month and year using a delegable expression

A common question I often see is - how do I filter a table or SharePoint list by month and year? This post describes how to carry out this task using formula that is delegable.

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.

Sequence(12,1,1)
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.

Filter(Property, 
       AquisitionDate >= Date(drpYear.Selected.Value, 
                             drpMonth.Selected.Value, 
                             1) 
       And  
       AquisitionDate < DateAdd(Date(drpYear.Selected.Value, 
                                        drpMonth.Selected.Value, 
                                     1),
                                 1,
                                 Months
                         )
)
The screenshot beneath highlights how this formula works as expected.

Conclusion

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.
Previous
Next
Related posts