I have a list of items (board games) and I’m tracking the date for when they were last played. (It overwrites the old play date with the new one, I’m not tracking history here). I also give the game a status based on how long it’s been since it’s last been played. Green - last 3 months, red - more than two years, etc.
I’d love to track which games are “evergreen” by figuring out how long a game has retained the “green” status without changing. Which happens automatically once x days have passed. I’m not even sure this is possible with Coda’s tools, but I know I’m just scratching the surface of what it can do so I thought I’d check and see if anyone had an idea of how to get that.
I’m not entirely sure where to begin with this one - but it’s a pain/nigh on impossible to track manually. If I can just figure out how long a game has been green I think I can set rules for x months/years to be marked as evergreen. (I suspect I may need to start tracking played history, but then the math escapes me.)
Here is a suggestion : You can create an automation that will track when you change status to “Green”, and fill a column with the date and time of this modification.
Then you can auto-calculate the time step between now and this date
OOH First time using automations! This is probably really going to expand my ability to bend Coda to my whims. I feel so terribly powerful!
Hopefully I’m doing this right - The steps all make sense although it took me a bit to realize the automation doesn’t run instantly - which makes sense. But I can confirm the automation is working! When the status column changes to green, a date is added to the newly made column. I do have one question/concern.
I originally had the status column update automatically based on the date last played. (Basically if last played is < 3 months set to green) - but I quickly noticed I couldn’t pick it as an option in the automation first step - i think because it can’t be manually changed.
So i just created a new field with a quick select list to test out the automation and that’s when it started working. My thought now is to have when I click the “played it” button for that to update the last played field and change the status to green. But I suspect this automation will still run even if it’s changing from green to green thus reset the “when started” date. Does that sound correct?
Would it work to set the button “if not green, set to green?” so it won’t modify an already green item. I’m not sure how that equation would be written - how do I say “else don’t change”? Do I just leave the fast part blank? (Seems like no because now my button is disabled).
The problem is that, often, an automation would run even if the modification is not manual.
What I would suggest in your case, is also to have a button “set to green”, rather than doing it with a dropdown and automation. Check that
Button “set to green” will change the status to green, and column “When” to now. In my example, I disable the button if the status is already green. So when you click this button, is the beginning of the clock to track how long your game is in this status (using Now()-WhenGreenStarted)
Button “played it” → Will modify LastPlayed date of course, and ONLY IF the status is not green, will click (using Runactions) the SetGreenStatus button. But if the status is already green, it wont reset the “When Green Status” value. Is that understable for you
Hi @Rae_Hanley, you can also add a column with the formula =Status.Modified() to show when that column was last changed.
That should work if the Status column is something you update manually or that an automation directly sets, though it won’t work if the Status column itself is calculated by a formula.
Hi, @nathan I did think about having the automation in charge of changing the status from green to yellow to orange - but looking though the automation options I don’t think I can do the same math as I was able to from a formula since it changes based on another field (duration since last played).
But I’m def. going to play with that and see because my formula for changing the status column is a bit wonky. not broken, but wonky.
Looks like I was just missing the final “” around the “do nothing” option in the formula. (My formula building knowledge has mainly been google sheet/excel and 10 year old SQL memories but it’s coming together.)
I agree setting a “set to green” button actually might be the easiest answer here - true I’ll have to click two buttons (play and set to green) but that’s not the end of the world and may clean up a lot of random interactions that are starting to infiltrate and already quite complex table. Especially if I just always click it and don’t worry if the game was green already or not.
Thank you so much for you excellent examples and sample do - very helpful and easy to follow!
And done! This is great. For now I have it set that a game is evergreen if it’s been green for 75 days and the duration since the last play is not equal to the duration of being green. I shall adjust as necessary.
And I was able to create a view of the game table for evergreen - I found out I have 4 games only that fit this criteria (and those are mostly my bar games, lol!)
As for the big table The “Played” button is back to setting the last played date, adding +1 to the number of plays, AND now triggering the “Greenify” button if it’s not already green. I do still have some work to do on how I want to trigger the changing of the status from green to yellow to orange which is also based on duration since last played and having that change clear the “When green” date when it looses that green status. But that feels fussy but doable!
Again that you so much @Quentin_Morel and @nathan for your assistance! And even if in the end automation wasn’t needed, I’m so happy to know that feature exists to explore.
For the rest of your feature : you could define a status (yellow/blue/green) automatically using time steps, and if you want to know since when your game is in this status may be use a simple definition.
I tried something for you
Define begin/end for a game to be in a status, according to “LastPlayed” value , for example this :
So this is easy to retrieve the duration since last session :
@Quentin_Morel
That does make sense! I have a convoluted equation running on my “auto status” field right now but this looks to A) break it out into digestible steps a bit better and B) work with my new status field that I am now using as my main status field. It also moves some of the calculations out of the huge table which is my current goal - try to get that table to be just the fields it has to be since it’s got everything in it.
I shall play around and see how to get it all hooked up.
