Smartsheet Basics
Smartsheet Basics
Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.
Automation: When there is a change and the field already has a vaule
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?
Best Answer
-
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 checkedNow 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!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Answers
-
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 checkedNow 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!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
@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?
-
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.
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
@Kerry St. Thomas that's perfect, thank you, sorry i was slow.
-
@Kerry St. Thomas this seemed to work, but finding that the notification is sending before the automation runs to check the box. any ideas here, we can't do a notification hourly, so not sure there is a solution.
-
I'm not really following what you're saying. Can you post screenshots of your automations and the pertinent column header/s in your sheet, so there's a bit more context to be able to trace down? Thanks!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
@Kerry St. Thomas i figured it out. Apprently there is some bug in smartsheet, where if you notification has {{field}} in won't send hourly. Basically i had to set the rule that make the check mark to be right away and then the notification to send hourly. Otherwise the notification would send before the checkmark was on the sheet. All is good now.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
