Expiring checkboxes

You can likely do this with a formula, in a formula field, by outputting emoji as either checked or unchecked. But to actually change the check/unchecked status of a checkbox field it would likely require a third party app like Zapier.

What are you trying to achieve?

Here’s a quick sample where I used a result of 1 or 0 to represent a ‘checked’ or ‘unchecked’ result. Formulas included in the field descriptions. You can replace the 1 or 0 with a check emoji, etc. Is this what you are looking for?

I believe that’s something like I’m looking for. I’m going to have 1000+ unique inventory rows. Some of the inventories will need to be checked daily while others weekly/monthly with a formula that dynamicly upgrades their importance to daily as needed. but because of the amount I want a quick way to cross-check the ones we need daily. I’m using a filter view+tag to sort the ones we need daily. I’ll still have over 500 I’ll need to check and keep track of so I’m looking for a formula where I can check a box on them on Monday and the box automatically unchecks itself, let’s say at midnight. This way I can track new and older daily inventories. Please let me know if this makes sense

Hi Alex

The best way to achieve this would be to use Integromat. You could use a Search records step (in a Scenario time to run at midnight) to select all checked records and then another Airtable step to update them all to unchecked.

Integromat will automatically process all records it finds and so this is really easy to set up - and I don’t think this particular process could be done with Zapier by the way.

Hope this helps!

Can’t seem to get the formula down for updating the searched records.

Hi Alex

It would be something like this:

{Checked} = True()

@alex_agi,

I do a lot of similar types of things in Airtable, where I have recurring checks that need to be done on things at certain intervals. I’m going to explain how I handle that, as I think it may be a better/more flexible option for you. It requires more work to set up, but once set up, it allows you to do a lot more, and does not require you to use an external automation service like Integromat. You can decide whether or not this will work for your situation.

You have your Inventory table, where each record represents some inventory object that you need to check.

I’d suggest creating a second table called Check Records; each record in this table will represent a check performed on an inventory object. It will have a field for the {Date} the check was performed (this can be a “Created time” field that automatically fills in the date when the record is created), and any other information you might want in in reference to a check-up; maybe “Who Did This” or “What was the result”. Or maybe you just need the date, and that’s all, but the date is the important field to have because we will use it to track your intervals.

Next, we will link each Check Record to an Inventory object. Create a field in the Check Records table that is a “Link to another record”, pointed at the Inventory table. Uncheck the “Allow linking to multiple records” option (we only want a check record to relate to a single Inventory object – but each Inventory object can have many Check Records).

Back in your Inventory table, we will need to have 3 fields:

1. A Rollup field called {Last Check} that finds the date of the most recent Check Record made for this Inventory object; the Rollup will point at the Check Records table, and the {Date} field, and uses this rollup formula:

MAX(values)

2. 
   

   A field that defines the interval required before the next check expressed as an integer and representing {Days Between Checks} - whether its just a Number field that you fill in manually, or a Formula field that dynamically calculates the interval you need is up to you.

   
   


3. 
   

   A Formula field that calculates the date that the next check is due, perhaps called {Next Check}. It can have a formula like this:

   
   

DATEADD(

   {Last Check},

   {Days Between Checks},

   'days'

)

Now you can create a View that filters to show Inventory records where {Next Check} /is/ /today/.

Marking an Inventory record as checked is as easy as creating a new linked record in the {Check Records} field. I do this quickly by 1) making sure the “Name” field of my Check Records can never contain the word “new” - perhaps easiest to achieve by making your {Date} field the “primary” field of a Check Record; and then 2) hitting Enter in that linked record field, typing the word “new” in the search box that pops up, and hitting Enter again (typing “new” filters the records to show those that match the search term – but if there are no matches, then the + Add new record button gets highlighted, enabling you to just hit Enter to create a new one).

If you only really have the auto-filling “Date” field in your Check Records, then that’s all you need to do. Once the record is created, the new {Last Check} and {Next Check} dates will automatically be updated, and your Inventory record will disappear from your “Due Today” filtered view.

Like I said, if you think that could be useful in your situation, then great. If not, no problem, do whatever fits your needs.

MAX(values)

DATEADD(

   {Last Check},

   {Days Between Checks},

   'days'

)

Would I have to manually link each new entry in the check records tab to each new entry in the inventory tab? I’d have hundreds maybe thousands of new entries coming in and out every week so it would be inefficient if it wasn’t automated.

Hi Alex

If you take this approach, you would either need to link the records or, if they were created from the Inventory table, the link would be created automatically. A bit more cumbersome than simply checking a box but not that bad.

The main issue will be the number of records created - you will probably want to delete these quite frequently (easy to do manually if you use a filtered view to show all the records to delete).

Like @Julian_Kirkness said, if you create them from within the Inventory table, they are automatically linked:

Also like @Julian_Kirkness said, if you are on a free account, this may not work well for you, as you’ll end up with lots of records. But if historical data is of use to you, like it is for me (I have to keep many of my records for 5 years to maintain accreditation standards), then this is a good way to ensure you have the entire history of checks that were made. You can easily create a view in the Check Records table that filters to show records Where {Date} /is on or before/ /number of days ago/ 365 or something like that, and then just delete everything in that view periodically.