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:
- Time based repeat every 12 hours
- [Combined News Budget].[Publish date].filter(Today()-days(7))
- Then: Delete rows
Table: Combined News Budget
Apply to: Custom filter
Custom filter: [Combined News Budget].filter([Publish date]= (today() -7))
It’s working, but too well. When I run a test it deletes every single row, no matter the date. Any ideas or tips on what I’m doing wrong?
Not an expert, but have you looked formula map?
hi @Aaron_Sheinin , welcome to the community. It is very well understandable that you don’t see it directly.
I see two options: you have an automation that deletes only boxes checked and how to check a box, see below:
the second option is to integrate the function as filter in your automation like below, you don’t need the check boxes in this approach.
hope it helps, Christiaan
Steve, I’m afraid I don’t even know what that is!
So, Christian, first thanks. Second, on the second option – is that just a two-step automation?
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
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.
Sultan, this is exactly what I figured out, too. It’s not ideal, but it does work.
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.
Step 3: [Publish date]< Today()-Days(7)
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!
Thank you for the feedback.
Can you explain why it is not ideal?
A button should output exactly what you require or are there cavities?
I am no Coda expert and I might be missing something.
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?
they may slow things down.
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.
Yeah OK. I’m working on the archive function. Thanks sir.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.