Alert when Checkbox Column gets Unchecked
I have a formula column that gets checked or unchecked depending on some other things in the sheet.
An alert goes out when this gets unchecked (and when rows get changed, not added) on an hourly basis.
We recently got an alert saying that a row got unchecked but it makes no sense. I checked the cell history, and it was never checked in the first place. I would understand if someone made a quick mistake and deleted it but the alert is set to hourly, not immediately. That row did just get added, though.
My question is: did this alert go off because a new row got added? And a new checkbox was then created, starting off unchecked, and triggered the alert?
Would it help to put this column as "not blank" as a condition for the alert? Or would that cancel it out from the trigger?
Thank you
Best Answer
-
I've come up with a workaround :)
I'll get another cell changed if the rate is ever chosen as 2. Maybe it can record the date. This cell will stay untouched no matter what happens later.
Then when this cell isn't blank AND when the checkbox gets unchecked, then we will know it went from 2 to something else.
@Paul Newcome I'm still open to smoother ideas though.
Thanks for your help
Answers
-
It might help to change the alert to send an update when status is changed to "checked". or change your alert status from "when rows are added or changed" to just "when rows are changed".
-
It sounds like you may be right in that the row being added with the box being unchecked to start. Do you have any system generated columns or formulas in other columns?
-
Thanks @Tony Rojek , but yes it's set to only "when rows are changed," not when they're added as well. And we are only monitoring when this gets unchecked.
The setup is, as an example: One column called "Parent Rate" has a dropdown of values 1-3, but it can also be blank. When a row has "2" chosen and then at a later point has the "2" removed to blank or anything else, we send out an alert. This is only when it started at 2, not 1 or 3. These selections only get made on a parent row.
We use a helper column called "Child Rate" that brings this rate value down to each child row. If there's a 2 in there, the formula column "Rate is 2" gets checked. We set the trigger to go out when this column gets unchecked.
Interestingly, the incorrect alert went out on the day that a new child row got added, even though 2 wasn't chosen anywhere. And what should've gone out as a combo of all the child rows + the parent row in this alert, only the parent + specific new child row were included.
@Paul Newcome - I have about 100 other formula columns on the sheet :) why do you ask? None are system-generated though. And it would not help us to start off with this being checked.
-
It sounds like what is happening is the row is being created.
Then your additional formulas run on the row which changes the row and triggers the automation.
Are you able to provide a screenshot of the automation?
-
Sure here is the alert. I just left out the message body.
I wonder if I should add in a condition to only send out if "Rate is 2" is checked... if that doesn't cancel itself out as an impossibility, maybe the system will recognize that as a "checked and then unchecked" workflow?
-
I've come up with a workaround :)
I'll get another cell changed if the rate is ever chosen as 2. Maybe it can record the date. This cell will stay untouched no matter what happens later.
Then when this cell isn't blank AND when the checkbox gets unchecked, then we will know it went from 2 to something else.
@Paul Newcome I'm still open to smoother ideas though.
Thanks for your help
-
I think leveraging the Record a Date automation is going to end up being the most reliable way of accomplishing this.
