In this article, I'll show you how I am using Script Triggers to log changes to records. This is a fairly common request that I get from clients. It is one thing to keep track of who created a record, when they created it, who last modified it, and when they did so -- we've been able to do that for quite some time using Auto-Enter fields. But with Script Triggers, we are now able to track the actual field values as well.
As the basis of this example, I am using the sample Contact Management database that is provided as a template with FileMaker Pro 10. This is a fairly basic solution, which keeps track of contacts and allows the user to attach notes to each contact.
Our goal is simple: When a new contact is created or an existing contact is changed, we'd like to attach a new note to the contact that lists all of the fields and their values. So, by looking through the notes, a user will be able to see detailed information regarding how the record has changed over time.
Developing the Script
The first thing that we will do is create the script that will run when a record is created or changed. The script that I wrote (titled "Contacts - Trigger - Log Record Commits") looks like this:
[ 1] #Grab the ID of the contact.
[ 2] Set Variable [ $ID_Contact; Value:Contact Management::k_ID_Contact ]
[ 3] #
[ 4] #Get the field names and values of all fields on the layout.
[ 5] Set Variable [ $FieldNames; Value:FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
[ 6] Set Variable [ $Audit; Value:"Audit Information..." ]
[ 7] Set Variable [ $i; Value:1 ]
[ 8] Loop
[ 9] #Ignore fields that are from related records.
[10] If [ PatternCount ( GetValue ( $FieldNames ; $i ) ; "::" ) = 0 ]
[11] Set Variable [ $Audit; Value:$Audit & "¶" & GetValue ( $FieldNames ; $i ) & ": " & GetField ( GetValue ( $FieldNames ; $i ) ) ]
[12] End If
[13] Set Variable [ $i; Value:$i + 1 ]
[14] Exit Loop If [ $i > ValueCount ( $FieldNames ) ]
[15] End Loop
[16] #
[17] #Create the note.
[18] Go to Layout [ “Notes_Utility” (Notes) ]
[19] New Record/Request
[20] Set Field [ Notes::kf_ID_Contact; $ID_Contact ]
[21] Set Field [ Notes::Text; Notes::Text & "¶" & $Audit ]
[22] Commit Records/Requests [ Skip data entry validation; No dialog ]
[23] Go to Layout [ original layout ]
Here's what the script does:
On line 2, we grab the ID of the contact that has either been created or changed. We need to do this so that we can attach the note to the correct contact.
Next, we need to grab both the names of all of the fields on the layout, as well as all of the field values. Before we do that, we need to do some prep work. On line 5, we store the names of all of the fields in a variable named $FieldNames, and we do this using a combination of the FieldNames, Get ( FileName ), and Get ( LayoutName ) functions. On line 6, we initialize a variable named $Audit, which we will use to build the contents of the note. On line 7, we initialize another variable ($i) which we will use to keep track of where we are as we loop through the list of field names.
With all of the prep work out of the way, we're ready to loop through the list of field names. The loop starts on line 8 and ends on line 15. On line 10, we check to see if the name of the field has two colons in it. If so, then this is most likely a field from a related record, and we aren't going to include these in our audit info (although you certainly could if you wanted to). Line 11 appends the name of the field and the field value to the $Audit variable that we set earlier -- and we use the GetValue and GetField functions to get this information. Line 13 increments our pointer variable ($i), moving us to the next name in the list of field names. Line 14 checks to see if we're at the end of the list of field names, and if so, we want to exit from the loop.
The final step is creating the note itself. On line 18, we jump to the Notes_Utility layout, and on line 19 we create a new record. Line 20 sets the "kf_ID_Contact" field with $ID_Contact, which essentially creates the link between the contact record and this new note record. Line 21 sets the "Text" field with the the audit information that we've just built up into the $Audit variable. On line 22, we commit this new record. And finally, on line 23 we jump back to the original layout and display the contact.
Setting Up The Script Trigger
With the script in place, we're now ready to use it . To do so, we go to the Contact Management layout, enter Layout mode, and select "Layout Setup..." from the Layout menu. A new tab on this dialog box is titled "Script Triggers" and that's where we'll setup the trigger.
The event that we want to attach our new script to is "OnRecordCommit." This event occurs whenever an attempt is made to commit record changes via this layout. This includes commits of both new records and existing records. Note that, in the case of commits to existing records, if no data has changed and the user attempts to commit the record anyway, then the event does not occur.
Using the Layout Setup dialog box, we attach the script to the event. See below.
Testing
Go ahead and test the new script trigger by creating new records and making changes to the existing records. When you do so, you should see that new notes are automatically created that include the audit info. Here's an example:
1/10/2009 9:40:38 am by Tim Dietrich
Audit Information...
Street2:
Address_Type1:
Street1:
City1:
State_Province1:
Postal_Code1:
Address_Type2:
City2:
State_Province2:
Postal_Code2:
g_Similar_By: Name
Name_First: Timothy
Name_Last: Dietrich
Phone1: 703-555-5555 x100
Phone2:
Email: tim@xgravity.net
Instant_Messaging:
Title:
Company: Xgravity
Website:
Image_Data: TimDietrich - Forum Pic.jpg
UserName_Created: Tim Dietrich
Date_Created: 1/10/2009
UserName_Modified: Tim Dietrich
Date_Modified: 1/10/2009
Conclusion
This is a great example of how to use the new FileMaker Pro 10 Script Triggers functionality. However, please keep in mind that there are a number of other events that you can attach scripts to, so let your imagination run wild and I'm sure you'll quickly find a number of other practical uses for this new functionality!
