Help

Re: Can I use a formula to clear a checkbox?

Solved
Jump to Solution
1924 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Valerie_Tate
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all, this is my first post on the Airtable community. :slightly_smiling_face:

I have a base that I use to track my FB groups activity. So I have a column with a checkbox to mark when I’ve completed something in that group.

What I’d like to do is have those checkmarks automatically clear off at the beginning of each new week.

Is that possible with a formula? If so, can someone point me in the right direction of where to get started?

Thanks so much for any help!

Valerie

1 Solution

Accepted Solutions

You have to put the Record ID into the ID section, not your checkbox field. See my 3rd photo above.

See Solution in Thread

18 Replies 18

Welcome to the community, @Valerie_Tate!

There are no automations built into Airtable, so you would have to turn to something external to make this an automatic process that happens every week.

You could write a custom JavaScript script to do this with the Scripting Block, but you would need to remember to manually run this script once a week, since the scripting block can’t be scheduled.

If you’d like this fully automated so that it happens all on its own every week, you could create a scenario in Integromat that runs once a week. When it runs, it can search for all your records that have the checkbox checked, and then clear the checkbox from all of them.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Welcome, @Valerie_Tate.

I don’t think you’ll be able to do that with a Formula. Formulas are only capable of outputting some value into the field(column) they are programmed for – they are not capable of changing the value of other fields(columns).

With your use case (tracking a FB group’s activity), I have to imagine you are probably using a Free Airtable workspace. If that’s true, then the easiest solutions to your dilemma are only available to you until September (as far as I know) – that’s the Scripting Block.

Blocks, in general, are the way to go for the kind of functionality you are wanting. The “Bulk Update” block, for example, could give you a single button to click to clear all the checkboxes on a Monday. It still requires your input, but it would take a single click. The “Bulk Update” block will not be available to you at all, though, if you are in a Free workspace.

So the next best option is the Scripting block, which is available to all workspace, including Free ones, but only until September – at which point, I presume, they will make the Scripting block only available to Pro accounts, like the rest of the Blocks. A script that clears all the checkboxes with a single button click would be pretty easy to write. It would require input from you, again, but still just a single button click.

Lastly, you could simple click the field(column) header for your checkbox field (this will select that field for all records in the table/view) and then hit backspace – this will prompt a pop-up dialog like so:
CleanShot 2020-06-02 at 17.09.27
Clicking continue will clear the checks in any checkboxes for you – so, this action takes 2 clicks and a keyboard tap in all.

As you’ll have gathered by this point, Airtable doesn’t have anything built in that will automate this process for you on a schedule. And I’m hesitant to suggest any external tools for doing this sort of thing – I’ll leave that to other people, should they choose to chime in :slightly_smiling_face:

Ah yes, I forgot about the Batch Update block! Another great option for you, @Valerie_Tate, if you don’t mind manually running it yourself every week.

I don’t have anything to add to the options listed above, aside from my own opinion. For something purely automatic, my preference would be what @ScottWorld mentioned re: Integromat. I have quite a few Integromat scenarios running on specific schedules, including one that runs daily and clears checkboxes in certain records, which is pretty close to your original request, @Valerie_Tate .

I really love @Integromat — their interface is so fun, too.

@Valerie_Tate, note that if you’re going to use Integromat to clear out your checkbox field, you’ll likely be using the “emptystring” tag to clear your checkbox field. At least, that’s how I do it — there are probably many other ways, too. See screenshot below. (Ignore my fake field names, but notice the “emptystring” tag.)

Screen Shot 2020-06-02 at 9.57.32 PM

Checkbox fields have a specific set of controls in Integromat. To uncheck the box for a record, you’ll choose the “No” radio button. “Yes” will check it, and “Not defined” will leave it as-is.
Screen Shot 2020-06-03 at 10.55.34 AM

Oops, thank you!! Hahaha. I’ve never actually used Integromat to change checkbox fields before — I should’ve tested before posting my post above!! Lol. Do you see what happens every time I try to post at 10pm or later?!?! :joy: :joy:

Valerie_Tate
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you, everyone, for chiming in. I really appreciate your help.

I definitely want to do the Integromat thing but so far have been unsuccessful. I do fine with Zapier, but Integromat is not nearly as self-explanatory. Having trouble figuring out how to do this. (I’d use Zapier except it doesn’t seem to have the right functions available).

Connecting to Airtable and the base is easy. But the options all look the same no matter whether I choose “search records” or “update a record” or anything else. Obviously, the formula is what makes the difference and that’s where I’m stumped. No speak formula. :slightly_smiling_face:

Since I’m just setting all checkboxes back to empty, is there a way to just tell it to change ALL records in the “Done” field to false regardless of current status?

Thanks again for everyone’s help!

Valerie

Thanks, @Jeremy_Oglesby. I’m going to try to get the Integromat connection working but if I can’t, it’s great to know there’s a simple “clear all” way to do it manually without having to go into each record (which is what I’m trying to avoid). Thanks so much for your help!

Valerie

Yes, just do a search on something that will always yield true, like simply a field name (or a formula field name) that will always have something in it.

This is what your scenario overview could look like (unless someone has an even better idea than this):

image

And for your 3 modules, they could look like this:

image image image

Actually, @Valerie_Tate, I stand corrected. I believe that if you leave that “Formula” field blank, that it will return all the records. I just tested this, and it returned all of my records.

You also don’t need the iterator module. Integromat automatically iterates through all records found in the “Search Records” module. The two Airtable modules alone will do the trick.

Valerie_Tate
5 - Automation Enthusiast
5 - Automation Enthusiast

Okay, based on y’all’s comments, here’s what I’ve got, but it’s giving me an error.

Here’s step 1:
Step 1

Here’s step 2:
step 2

Here’s the error:
error

What am I doing wrong?

(Again, thanks for taking so much time to help me out! Y’all rock!)

What?!? For real!? I’ve always been using the iterator module!! :grimacing: :grimacing:

I’m going to test now! Then what would we need the iterator module for?

You have to put the Record ID into the ID section, not your checkbox field. See my 3rd photo above.

For real. The first module in any scenario typically returns one or more bundles. For example, with a search module, there will be one bundle for each Airtable record found. Integromat then iterates through the bundles, feeding them one by one through the rest of the modules in the scenario. If any later modules create their own bundles, those will cause another level of iteration from that module onward with those results.

The Iterator module is meant to iterate through arrays. The example that the Integromat docs give is that you’re processing a single email message found by the first module, but that message has several attachments, which will be stored in an array. If you want to process each of those attachments individually, you need to iterate through the attachment array, passing each item to later modules (i.e. upload to Dropbox). To do that, drop in the Iterator module, having it grab the array from the first module, and then the rest of the scenario will run once per array item.

Wow, thanks so much for this, @Justin_Barrett! All this time, I was wasting my time using iterators! This is a game-changer for me. Thank you!! :slightly_smiling_face:

Valerie_Tate
5 - Automation Enthusiast
5 - Automation Enthusiast

IT WORKS!! Y’all are the bomb. Thank you so much for all your help!!