How to better use Cards Grouped view based on month not a full date

[tmchow]

I’m struggling to make a grouped card view useful based on dates.
We often have projects with a launch date (full date like 4/1/2022).

However, If i try to group them by that Launch Date in a card view, there’s no option to use only a portion of the date like just the month. We end up with this less than helpful view:

The only good thing about the above view is that you can drag and drop cards between columns which updates the Launch date.

I then decided to try to add a Launch Month calculated column with this formula:

If(thisRow.Launch.IsDate(), thisRow.Launch.MonthName("MMM"),"")

Good News: Then the card view grouped by this new column works!

Bad News: You can’t drag and drop the cards between columns because it’s a calculated column.

I’ve created this test document to show you:

Does anyone have a solution for this? This seems like such a natural thing to want in planning scenarios.

[Tim_Sherman1]

Hi there Trevin.

Automations are one option for this (results are not perfect, but acceptable in many situations):

• Make your ‘Launch Month’ column a manually populated date column, but with the date format set to display only the month and year, e.g. “May 2022”
• Group cards by the ‘Launch Month’ column
• Create an automation triggered whenever the Launch Date is changed. Set this automation to update the Launch Month column to the first day of the month of the Launch Date. The date value must always be the first of the month, otherwise you get multiple groups for the same month.
• Create another automation triggered whenever the Launch Month is changed (i.e. as a result of dragging and dropping between groups). Set this automation to update the Launch Date column, taking the month and year from the Launch Month, and the day from the (old) Launch Date

Note, for this final automation, you’ll need to include a mechanism to ensure that dragging a card with a Launch Date of 31 May and dropping it into ‘June’ updates the Launch Date to 30 June, not the invalid date of 31 June. The best way to do this is to ensure that when taking the ‘day’ from the (old) Launch Date, you include an IF function to test whether the numeric value of that day is greater than the numeric value of the last day of the new Launch Month (thisRow.[Launch Month].EndOfMonth(0).Day()), and if so substitute the latter instead of the former.

Hope that makes sense - let me know if you need a demo.

[tmchow]

This makes sense! thank you. Total pain to need to do this, but I like your approach.

[Tim_Sherman1]

No worries. I agree it’s a pain. FWIW you can make the updates a little less laggy with strategically placed buttons, e.g. a refresh button on the card that updates the Launch Month or Launch Date immediately, using the same formulas as above, and choosing the ‘direction’ of the update based on whichever of those two columns was most recently modified.

The most general solution I can think of by way of an enhancement to Coda itself would be to create a function called something like userInput(), which switches a cell in a formula column to accepting/displaying a user input rather than a calculated value, and references the last input provided by the user.

That way, in your use case, you could define the columns as follows:

Launch Month =

if( 
   thisRow.[Launch Month].modified() > thisRow.[Launch Date].modified(), 
   thisRow.[LaunchMonth].userInput(), 
   date( thisRow.[Launch Date].year(), thisRow.[Launch Date].month(), 1 ) 
)

Launch Date =

if( 
   thisRow.[Launch Date].modified() > thisRow.[Launch Month].modified(), 
   thisRow.[Launch Date].userInput(), 
   date( 
      thisRow.[Launch Month].year(), 
      thisRow.[Launch Month].month(), 
      if( 
         thisRow.[Launch Date].userInput().day() > thisRow.[Launch Month].endOfMonth(0).day(), 
         thisRow.[Launch Month].endOfMonth(0).day(), 
         thisRow.[Launch Date].userInput().day() 
      ) 
   ) 
)

[tmchow]

How do I prevent race condition with the 2 automations? They are triggered on a change on that column so if they are both on, then won’t it just keep ping ponging back and forth?

[Tim_Sherman1]

Good point! In the automation rule that updates the Launch Month whenever the Launch Date changes, you can include an ‘IF’ condition in the automation rule, as follows:

thisRow.[Step 1 Result].[Launch Month]!=
   Date(
      thisRow.[Step 1 Result].[Launch Date].Year(), 
      thisRow.[Step 1 Result].[Launch Date].Month(), 
      1
   )

This tests whether the Launch Month already matches the month of the Launch Date, and if so, stops the automation from unnecessarily updating the Launch Month (and triggering a loop of further automated changes to the Launch Date etc).

[tmchow]

Thanks! this solution worked overall. Kind of a nightmare to make sure no one breaks it, but that’s coda life