How to Count the No. of Times I Checked a Checkbox :white_check_mark:

[thera_luis]

Hi. Would like to know is there a way to do automation so that I can count how many times I have checked a checkbox? :white_check_mark:

The counter (or variable?) should increase by 1 every time I check a checkbox in the corresponding record. It should NOT decrease by 1 if the checkbox is unchecked. :white_medium_small_square:

I need this function/automation because I have recurring tasks every week and want to count the times I completed the task.

Also, I think there is currently no way to uncheck a checkbox by formula / automation. How can I make my weekly tasks show up to be unchecked when a new week starts?

Thanks!

P.S. I have tried this method:
By automation, every time a record is checked (trigger), I create a new record in the ‘Script’ table. The record has my task’s ID as its name and the number (initially 0) copied from the triggering record field ‘counter’. Then, another action is modifying the triggering record by adding 1 to the Script record. But it seems doesn’t work :slightly_smiling_face:

[Justin_Barrett · Accepted solution]

Here’s one way to pull this off:

1. Create a {Count} number field and set its default value to 0.
2. Create a formula field named {Next Count} using the formula: Count + 1
3. Create an automation that triggers on a condition, with the condition being that your checkbox field is checked.
4. The action step for that condition would be an “Update record” action that updates the record from the trigger step, inserting the {Next Count} value into the {Count} field. In doing so, {Next Count} automatically increases for the next time this is triggered.

This could also be done via a “Run script” action in an automation, which wouldn’t require the extra field, but it does require that your base be in a Pro-plan workspace or higher.

thera_luis:

Also, I think there is currently no way to uncheck a checkbox by formula / automation.

Not with a formula, but it can be done with an automation. If you can use “Run script” actions (note the requirement for this above), you can use such an action to process multiple records in your base. I have a script action in an automation that runs daily to reset certain tasks, including clearing checkbox fields.

If you can’t use a “Run script” action, there are other ways of making this happen, but they will eat up your monthly automation quota more quickly. Let us know if you need one of these alternate options.

[Katerie]

There is a way to uncheck a box with an automation without needing to use a script:

Have the automation update the record’s checkbox field with the value: False

You can set this up to clear the checkboxes once every week, like you stated in your post. Create an automation using At scheduled time as the automation trigger, followed by Find records to search for all the records where the checkbox is checked. Then use Update record with the step outlined above to uncheck the boxes.

[Justin_Barrett]

I’m not sure if they’ve updated how the “Find records” step works, but the last time I looked at it, it was only technically supported to work with a later email action; i.e. find a bunch of records, then email people based on the addresses in those records. I’m not certain if it will work for bulk updates.

[Kamille_Parks]

Update record is still singular, so you cannot update multiple records at once with it.

[thera_luis]

Thanks for your help and yes I would like to know how can I uncheck checkbox without using run script function. :slightly_smiling_face:

Justin_Barrett:

If you can’t use a “Run script” action, there are other ways of making this happen, but they will eat up your monthly automation quota more quickly. Let us know if you need one of these alternate options.

[Katerie]

Apparently you cannot do it the way I posted above at this time, but you can still do it as part of the automation that @Justin_Barrett wrote about.

In step 4 of his automation, he has you insert the {Next Count} value into the {Count} field. Add a new field to that step of the automation, making sure it is below the {Count} field (so that it updates after that one does). Set the new field as your checkkbox field, and for the value, write/paste the word False into the box.

This will make it so that the checkbox unchecks itself at the end of the automation.

To keep track of when you last checked the box, you can use a Last modified time field that is set to show the changes to the checkbox field only.

[Justin_Barrett]

Katerie:

Set the new field as your checkkbox field, and for the value, write/paste the word False into the box.

You can also leave the box empty. An empty box will clear the contents of any field: checkbox, date, single-/multi-line text, etc.

[thera_luis]

Thanks. But what if I want my record to be updated only at the end of the day, but not every time I click on it?

[Justin_Barrett]

If you don’t want to use scripting in an automation, then here’s an alternate solution.

Make a formula field named something like {Checkbox Reset}, with a formula like this (change the field names to match your actual records):

AND({Checkbox Field}, HOUR(NOW()) = 0)

That will output a 1 when the checkbox is checked AND the current hour is midnight. However, you’ll need to adjust the “0” in the formula based on your local timezone. This is because the NOW() function returns the current time based on GMT, not your local time. So midnight (hour 0) in GMT will be some other hour for you (assuming that you’re not in GMT). If you want it to reset at midnight in your local time, you’ll have to calculate the GMT offset and use that number. For example, I’m in the Pacific timezone which is currently GMT -7. Midnight for me is 7am GMT, so I’d use this variation:

AND({Checkbox Field}, HOUR(NOW()) = 7)

Figure out the variation that you need for your local time when making that formula field.

With that in place, modify the first automation to only update the count of how many times the record was checked. To create the second automation that resets the checkbox daily, use the “When record matches conditions” trigger, with the condition being that “Checkbox Reset” = 1. For the action use “Update record”, making sure to update the record that triggered the automation to run, and clearing that checkbox field by leaving its settings empty in the action step.

This automation should only trigger during one hour in any given day, and only on checked records. For records matching that condition, the automation will fire once, then wait until the trigger resets, which will happen once the formula outputs a 0 (the very next hour). Then it’s ready for the next day’s trigger.

The downside, as I mentioned above, is that it will use a lot of automation runs from your monthly allotment. If you check 50 records in a given day, it will run 50 times to reset them all one by one. This is why a script is preferred. A script can process multiple records, and the automation only runs once.