Mark a related table Row as Modified when a lookup value is added?

[William_Bell]

Hi folks, I’m wondering if there is a way to mark a row as modified when I add additional notes from a related “Tasks Notes” table?

I was looking for the Task updates one of my team members added this morning to the “Team Tasks” tracker I created for my company’s group:

When I changed the “Modified” interactive filter I have for the table for “Today”, nothing displayed - as the image shows, she clicked the + Note button which opens up a row in the related “Task Notes” table, added her notes marked “5/1” and the Task Note displays correctly in the Team Tasks table.

However the updated row still shows the row as being last modified when the table row itself was last changed on 04/28?

In my mind, adding a new Task Note to a row in its related Tasks table should tickle the modified time for that row since something was actually added to that row?

Is there way in the Add Note button formula I can somehow get the Modified timestamp of the Tasks table to become updated after a new related Task Note is added?

Any suggestions are appreciated (I also have related “Sub-Tasks” column that are added to the row in a similar fashion…)

[Pch]

Hi @William_Bell !

I can’t remember exactly (so I could be wrong ) but I think that Modified() only returns the last manual modification within a row (let it be through someone effectively modifying a value or through an action).
So, something modified through a formula would not count … which seems to be how you get the notes within your Notes field.

There are more than one way to deal with this but I think that, as you already use a button, having your button check and uncheck a checkbox when pushed to force a manual modification in the row, might be the easiest way to go

I don’t know what you’re button is already doing at the moment but you could just add to its action(s) something like :

thisRow.ModifyRows(thisRow.Checkbox,thisRow.Checkbox.Not())

And what this should do is either check a checkbox if it’s currently unchecked (as Not() negate a value) or vice-versa .
(The value of the checkbox doesn’t matter, only the fact that it was manually changed)

This will force your Modified field to recalculate as checking/unchecking the checkbox is considered as a manual modification .

And you could re-use the same principle and checkbox for your sub-tasks.

Here’s a small sample to illustrate, so you can see the value within the field Modified on (thisRow) change each time the value of the checkbox changes

I hope this helps

[William_Bell]

Hi Pch, thanks so much for your response - I was going to mention similar (my thought was to suggest (as an inelegant solution) adding a hidden text column at the end of the row and adding & removing say a “space” into the column, so it would tickle the row causing it to become modified, but opt’d to just see if there was already a solution, keeping the post short) - your checkbox option is a much cleaner solution w/a similar effect.

I would hope Coda looks at this use case, and perhaps makes a more elegant way to (optionally) support this kind of updating of a table row due to the add / removal of a related table values, wo/requiring an additional column which may confuse business users if they happen to uncover the setup…

If there were a simple formula thisRow.RefreshModifiedDate() or similar, I feel would be nice way to accomplish this.

Thanks again for your time and suggestion!

Edit: I will add, this is sorta tough because my button is adding a new row into another (Task Notes) table, so I don’t see parameters to have that button also update a value in this Tasks table was well - I’m guessing I’ll have to use a full formula for this button - this will be my first time completely coding a button function - NICE

-Will

[Pch]

My pleasure @William_Bell !

@William_Bell

adding a hidden text column at the end of the row and adding & removing say a “space” into the column, so it would tickle the row causing it to become modified,

Yes, that was a possibility too !

But the advantage of using a checkbox here, as a boolean, is that it can only be either true or false, On or Off, etc… (even though there’s blank too ) so there’s no need to a full formula as its value can just be negated .

@William_Bell

this will be my first time completely coding a button function - NICE

And this is where the fun begins !
At least, in my own personal experience …

Here’s a little something to get you started …

This is the Action formula you’ll find in the button in the sample below .

RunActions(
  // Action 1: Add a row in [Table 2]
  [Table 2].AddRow(
    [Table 2].Name,
    Format("Test {1}",[Table 2].Count() + 1)
  ),
  // Action 2: Check/Uncheck the Checkbox in this row
  thisRow.ModifyRows(thisRow.Checkbox,thisRow.Checkbox.Not())
)

Happy coding !

[William_Bell]

“THAT’S THE EQUATION!!” - Ruk, Star Trek

You saved me, Pch! I realized I needed to be able to chain multiple formulas together and I was looking through Coda’s Formula docs, and I didn’t run across that RunActions formula you have listed above!

I was coming back to write that I was drowning ins squiggly red syntax error warnings and couldn’t chain the two formulas, and here you supplied the answer AND a demo solution!

Once I saw the RunActions(…), that was all I needed!

I owe you a nice tall glass of Coda Cola! (Whoops, see what I did there?.. )

Thanks, so much Pch!

[Pch]

Ahaha !

My pleasure once again @William_Bell !

[William_Bell]

Hey Pch, I just wanted to follow up w/a final on this topic.

I got my Task Notes and Sub-Task buttons working where when I add a new Task Note or Sub-Task into the Team Tasks table, the Modified time for the row is correctly updated - thanks again for your help with this.

I will add, this has now opened a Pandora’s Box because while adding new Notes and Sub-Tasks work great, the Task table is still NOT modified when I then delete those Notes or Sub-Tasks…

…AND in addition to those two tables, my Sub-Tasks table also has a similar Add & Delete Notes buttons as well!

I’ve also taken the chance?, and got away with using the same toggle button for indicating modifications from both the Notes and Sub-Task buttons!

Since Coda is a collaborative / multi-user application, I’d HOPE there’s some semaphore action going on behind the scenes, controlling access to shared resources (the toggle button) when multiple users are banging on and same table (and/or row) at the same time!

If not / or this turns out to be glitchy, I’d then have to use multiple toggles per table - one for adding / removing Notes and Sub-Tasks etc., - UGH and OUCH!

This IMO, is a lot of work, just to indicate to a table that a row has been modified effectively by a related table!

I’m definitely going to put in a request to Coda for adding a UpdateRowModified(tableId, rowID) formula which could encapsulate the frankly simple goal of letting a table row know it should update its Modified timestamp, wo/having to add all kinds of hidden columns, and hard coded formulas etc., (making the barrier for non-coding users very high to accomplish this), since the current workaround gets overly complicated when just a few columns of data are used in this fashion…

Take care!

[Pch]

@William_Bell

the Task table is still NOT modified when I then delete those Notes or Sub-Tasks…

Well, there’s another option I didn’t suggest earlier as I didn’t know you wanted to track other events (deleting a row, etc…) …

Instead of using a checkbox to simulate a manual modification in a row, you could use in your Modified field a formula such as :
(See the field [Modified (All in one)] in the sample below)

ListCombine(
  thisRow.Modified(),
  Notes.Modified()
).Max()

… where Notes.Modified() will return the moment the table Notes was last modified in any way (a row was added, modified or deleted) .

And the whole formula just returns the maximum Modified() value in between thisRow or the table Notes.

You would probably need to add your sub-tasks table to the list too.

Here’s a small sample to illustrate …

The buttons are just there to “modify” either the table Notes or thisRow.
Then there’s the field [Modified (All in one)] and the 3 last fields in the table are just there to show what was last modified and when …

[William_Bell]

Wow, thank you so much, Pch - I will say I ABSOLUTELY ENVY your mastery of Coda’s formulas!

I’ll give your example a try and I’ll let you know how I make out.

I learned of Coda back at the end of Nov. 2022 from a YT Video - “Notion vs Coda” and I haven’t looked back since!
(That night as I started playing around w/Coda, I was literally laughing out loud - my wife thought I was nutts!, as I was seeing all the advanced capabilities Coda had!)

I’m slowly getting getting the hang of the formulas, but I def have a way to go - thanks again for sharing your wisdom with me!

-Will