Expand all | Collapse all

Delete records based on a rule

  • 1.  Delete records based on a rule

    Posted 12-21-2017 18:27
    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?

  • 2.  RE: Delete records based on a rule

    Posted 12-21-2017 18:34
    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.

  • 3.  RE: Delete records based on a rule

    Posted 12-21-2017 19:07
    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. 

  • 4.  RE: Delete records based on a rule

    Posted 12-21-2017 19:08
    Yeah, if its a lot of records, it can be tedious.

    I'd recommend the code page route then.  

  • 5.  RE: Delete records based on a rule

    Posted 12-21-2017 21:57
    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 ;)

  • 6.  RE: Delete records based on a rule

    Posted 12-26-2017 12:44
    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

    Pastie Database


    (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

  • 7.  RE: Delete records based on a rule

    Posted 12-26-2017 13:54
    Thanks for the reply. That does look like a good solution