History Tables

History tables are used to track changes. They are very useful in financial applications where you wish to allow flexibility to end-users but must still provide to-the-penny accuracy.

As an example, consider a doctor's office. In a small practice the office staff is going to prize simplicity above all else. (Just consider the success of Quickbooks if you need more evidence). If we gave them a rigorous accounting system many would rebel at the cumbersome constraints. Judicious use of a history table allows us to give them the flexibility to change anything while still keeping careful count of all activity.

In this example we will consider a table of patient payments. We will set up a situation which is no less than horrifying to a bookkeeper, we will allow the user to change any value at any time. The table might look like this:

table patpays:
    module: masters
    description: Patient Payments
    
    column recnum:
        primary_key: “Y”
    
    foreign_key patients:
        uisearch: “Y”
    column date: 
        uisearch: “Y”
    column amt:
        uisearch: “Y”

To keep strict records, we want to setup another table, a history table. We then want all changes in the PATPAYS table to be reflected. Here is what that looks like:

    foreign_key patients:
        uisearch: “Y”
    column date:
        uisearch: “Y”
    column amt:
        uisearch: “Y”
    
    history frompatpays:
        table_id_dest: history
        column date_old:
            retold: date
        # this is the magic.  Return difference between old
        #  and new values.  Can always be SUMed and GROUPed
        column amt:
            retdiff: amt