Automation: When there is a change and the field already has a vaule

Charles Osborn

Looking to send a notificaiton if a date field changes, but not if it was already blank before the change. Basically I want to send a notification when the date changes, but not when the date is first assigned. Anyone know how to do this?

Kerry St. Thomas

The solution for me started as an absolutely hideous workaround, but this is what I've eventually refined it to:

Create a checkbox column, and then create a new automation:
-Trigger: When Rows are Changed, When Date Field Changes to Any Value.
-Condition: When Checkbox is not checked
-Action: Change Cell Value in Checkbox to checked

Now that you've got that checkbox and automation in place, you have a marker on your sheet for when the date field goes from BLANK to SOMETHING. Once you have that, you can create your automation:
-Trigger: When Rows are Changed, When Date Field Changes to Any Value.
-Condition: When Checkbox is checked
-Action: Send your notification.

Also, in THIS case, I recommend AGAINST putting a branch in this: I'm the paranoid one, so if the date is populated and then changed all before the sheet is saved, a notification can be missed. This is an imperfect solution - if someone MISTAKENLY populates the date column and then erases it, that little box will remain checked. You could conceivably add in yet another automation to uncheck the box; I have found in the cases I've used this that the error rate for that one was small enough to not sweat it. Also, the checkbox column isn't a formula, but is subject to user modification - so lock and hide the column, and just hope admins on the sheet don't go crazy checking boxes. :)

Good luck!

Charles Osborn

@Kerry St. Thomas so you are saying add a user controlled check box that they check when they change the field? Is that your solution?

Kerry St. Thomas

No. I'm saying add a checkbox that's controlled by an automation - whose value is fully based on users populating the date, so that you have a delineation at the row level for whether the date was NEWLY added, or CHANGED. Notice the Condition difference between the the automations I describe.

There isn't really a direct way in Smartsheet automations to do a "WAS it populated" versus "IS it populated" comparison, so this workaround creates a data point that essentially allows for that comparison.