FileMaker Meta Data Pt 2
Wednesday 7th January, 2015
In a retconned part 1, I wittered on about IDs and serial numbers. Now it’s time for my take on creation and modification dates.
FileMaker already does these right? Well, yes, but there are couple of tweaks worth taking the time over.
Let’s start at the end…
Okay, for the doubting Thomasinas out there, here’s the spoiler shot.
FileMaker makes it easy-peasy to create a field that stores automatically entered dates and times, and indeed timestamps, for when a record is created or modified. Double click on a field in the Manage Database dialog, or select on and click on the Options button that lights up, tick one of the checkboxes at the top of the Options for field “blah” dialog, and voila. One auto-updating time, date, or timestamp field. What else could you possibly want? And err, hang on, by the way… that’s an awful lot of fields - why do you think you need so many?
We’ll begin with the only plain-vanilla example, the ‘creator’ field.
Perhaps the first thing to note is the name of the field. I’ve wrestled with naming fields for years, and ‘creator’, ‘created’, ‘editor’, and ‘edited’ are the best one-word field names I can come up with.
Account Name vs. User Name
Secondly, note that we’re using Account Name and not User Name. User Name is what the client software gets from the Preferences dialog - Account Name is the account that was used to access the current file. In the past, in non-multi-user solutions, this wasn’t much of a problem, and you could probably use them interchangeably. But if ever going to be using it on a plain server, or a server relying on single-sign via an external server, or using via WebDirect, then Account Name is the way to go. Indeed, as the manual indicates:
In FileMaker WebDirect, Insert Current User Name pastes the current user’s account name, not the current user name.
The ‘creator’ field is straightforward because when a record is created, FileMaker knows exactly which account was active when the record was created.
(And almost forgot, don’t forget the Prohibit modification of value during data entry checkbox!)
Getting creative with ‘created’
Which brings us on to the ‘created’ field.
FileMaker does not know exactly when a record was created. It might think it does, but it in a multi-user environment, FileMaker the client could be wrong. Or at least out of step with all the other clients on the network. Until I hear otherwise, the trick with creation and modification times are to use the time and date the Server thinks is correct. So we use a calculated value to get hold of the Server’s idea of ‘Now’.
And since this is a creation date, we make sure that it’s set to Do not replace existing field value of field (if any).
Mixed doubles, or a double date?
Which takes us finally to the real crux of the multiplicity issue. Why ‘edited’ and ‘user_edited’? Why ‘editor’ and ‘user_editor’?
Suppose you have some pre-existing data that needs fixing. You know, the sort of thing where you go to a layout, do a find, stick your cursor in a field, and use good old Records->Replace Field Contents… to clean things up with a quick calculation. (This never goes wrong, eh?)
Even when you got the calculation right first time, and you’re happy with the end result - whoops, there goes the neighbourhood! You may have fixed the original data problem, but now all the modification data for the records in your found set, and the blame for who modified, them point to you. So step 1 is to find some way to automatically update a field when a record is modified unless we specifically turn on an Ooops-don’t-look-I’m-fixing-a-screw-up type of switch.
And even when we’re not screwing around hot-fixing data, wouldn’t it be nice to know when a script modified a record as opposed to when a user futzed with it?
Hence the two pairs of fields. One pair for storing the modification data for just when a user does it, another for when the user or system does it.
Installing the Ooops-don’t-look-I’m-fixing-a-screw-up switch is relatively straightforward. Here’s the auto-enter calculation for the ‘edited’ field:
Let( trigger = GetField ( "" ); Case( $$do_updates ; GetAsTimestamp( Get ( CurrentHostTimeStamp ) ) ; edited ) )
That first line of the Let statement
trigger = GetField ( "" );
is pretty cool. Calling
GetField() on an empty string has the side effect of making sure that the auto-calculation gets called whenever any field on the record is modified. If that trick is new to you, let it sink in for a moment.
The rest of the calculation is a standard sort of auto-enter calculation, using Case instead of If. Think of it as:
If( $$do_updates ; do something ; do nothing)
It checks if a flag, the global variable
$$do_updates, is set to true, and if so uses the Server’s timestamp to update the modification date.
Aside: If vs. Case
Why do I prefer to use
If()? Essentially I hate nested
If()s, and when I’m writing a function or calculation I may want to come back and add another decision branch.
We update the field depending on the value of the global variable. Here it’s a positive test - and we need to make sure that we’ve set up startup script somewhere that sets the flag correctly, using a script step like
Set Variable[$$do_updates; Value:True].
Now, you might prefer to have updates happen by default, and make throwing the switch turn updates off, in which case you’d want to use:
Let( trigger = GetField ( "" ); Case( $$ignore_updates ; edited ; GetAsTimestamp( Get ( CurrentHostTimeStamp ) ) ) )
Just make sure that the global variable makes sense to you - in this case
In either case, every time a field is modified on the record we do a test, and either return the current value (‘edited’) or a new timestamp from the server.
Who’s the ‘editor’ around here?
If, like me, you’re worried about using fixed names in this sort of calculation (i.e. in case you’re worried about cutting and pasting code), self will work here just fine too.
For example, the calculation for the ‘editor’ would be similar:
Let( trigger = GetField ( "" ); Case( $$ignore_updates ; self ; Get ( AccountName ) ) )
What about the poor luser?
So how about the fields that show just the modification data for user edited content? - ‘user_editor’ and ‘user_edited’?
Again, they’re both auto-enter calculation fields. The calculations are slightly hairier though. Only slightly.
We need to make sure that the field update is only recorded if we’re outside of a running script. We can check for that using a simple test:
IsEmpty ( Get ( ScriptName ) )
No current script name, means no currently running script.
We also need to make sure that the record is being edited and not created. We only want to update the field if the user is editing an existing record. Luckily there’s another piece of FileMaker reflection,
Get ( RecordOpenState )
which according to the manual:
0for a closed or committed record
1for a new record that hasn’t been committed
2for a modified record that hasn’t been committed
We therefore ignore a
RecordOpenState of 1, and only worry about a 2. Putting it together, we get:
Let ( [ trigger = GetField ( "" ) ; not_script = IsEmpty ( Get ( ScriptName ) ) ; record_state = Get ( RecordOpenState ) ] ; Case ( not $$do_updates ; Self ; record_state = 1 ; "" ; // new record not_script and record_state = 2 ; Get ( AccountName ) ; Self ) )
The only trick here really is that first case - which stops the fall-through when the flag isn’t set. Remember if you’re going to use
$$ignore_updates then you don’t need the
not, so :
$$ignore_updates ; Self ;
is what you’d want.
Now that we’ve dealt with the modification fields, there are two more laggards to mention, hiding at the bottom of the Manage Database field list -
Say that I have a note table that I want to use to add notes to several other tables, a table that I want shared between tables so to speak. So with individual, invoice, and delivery say, I might need table occurrences such as individual to note, invoice to note, and delivery to note to show the notes in a portal on the appropriate record.
The structure might look something like this:
basetable field is the key to letting you get back from a note, (say in a portal on a home screen/dashboard layout) to the record the note belongs to. The
basefile field is only useful if you’re using a non-modular or multi-file, decoupled solution. In effect the note table has a three (or two) field foreign key.
There are other ways to do this of course (you can now use an ExecuteSQL calculation to determine the basetable of a record on the fly), but for most cases I feel this pre-13 solution does make the data more portable - especially here since the bastable lets you display what type of record the note in your home screen/dashboard layout refers to, without feeling too guilty about de-normalising your data.
Hope that’s been useful.