Hi there. I’m new to Coda and am trying to figure out a formula to automatically delete a row 7 days after a particular date. I have a column called “Publish date” and I want it to delete 7 days after that date.
Right now I have an automation as follows:
yes @Aaron_Sheinin
in step one you define the time : every day at a certain time
in step two you enter the code as written and you replace TheDate with the name of your column.
there is no condition, thus indeed two steps.
once you try and it does not work for you, please let me know
cheers, christiaan
Awesome. That makes me think the third step was probably causing the error. I’ll test it and let you know. I thought it was requiring me to create a third step.
As another approach, you can add a page button that will delete any row older than 7 days.
Unlike automation, this solution has no monthly limit quota.
Christiaan, your version did work, although it didn’t seem to do anything. So added this as Step 3 and it did work. Thank you so much for all your help.
I am glad it works with step 03 @Aaron_Sheinin !
I tested it that morning and used two steps (as far as I can remember, I delete all these temp pages shortly after they worked). now you are set for a lot of other great adventures in and with Coda, enjoy the ride!
i know this isn’t exactly what you are looking for… but it is an alternative that has been used in databases forever… a Very Olde Trick…
you place an IsDeleted checkbox column in your table(s) and set filters on all user views so that rows with IsDeleted=true are never shown or used in calculations etc.
so when you set the IsDeleted checkbox, that row is ‘logically’ deleted as far as users are concerned. but the row remains in case it is needed.
then a regular automation is run to physically delete those rows to save space and improve performance.
or, if they are never culled, they are kept as a logical ‘archive’ sub-table.
this may seem like a lot of extra work and complexity, but there are cases where the data is so critical, that instant physical deletion is considered too risky. (and recovering deleted rows using the document’s history is NOT easy nor straightforward).
anyway, this technique has one other advantage; the logical deletion can be done by a column formula, it does not need a button or automation.
so a formula in the IsDeleted column can automatically mark rows as deleted using any If() logic that is required… and the deletion occurs instantly when the conditions are met.
and we often make the IsDeleted a date&time column instead of a boolean, so we can ‘time-stamp’ the moment of deletion. if it is blank; the row is live; if its set, the row is deleted. and the timestamp can be used to cull the logically deleted rows after 7days via a nightly automation.
anyway, just throwing that out there, in case its useful to anyone.
Sultan, I’m not necessarily in the table every day and I don’t want my other team members to have the ability to push that button. They have already proven themselves more than capable of screwing up the table in other ways.
Max (great name BTW. My eldest son is a Max), I definitely like the idea of saving those rows in some way. I’m trying to figure out the archiving function. Would leaving those rows “hidden” as you describe still slow the table down as if they were still visible?
its not the row-count that slows things down, but the execution of formulas. and thats complicated.
many formulas are only recalculated when something changes in the row they belong to.
some formulas need to recalculate over every row on the table.
if you put the expensive formulas inside an If(IsDeleted,‘’,…) condituon, then they never recalc when the row is marked as logically deleted.
but the safest thing to do is to run a nightly automation that archives the logically deleted to a cross-doc table in an archive document, and then removes them from the live document.
that way, you keep an archive but it dont slow down the live doc.
