Hi community, I got a question.
I have multiple tables and each of them has a date column “Month”.
Also, there is a control for each of the tables that is used to filter rows based on the month.
When adding month data into the Month cell, the best I could figure out to use is date select like this:
But the problem with this is that user has to select exact date.
So to compare two months that are created this way, I need to “erase” the day. What I do is turn the date into a new date that is the first day of that month, and then compare them. Like this:
The problem is that this is really really ugly and a lot of more work down the line.
How do I implement this in a nicer way than this?
I was thinking of creating a table of months, but something tells me that is not a good idea, especially because I am syncing these tables across multiple docs.
to be clear, you do not use a datepicker, nor a lookup (that could related to something like “sept 2022”, but you have a text control people fill out manually, right?
once we have this out of the way, we can have a look and as regular user, you understand the value of a demo doc
Datepicker is a nice UI cause you have things like “today” etc., but yeah it could be confusing to users who think they’re supposed to select a month but then somehow have to choose a day too…
I’m not against the support table filled with year-month pairs. You could also have a “first day of the month” column in it to let you do true date-based filtering. Depending on your use case, you could also filter the available options in lookup controls (for example, if you never have to look things up more than 3 months in the past, you could filter to start 3 months ago, so people don’t have to scroll past years of months to get to “next month”). In theory this might also let you do more custom stuff too like a “Next month” (or even “Next quarter”?) row that changes dynamically (either via If() formula on the column, or updated periodically via an Automation).
Or have two controls/columns: year and month (instead of trying to combine them).
hi @Tomislav_Mamic , it seems to me that the main issue is not how to make it work, but how make it work fast & elegant.
the idea of a helper table that contains the formatted period (like Sep 2022) seems a good one to me, you can template this one and use in all your docs. The helper table has a column for year (based on a second table with all the years) and with the months and the month number.
This is how I’d like to work, if data comes back again and again, I template the set up and improve the template based on new insights. In my case it means that I have a table with the names of the months in several languages. This table has in the display column the name of the month in the active language. You can make this as fancy as you want, but since in Coda every doc is unique templates are easy (or cross doc, but that seems to heavy to me in this case).
