Is there a way to get the modified date of an item only the very first date it's modified?

1

So I’m using a progress bar and I’d like to record when the progress bar is no longer equal to 0 (start date) and when it’s equal to 100 (end date). However, if I have multiple days of work, the “start date” is going to change each time the progress bar is moved to some value between 0 and 100. How to I make it only collect the very first time it leaves 0?

2

If I get you correctly, you want to build an automation rule that will record the date the progress bar is manually moved off 0, and then also separately the date the progress bar is moved onto 100?

Well, there’re several ways to do that. What I would do is first construct the button to perform the relevant operations given the position of the progress bar. Then make a simple automation rule that just pushes that button whenever “Progress” changes.

The button formula would look like this:

thisRow.ModifyRows(
  thisRow.Start, 
    If(thisRow.Progress!=0, thisRow.Start.IfBlank(Today()), ""),
  thisRow.End, 
    If(thisRow.Progress=100, Today(), "")
)

If you need more precision than day, you can use Now("second") instead of Today().

Explanation in normal English:

  • For the Start column:
    • if the progress is not equal to 0, set the Start to Today if and only if Start is blank (otherwise keep the existing Start date)
    • if the progress is equal to 0, reset the Start date to blank
  • For the End column:
    • if the progress is 100, set the End date to Today.
    • otherwise, make the End date blank

Limitations

  • if someone accidentally sets the progress bar to 0, the original (real) start date will be lost
  • likewise, if someone moves the progress bar off 100 after it’s finished, the end date will be lost
1 Like
3

Hi Ryan, Thanks for your help! Is there a way to do it without automations? I only have a limited number and I suspect there is, I just haven’t found how.

4

Well, if you want it to be “automatic” then you will “automation”. Otherwise, you will have to have the user either press a button or manually set the Start Date.

Were you not using automation before? What did you mean by

If the progress bar is a formula, and it is moved by the press of some other button (eg, a “Done Task” button somewhere) then you could incorporate the additional start-date/end-ate logic into that button.

5

Yea I noticed that the button you set up above would work for manually recording the start date. That in itself is useful.

I wasn’t using automation before as I figured it could be done without it. What I did was have this code for the start date column:

If(
  thisRow.Progress != 0, IfBlank("", thisRow.Progress.Modified()), ""
)

And this code for the end date column:

If(thisRow.Progress=100,thisRow.Progress.Modified(),"")

What I meant in the snippet you pulled out is that, using the code above, the start date column would record the date the progress bar was no longer 0, but would also record every other time it was modified through-out the progress. This obviously defeats the purpose if I want the start date.

If there was a way to “store” the latest date the progress bar was modified and compare it to the previous date and always pick the previous one that could be a solution I imagine. Just not sure if I can do that in Coda.

6

Right, I see what you were trying to do now with Modified(). And I also just learned from you that this formula can get you the modified date-time of a specific cell — I thought it only worked at the level of rows and higher. Thanks :slightly_smiling_face:

But of course, as I’m sure you are realizing, the Modified formula only returns the date-time the object was last modified and it does not keep any sort of history for you to choose from. So you need some other way of storing the specific date of the specific modification.

Actually, technically there is a way to get the Modified formula to conditionally run, giving you the result you want. But since it involves cyclical-reference (which is usually a bad idea in Coda) it’s very fragile and therefore would still need some sort of backup with automation.

7

Yup! I ended going with your original suggestion :upside_down_face:

My aversion to it was that – at least for the way I originally set up the automation – it was checking EVERY row in the table each time a progress bar somewhere on the table was modified. At any given time the table in question was being shared (through different views) with several people and if each person was then changing the progress bars several times a day I saw myself going through automations way too quickly (I had the PRO plan then and the automations were I think counting each row as a credit). It also made the update progress extremely slow.

I now have the team plan and changed the automation to only look at the row where the progress bar changed which is much more manageable (and obviously makes more sense). This is I’m sure what you originally meant so my bad for not understanding and thanks for giving me a viable solution weeks ago. haha

Yup, I figured with a cyclical reference this might be able to get done, but I get why Coda avoids that so it’s fine. Thanks again for your help!

2 Likes
8

Great, glad you got it figured out :slightly_smiling_face:

9

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.