Get year from dates with Select List

[Stian_Ovesen]

I have a table where I have a column using the Date column type, and have set the date options to show Month and Year (i.e. Feb 2023).

I’m trying to set up a filter with a select list, so I can filter every entry within a given year. I have had no luck so far. Does anyone have any pointers?

[Pch]

Hi @Stian_Ovesen !

There are multiple ways to get to that result, depending on your use case …

The very first step would be to add a column to your table to extract the year from your dates with something like :
(In the sample below, this field is simply called Year )

thisRow.Date.Year()

Then, you can create a single/multi select canvas control and in the section Selectable items use something like :

Table.Year.Unique()

This returns the list of unique years in my table named Table ()

After that, in the Options → Filter of your table, you can add a filter such as this one :

thisRow.Year.Matches([select year])

[select year] being the canvas multi select in the sample below, this formula filters my table by the value(s) selected in the canvas control ( : Matches())

Or, you can use a table to store the years you need and a lookup/relation field to link the years to your dates

The formula within the Years lookup/relation field to pin point the appropriate year in the Years table would be something like :

Years.Filter(Year = thisRow.Date.Year()).First()

Then, you can filter your table using a relation/lookup canvas control (which is also a single/multi select control but dedicated to lookup/relation type of fields).

Coda can create it for you if you either right click on the Years field in your table, then go in Filter → Canvas control → Create new or …

In the Options of your table, once you’re in Filter, you can click on Add filter and select the appropriate field

Then, instead of Is any, you should select : Uses canvas control.

All that’s left is to click on Create control

I hope this helps

[Piet_Strydom]

Not sure what you mean with a select list?

But you can create a column with the formula datecolumn.year(). That will extract the year from the date, and you can then filter on the column, with any of the Coda filter tools.