Audit Trail of a different kind

[PsyberDave]

I am looking to implement an audit trail in my database, but I want it to be different from the standard audit trails I have seen. I need help and would greatly appreciate some assistance.

Typically an audit trail is a text field with a calulation that copies changed data from other fields, concactenates those data, and appends it to previously stored data in the audit trail field. It might store data from several fields and tack on a timestamp.

I don't want to store a concatenation of several fields in one audit trail field. I want to write changed data to *respective fields* in another table altogether.

For example, I have a table of students. Each student record has a status field. Every time a user changes the status of the student, I want to record that change in a Status History table. Eventually I want to be able to display a history of status changes.

I know it would be simple to write a script to do this, but here is the twist: I don't want to use a script. I can't. The student data are displayed to the user as a table view, with Status as one of several columns. Users can enter and exit fields quickly and they do not have to manually save changes when moving between records. They like the speed of this design. The Status field contains a dropdown list of a handful of statuses that the user can choose from. I don't want to force the user to have to click a button to save the change. They should just make their changes and move on to the next field or record, just like they do for any other field changes. I want to record the Status change transparently.

Ideally, the solution would detect a change in the Status field and create a new record in a Status History table with a copy of the student's key record ID in the Status History table's Key Record field and copy the new Status to the Status field of the History Table (Of course a time stamp would go in the History Table too, but that's goes without saying).

Eventually I'll want to create reports that show status changes for students during certain date ranges. This will be so much easier to do if I can do a find on a table that has discrete fields for dates and statuses and discrete records rather than attempt to tease apart a bunch of information that was mushed together in a text field.

I don't know how to automatically create a new record in another table and write to it in a way that is automatic like a calculation is automatic.

HELP! -)

[The Big Bear]

ISO Filemaker Magazine online has something you might want to take a look at.

[PsyberDave]

I've searched the magazine online. Do you have something specific in mind?

[The Big Bear]

sorry

Super Audit Log does what you want but you need a script to transfer the information from the log to the separate fields.

To create another record in an table. When you create the relationship between the two table check the box "Allow creation of records".

Edited December 14, 2007 by Guest