AnsweredAssumed Answered

Create History of Multiple Fields

Question asked by ShaunHamid on Aug 27, 2012
Latest reply on Aug 28, 2012 by ShaunHamid


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.