I’ve been scratching my head for a couple of days now: I’m trying to track who is the last person who changed a text column with an automation, but it fails with this error message.
Here’s a simplified doc showing this behaviour (check the automation rules):
Context: the value of the Text column has to be reviewed and approved. If a user changes the Text, it is no longer approved, so the “change approved?” should change to “no”. This is tracked by an automation, easy peasy. However, I’d like to know who is the user who last changed the text. “ModifiedBy” property doesn’t do the trick, as running the automation in itself changes the ModifiedBy to “Automation Bot”. So I tried to tracked the last (human) user using the same automation.
I usually avoid using Automations for anything that requires immediacy in response because of the noticeable delay between the change and the automation action which could be up to a few minutes.
I find automation are more suitable for things like: running a nightly batch summary, a weekly backup process, etc.
That being said, we have two issues at hand in your example:
The “ModifyBy Rule” has a typo in its then formula. Step1 Result is a row from your first table, but you are giving a column from your second table in the ModifyRows formula of the “Then” step of the rule.
Even when I fix 1. which was likely just a typo in the example your put together and not in your actual doc, I suspect - but I am not 100% certain- that we get the “User has not been set” because the automation rule is triggered as soon as the change happens and before Coda internally updates the row’s .Modified() and .ModifiedBy() attributes.
I only guess this because I observed funny things like this happen before. For example, you try to access thisrow.RowId() immediately after it’s created by a button action - fun fact : the rowID is not there yet.
How about achieving the outcome you want without any automations:
Turn “approve” into a button that saves the current time into a hidden column. This has the added advantage of allowing to control who can approve via a combination of the disable If and the User() formula
Take advantage of the fact that each individual cell has is own .Modified() and .ModifiedBy(). You can just compare the Text.Modified() attribute against the saved approval time to determine whether to reset the “change approved?” column to “no”.
Working example below. I kept all intermediate columns visible for clarity, but you can hide them in your final version.
thank you for your thoughts and example! Let me unpack your message:
Sorry for the typo, it’s indeed from the example doc, as I duplicate the automation rule. Well spotted!
The error message is weirder if we consider, as you did, that there’s usually a delay before an automation triggers. That should leave plenty of time to populate ModifiedBy(). Or we should be able to access the previous value in any case. I suspect more a sandboxed execution for the automation, from which not all properties are accessible, but who knows?
In your solution without automation, you suggest:
an “approve” button. My original document does just that, with a user-based deactivation, but I removed it for the example doc,
using ModifiedBy() at cell level. You made my day with this! I thought the property was available only at row level, hence my reluctant automation-based approach (I’m also not a fan of automation.)
Thank you for taking the time to look at my issue and making a working example, it’s very much appreciated!
