Delete records based on a rule

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
I have a table (History) that is tracking changes of another table (Projects). I want to only keep the 10 most current history records for a project. Is there a way to do this?
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
You will need to have a 'count' of the number of history reports, and know the record ID# of the max.  

Then once you hit the limit of 10, you can have a formula checkbox to mark the history report as "ready to be Deleted"

As far as making those delete automatically, you will either (1) need a server cron job, or (2) you can make a report of all the "ready to be deleted" records, and delete them periodically via the report.
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
Thanks for the reply, but I don't believe that will work for my need.
That will be way too much manual work for the scope. I will try to branch of that idea though. 
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Yeah, if its a lot of records, it can be tedious.

I'd recommend the code page route then.  
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
Going with blanket SQL statement to keep 30 days of history

DELETE FROM History
WHERE Date_Created <= '30 days ago';

Note: SQL statement given more of a template ;)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
This task is easy to accomplish with scrip and there is no need to comprimise your original goal of deleting all but the most recent 10 records.

Here is a working demo with (1) a button to delete all but the ten most recent records in Table #1 and (2) a second button to add new records to Table #1 (to keep the demo fresh for new visitors).

Delete All But Top Ten ~ Tasks
https://haversineconsulting.quickbase.com/db/bncb83cqt?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=615

Notes

(1) The basic algorithm is to obtain the [Record ID#]s for the first 10 records and then form a query that asks for all records where the [Record ID#] is not equal on of these ten values using the XEX and AND query operators. The resulting set of records is then deleted using API_PurgeRecords.

(2) You should use extra care in using this script to insure you do not accidentally delete records since API_PurgeRecords does not ask for confirmation before purging records. Take the normal precautions of working in a copy of your database and temporarily substitutie logging for deleting until you are sure you have correctly implemented your version of the script..

(3) The scripts are implemented through a Tasks table for convenience of the demo. In an actual deployment the buttons that invoke the scripts might be placed in a more localized part of the workflow.

(4) The script that adds records uses the underscore library to select 10 random records from a set of hard-coded list of fifty records. This is done solely to make the demo appear fresh although other users may have run the scripts during thier session.

(5) The scripts use backtick characters (ie `) for string interpolation
Photo of Chad Brandmire

Chad Brandmire

  • 968 Points 500 badge 2x thumb
Thanks for the reply. That does look like a good solution