Delete records older than 3 days with automation

[Philippe-M]

Hello everyone,

I have a Budibase table with four columns:

• reference
• url
• status
• importUUID

The fourth column contains the date/time in microseconds of when the record was created. It's a table that stores temporary data. For maintenance purposes, I'd like to delete all rows older than three days using an automation.

I found (#14830) that using the Node Loop allows me to create a list of rows to delete, but I haven't figured out how to do this in the selection conditions:

importUUID < timestamps(now) - 3 days

Is there another method?

Thanks in advance.
Philippe.

[mikesealey]

Hey @Philippe-M

I've mocked up a quick table that might look a little bit like yours, as shown here:

Two of these rows are more than 3 days old, so they should get deleted if the automation is working correctly. While I'm here, I've exported the table to .CSV so I can restore it when testing my automation, using the Import feature.

I've then created a Cron-triggered automation, this will run at every specified time (you might want to run it daily, or use a different trigger like "Row created").

The first step of my automation is to Query Rows, with a filter.

Here, I'm using a filter on date_created, and checking if it's less than the result of a JS function that returns a date exactly 3 days ago.

const now = new Date();
const threeDaysAgo = new Date(now);

threeDaysAgo.setDate(now.getDate() - 3);

return threeDaysAgo;

Once you've got this far, you should just run a test of the automation to see what is output in the Query Rows step - mine returns two rows, which can be inspected in the "Data Out" tab

Next, you should add a Loop step, and inside it add a "Delete Row" step. In the Loop step, set the Binding/Value to be the output of the Query Rows step.

In the Delete Row step, you should utilise the "Current Loop Item", specifically it's ._id

Having run a quick test, I can see that two rows were deleted

But if I wanted to restore to the point before that automation ran, I could use the Import feature and provide the .CSV file we pulled exported earlier.

Hope this helps. Please let us know if you get stuck anywhere.

[Philippe-M]

Perfect !