"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Document Management Videos



Become a patron of this FREE web site!


Recent Blogs:

Currency Formatting
Currency Formatting

Gathering Portals
Gathering Portals

Multiple Choice Picker
Multiple Choice Picker

Popups and Pickers
Popups and Pickers

Window Locking
Window Locking


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


Create a FileMaker Calendar


Quick Tip:

Enter Text Format Quicker
I've never seen this documented, but just today, while in Layout mode, I double-clicked a field while pressing the Option key on my Mac and, suddenly, I was in the Text Format dialog. Iíd always hoped that there was a shortcut to get there, but I kept expecting to see a key-combination. Who knew? You can do the same under Windows by holding down the Alt key. Compliments of Reeva Golub.



Create a FileMaker Calendar


Fun Stuff:

Hall of Fame
I started working with FileMaker 25 years ago and a lot has changed. Here are the folks I think need to be recognized for their effort in shaping the early FileMaker (in no particular order):



Create a Password Management solution!







RSS Feed
Scripted Change Log
Level: Intermediate
Version: FileMaker 19
Category: Scripting
Tuesday, July 13, 2021
Scripting a change log (Audit Log) table has been around since script triggers were introduced in FileMaker 10. But, do you know the right way to script a field modification log? I've been programming them for years and have gone through every version of them and have settled on what I'm going to cover today. We'll start off with the wrong way to script an event log so you can get some perspective but will end up with a dynamic solution that can be copied and pasted from FileMaker file to FileMaker file.

Scripted Change Log

Previous Article
This article is a second in a series of articles on how to track field level changes. It's not necessary to read the first article but I thought I'd let you know about it in case you are trying to come up with the best solution to log changes.

Abstracted Log

The Overview
The idea with this solution is to use script triggers to track any changes to specified fields. The log will be entered into a table with each field being a record. This will facilitate reports and searching and even roll backs, if desired.

The Wrong Way
It's not that the wrong way doesn't work or fails in a multi-user scenario, it just has shortcomings. Let's start by covering the approach, identifying the issues and then solve the stated issues. Let's start with the script:

Scripted Change Log

The first step freezes the window because a new window is opened, manipulated and then closed. In older versions of FileMaker, the opening and closing of a new window could cause undesirable redraw screen flashes. Screen flashing could also occur on Windows, given how Microsoft redraws the screen as compared to the Macintosh. I'm using FileMaker 19.2 on a Macintosh running Mojave and didn't notice the difference when freezing or not. What I'm trying to say is, if you don't need the Freeze Window script step, then don't use it. FileMaker has gotten a lot better over the years at reducing temporary window redraw so take advantage of it and make your script shorter. It may even make your script run slightly faster cause the window is not being frozen and then redrawn.

Next, the script sets two variables. The first is the primary key uniquely identifying the current record. The second grabs the active field name and concatenates it with the current contents of the field. A return separates the two values, making it very easy to parse, as you'll see later. The result of the $Field variable might look like the following (the invisible carriage return is shown):

John¶
John Mark


A new window is created next, including a context change to a layout based on the table where the changes are being logged. In the included example file, this table is called LOG. The example file also includes a simple CONTACTS table to serve as the table being tracked. Now, I could have simply switched layouts without the new window but a lot of contextual problems will occur. The active field, active tab or slide control and/or active portal row will be lost, causing the user frustration. Even though opening a new window could cause screen flash, it's better than losing context.

Now that the LOG table is selected, a new record can be created and populated. First, the foreign key is populated so the logged change knows where it came from. Second and third, the values from the $Field variable are parsed easily with the GetValue function and placed in their respective LOG fields.

The last step is to close the window. Again, opening a new window is one of the easiest ways to preserve the context of your first window. It doesn't matter how many layouts are visited, finds are performed or fields are set, the original window will remain untouched with the active field, tab or slide pane and the active portal row.

The Trigger
Once you understand the script, it's time to look at the potential script trigger choices:

OnObjectKeystroke
OnObjectModify
OnObjectValidate
OnObjectSave
OnObjectExit


All of these script triggers will fire if a field is modified so which is the right choice? We can cross off OnObjectKeystroke and OnObjectModify since they will fire every time a key is typed. What we want is for the script to initiate once and only when the field is exited. All three of the remaining triggers fit the bill but let's take a closer look to see which one is the absolute best.

OnObjectExit is not a good choice because it fires every time the field is exited. No change log should be recorded if someone simply tabs into the field. That leaves OnObjectValidate and OnObjectSave. While OnObjectValidate will only fire if the field is modified and only upon exiting the field, it fires the script prior to processing the event that triggered it. After many tests, it determined this makes absolutely no difference in this situation. However, I have chosen OnObjectSave simply because I don't want to get in a situation where the script fires prior to the event and I'm not really validating anything.

With the OnObjectSave script trigger in hand, apply the script to every field you want tracked. If you don't want to track a field, don't attach the script trigger. To make things faster, select all the fields you want track and Command-Double-Click (Mac) or Ctrl-Double-Click (Win). This will take you to the object script trigger dialog where you can set all the fields in one shot.

The Issue
Seems like a straightforward solution that works great. A potential context issue was resolved with a New Window step even before it was a problem. So, what's the issue? The problem is the approach of logging each field as it is modified. Imagine a user trying to do data entry and each time he tabs to the next field, a script fires. This will definitely slow down the data entry process. This solution is no bueno.

The Resolution
A better solution is to write all the changes to all the fields upon committing the record, essentially hiding the script in the Commit so it doesn't interfere with data entry. Committing a record occurs when navigating to another record or layout, closing the file, clicking somewhere outside the fields or typing the Enter key on the keypad. A commit can be captured in any form using the OnRecordCommit layout trigger. A few changes will also be made to the script and a new script will be added.

The first script runs in the same scenario as what was covered earlier (OnObjectSave). It's still a one line script but the record goes into a variable rather than directly into the LOG table. Here's what it looks like:

Get(ActiveFieldName) & "|" &

Get(ActiveFieldContents) & "|" &

CONTACTS::_kp_contacts_id

&

Case(not IsEmpty($$Changes); "¶") &

$$Changes


You'll notice a lot of the same code as the previous technique. Get(ActiveFieldName) and Get(ActiveFieldContents) are still utilized but are separated by a pipe character rather than a carriage return. Since all the modified fields from the current record will be stored until the record is committed, each value is separated by a pipe character and each field change by a return. After the primary key is added to the list of values, the final line concatenates everything to a global variable titled $$Changes.

$$Changes is also the variable being set with the Set Variable step. This technique is called Append and allows a single Set Field or Set Variable step to build up a list of values. I've also selected a global variable in order to track the changes no matter how many times the script fires on a single record. Normally, I'd try to convince you guys to use a local variable to cut down on Data Viewer clutter but this is a good scenario to use a global variable.

FYI: The Case statement prevents an initial return from being entered when the $$Changes variable is empty.

As I said before, the script we just covered runs when OnObjectSave triggers. It works in conjunction with an OnRecordCommit trigger that takes all the data collected and parses it out to a LOG record. Here's the script:

Scripted Change Log

After the Freeze Window and New Window steps, the script enters a parsing loop. $Record grabs each value (returns designate a value) using the GetValue function. That value is then transformed into a return-separated using the Substitute function to change the pipe character into a return. Again, this allows for easy parsing using the GetValue function.

BTW: The script loops until all values have been parsed. The script knows when to stop when the $Counter equals the count of all the values in the $$Changes variable.

You might be wondering why I stuffed everything into a single variable and then parsed it. Wouldn't it be easier just to use separate variables? The answer is Yes and No. Yes, the script would be simpler with no parsing necessary. No, because now the Data Viewer would be cluttered. In a complex solution, the clutter will definitely be a problem. And, if you think parsing is difficult, get used to it. It's an important skill to have for all kinds of FileMaker jobs. Once you get good at parsing, what I've done here will seem like child's play.

TIP: With this technique, it's important to reset the global $$Changes variable once the data has been parsed into the LOG table. Normally, a local variable would clear itself so you wouldn't have to worry about line 12 of the script.

Bells & Whistles
Yes, we can make this solution even better! We'll be adding a step to track the previous content of the field as well as some indirection so you can copy and paste this solution from one FileMaker solution to another. Let's start with the easy step and show you how to preserve the current contents of the field. You'll need a new script and another trigger. The script is pretty simple:

Set Variable [$$Current; Get(ActiveFieldContents)]

This script is also attached to every field you want to track but with the OnObjectEnter trigger. All it does is capture the current value whenever you click or tab into a field. Then, all you have to do is add the global $$Current variable to the list of values being concatenated. But, before I show you the script, let's cover another value that will be concatenated. In order for the solution to be dynamic, so you can copy and paste from file to file, you'll need to record the table name. This can be done in a couple of ways:

Get(ActiveFieldTableName)

Get(LayoutTableName)


It doesn't really matter how you get the table name, just realize both Get functions return the Table Occurrence name and not the source table name. If you are properly implementing the anchor-buoy system then this won't be an issue. Otherwise, you'll need to determine a method for specifying the source table so the indirection will work. In reality, you don't need the source table name to make this technique work, it's just nice to have a consistent table occurrence name so the indirection works every time.

So, here's the formula from the script that sets the $$Changes field when OnObjectSave fires:

Let(

[@Table = LeftWords(Get(LayoutName); 1);
@Field = "_kp_" & Lower(@Table) & "_id"];

Get(ActiveFieldName) & "|" &

Get(ActiveFieldContents) & "|" &

GetField(@Table & "::" & @Field) & "|" &

Get(ActiveFieldTableName) & "|" &

$$Current &

Case(not IsEmpty($$Changes); "¶") &

$$Changes

)


So far, the changes have been pretty simple. Now for the hard part. Let's start with the Let function. Two values are declared. @Table grabs the first word of the layout name. This is yet another method for grabbing the source table name, as long as you have good naming conventions for your layouts. In my case, all layouts start with the name of the table, followed by whatever describes that layout. So, I might have the following layout names using my naming standards:

CONTACTS_Form
CONTACTS_List
INVOICES_Print


The second declaration in the Let function is @Field. It adaptively grabs the primary key field name for the current table, as long as good naming conventions are used. Together, these two Let variables allow GetField to identify the primary key field from the current table. As you look through the formula above, there is no reference to a field or table outside of the LOG table so it will adapt to whatever table is there. In other words, this solution will work with multiple tables!

The parsing script hasn't changed a lot but I'll show it just to be clear:

Scripted Change Log

Roll Backs
Wouldn't it be great if you could scroll through the changes and revert back to an earlier version of your record? It's really not that hard. It just takes a little more indirection and the Set Field by Name script step.

Scripted Change Log

FYI: I often speak poorly regarding indirection and the Set Field by Name script step, simply stating that it isn't needed most of the time. Well, in this case, the complexity of indirection is a great idea simply because you want this technique to be portable from one FileMaker solution to another.

The script begins with Set Error Capture because the record you are trying to roll back could be locked by another user modifying it. The next step is to attempt writing the record through a relationship. If the write is successful, a message is shown notifying the user that the roll back has been successful. Otherwise, the message tells the user that the record is locked.

Scripted Change Log

They key to the roll back working properly is the relationship. And, yes, a table occurrence for every table will be needed for this script to work properly. The Set Field by Name script step will use the data from the current LOG record to specify the correct field and table via a relationship.

MORE, MORE, MORE
You could even go farther and specify the file name so you can use this solution on a multi-file solution but I'm not going to go there in this article.

Caveats
Set Field will cause a problem with this script because a script trigger requires the field to be modified manually ( or at least an Insert type script step). When it is modified from the current layout, using a Set Field step, the OnRecordCommit step still fires but there is nothing in the $$Changes variable so you get an endless loop. The best solution is to add an If statement to stop the script from running if $$Changes is empty. On the other hand, Set Field's little brother, Insert Calculated Result, will allow the script to function properly since it acts on data in the same way a user would manually.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

Example File (available to patrons at $5.00 per month): Download here if you are a patron

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

Massimo Maioli 10/13/2022
  Hi Mark, awesome post, i learned a lot of things. I looked on patreon but i did not find the demo file of this post.
Bob Marlee 08/14/2021
  If you use List() you can lose much of the manual concatenation in your variable calculation,as well as the Case() that conditionally appends a line break.
Response by:   John Mark Osborne 08/19/2021
Good point. Thanks for sharing.

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!