Create History of Multiple Fields
I have a somewhat complex idea (well it seems complex to me) and these forums have always steered me right.
I have a database of records connecting to a number of episodes of a television show. The database is broken down in tables based on the the portions of the show. The tables go SHOW->EPISODE->MEDIA.
In the EPISODE table, there is a pair of shipping fields. One field "ship_status" indicates if it is sent or sent revised and a "ship_date" field lists the date. What I would like to do is create a method in which if I modify both fields it will create an archived piece of information listing such. For example, if I shipped an episode on 8/17/12 it would say "Shipped 08/17/12" but if I later ship a revised version it would list the previous ship on 8/17/12 as well as the new shipment.
I have been able to create a field "shipping_change_log" that lists modifications of either field in a list with an autocalc text field that uses the following calculation:
shipping_change_log & ship_status & " " & ship_date & ¶
It works well except that it creates a list entry if I modify either field, and so I have entries that are half complete. Is there a more legant way to achieve this? The only stop gap idea I can come up with is to create an individual log for each field and place them side by side, but that seems clumsy.
Any help would be greatly appreciated.