Help

How can I auto delete a record after a specific amount of time

Topic Labels: Formulas
5432 12
cancel
Showing results for 
Search instead for 
Did you mean: 
NoCode_Nerd
6 - Interface Innovator
6 - Interface Innovator

Hi,
I am trying to get a record to delete after 24 hours.

I have copied a formula from another thread and changed the time from hours to minutes. I have no idea about scripting so wasn’t able to implement the second part of that thread.

I currently have the “delete” column set to add the word “delete” to the formula cell after 1440 minutes. This then updates another view filtered by “if delete is empty”. In other words, if delete is empty in the new view the record will show on the front-end currently Softr.

This formula seems to work but it’s sluggish and doesn’t update on the 24-hour mark.

IF(DATETIME_DIFF(NOW(), CREATED_TIME(), ‘minutes’) > 1440, “delete”)

My long-winded and probably not well-explained question is: How do I auto delete a record after a 24 hour period?

Thank you.

12 Replies 12

You can only automate the deletion of records with a script, with the API, or with an external automation tool like my personal favorite Integromat.

Integromat is a low-code point-and-click automation & integration tool that has advanced scheduling capabilities, so I would just create a scenario that runs every X minutes, searches for any records that were created greater than or equal to 24 hours ago, and then automatically deletes those records:

Alternatively, if you wanted to automate everything within Airtable without using an external tool, you would have to use scripting. There are several scripting experts in these forums, and you can use Airtable’s automations to schedule a script to run every minute. Although that will push you dangerously close to Airtable’s automation limit of 50,000 runs per month.

p.s. Airtable formulas using the NOW() function will only update once every hour or two (if your database is closed) with no precision or reliable predictability, so if you’re looking for precise 24-hour precision down to the minute, then using a formula isn’t the way to go. If you don’t care about exact precision, you could use a formula.

Excellent thank you. I was hoping to keep it within Airtable but I really do need it to stay up to date to the minute. I have heard of Integromat, I think I need to check out how I do it with Integromat. Thanks again.

Yeah, if you need it to check every 60 seconds to stay up-to-the-minute, then your best choice is to take care of it externally in a professional platform like Integromat, because Airtable only gives you 50,000 automation runs per month with no ability to increase the number of runs. Checking every minute could use up to 44,640 runs per month, which is dangerously close to Airtable’s inflexible limit. I wouldn’t depend on Airtable for this… I would depend on a professional tool like Integromat for this.

Just looking at Integromat now. Any advice on which route to use? Appreciate I’m asking a lot. I will learn how, if I can flatten the curve it’d be a great help. Cheers.

You just need two Airtable modules: “Search Records” and “Delete a Record”.

Schedule the search records module to run every 1 minute.

You’ll need to write a formula in Integromat for your search records module to search for the appropriate records in Airtable. You’ll also need a “creation time” field in Airtable to search. (Or you could use the CREATED_TIME() function in your formula.)

I don’t have time right now to help with that formula, but if you can’t figure it out, I can figure it out for you later tonight. Integromat support might be able to help you figure out the formula too, but their support often takes 3-4 days to get back to people.

No, I understand, I am asking a lot. I have found a video which I think will do the trick. You have saved me hours because I’d be struggling along in Airtable. Thanks again.

You’re welcome! Glad I could help! :slightly_smiling_face:

Update: Thanks for your help. I think I’ve figured it, two different ways:

  1. On Airtable I created a formula that calculated the time difference in minutes between the created date and the current time DATETIME_DIFF(NOW(), {Created Time}, “minutes”)

Then I created another formula that outputted “DELETE” when the minutes from {Created Time) exceeded 1440 minutes which is 24 hours.

Then I created another view that was filtered by: When Delete row is empty.

In other words, when a record exceeded 24 hours it would be removed from the live view which I can then use to display on my front end.

The problem with this is Airtable only updates every 15 minutes using the NOW() function.

If I knew how to automate updating the records in Airtable with Airtable automation I might be able to bring that 15 minutes down.

My homework, get better at writing formulas because I’m sure these two formulas can be condensed.

The second solution (much easier) is to use the Integromat integrations to search for records with a “Time” greater than or equal to “1440” minutes then use the delete record integration to automatically delete the record.

I can run this every 15 minutes, I need to figure out if this brings the time down as the two 15 minute periods may be staggered.

The dilemma I have now is:

I’m wondering if I want to permanently delete the records or archive them but that’s a problem for today.

I hope all of that makes sense?

Either way, thank you so much for your help, you got me thinking in a straight line.

NOW() only updates every 15 minutes or so if you are actively using the base at the time.

If nobody is using the base (and no automations are accessing the base), then NOW() only updates every 1-2 hours.

I can’t decide for you whether you should delete or archive your records — it all depends on what your needs are! (Note that Airtable only allows a maximum of 50,000 records per base, which could affect any archiving decisions you make.)

If you need to delete a record at exactly after 1440 minutes, you are not going to be able to use a formula field. As Scott has pointed out NOW() is very limited due to its infrequent updates.

If you are using Integromat to search for records, you will need to have Integromat provide the current time for the search, since you cannot depend on NOW(). You would also need a business account at $99/month to get enough operations to run every minute.

If you are okay with deleting records with more of a delay, you may still be able to use Airtable automations and a script, without relying on NOW(). If you run the script every 10 minutes (versus every minute), you would use under 4,500 runs per month, well under the 50,000 run limit.

If you want to experiment with deleting records in an automation, my “automation helpers” includes scripts for deleting an individual record or deleting records from a “Find Records” automation action. The automation helpers also includes a script for getting the current time that the automation is run. Note that these scripts will not get you all the way to your goal by themselves, but can give you a taste of working with scripts without having to actually touch the code yourself (which I assume you do not want to do, given your “NoCode_Nerd” name).

Sorry for the delay in replying. Thank you for an excellent explanation. There is quite a bit for me to work through there. I’ll come back and let you know how I got on.

Great, thanks Scott.