Metadata is "data about data". In terms of your database records, it can include data such as when the record was created and who created it.

If we focus on creation metadata, there are a number of things that can get automatically recorded (or auto-entered) when a record is created. These are set in the field options when defining a field. If you create a new text field, you can choose from the Auto-Enter Creation:

• Date
• Time
• Timestamp (date and time)
• Name
• Account Name

The date, time and timestamp are all taken from the guest computer. This may be an issue if the clock on the guest computer is wrong or if guest computers are in different time zones. So if you want consistency in the record creation date and time, it is better to use an auto-entered Calculated Value such as:

Get ( CurrentHostTimeStamp )

This will get the timestamp from the FileMaker host which will be the FileMaker Server for most database solutions. If this is used in a date or time field, it only records the date or time respectively.

An area of confusion is the difference between the auto-entered Name and Account Name. The Name comes from the User Name set in the FileMaker Preferences for the computer where FileMaker Pro is installed. It can use the system name or a custom name entered in the Preferences. It is of limited usefulness and can easily be changed by the user.

The Account Name is a more accurate reflection of who created the record if accounts have been set up such that every user has their own account name and password to access the database.

There is a lot more metadata that can be collected on record creation. This can be done using various Get functions and the Auto-enter Calculated Value. Some of the more useful Get functions are:

• Get ( AccountName )
  - the authenticated account name being used as described above
• Get ( ApplicationVersion )
  - text representing the FileMaker Pro application version used to access the database
• Get ( PrivilegeSetName )
  - the privilege set name used by the user account
• Get ( SystemIPAddress )
  - the IP address(es) of the guest computer
• Get ( SystemPlatform )
  - a number indicating the current platform (Windows or Macintosh) of the guest computer

Have a look at the FileMaker Help to find out exactly what these functions return.

Rather than collecting each separate piece of metadata in a separate field, you may consider collecting all in one field with an auto-entered calculation such as:

List (
  Get ( CurrentHostTimeStamp ) ;
  Get ( AccountName ) ;
  Get ( PrivilegeSetName )
  )

This will record a return-separated list of metadata in the field. When needed, you can extract the various metadata elements with a calculation like:

GetValue ( creation metadata ; 2 )

The code above will return the second value from the list in a field called creation metadata. This will work fine as long as you can be assured that each Get function will return one and only one value. This is true for the above calculation. However, if the value could be blank or if there could be multiple values returned by a function, you will need to use other methods such as named value pairs and substitution of return characters to make sense of the data as a list. For more information about named value pairs, see this blog from Nick Orr of Goya Pty Ltd.

Finally, when setting these options, it is useful to check the option to "Prohibit modification of value during data entry". This will prevent a user from directly changing the auto-entered data in a field on a layout. However, be aware that this should not be considered security for the field since the data can still be changed by a scripted action such as Set Field. To fully secure the field, it should be set as View Only in the Privilege set (Custom Record Privileges > Field Access).

While metadata takes up space in your database, it can be very useful to keep track of the when, who and where of record creation.

Check out my twtpoll and have your say:

What are your favourite new features in FileMaker Pro 10?
(select all that are important to you)

http://twtpoll.com/wm66p1

Before FileMaker Pro 10, you could work with a sorted list and edit records in that list without the sort order changing. If records in the list were sorted by say company name, and you changed a company name, the records did not change position. This was standard behaviour for sorted record lists in FileMaker Pro 9 and prior versions. The records became 'semi-sorted' as you edited them and eventually 'unsorted'.

This behaviour was changed in FileMaker Pro 10. There is now the standard maintenance of the sort order. When changes to a record are committed, the record may be moved in the list. The edited record is still the current record but it may have been moved off the screen. This can be disconcerting when the record you are editing disappears, and it means you do not have a list of record changes in front of you.

We can fix this problem but only after we have submitted a feature request to FileMaker Inc carefully explaining why it would be better if this 'feature' was an option rather than the default. After that, we put a temporary fix in place and wait.

What we do know is that this behaviour does not apply while a script is running. This was explicitly engineered so that scripted processes that loop through sets of records would behave as expected. See the FileMaker Knowledgebase article 6981 for more information. We can take advantage of this supported behaviour in our solution.

What we would set up is a script that jumps into an indefinite pause in a script. While that pause is in effect, we can edit records and they will not be re-sorted. When finished, the pause can be cancelled. So the minimum script you might need is:

Allow User Abort [ Off ]
Pause/Resume Script [ Indefinitely ]

When the script resumes, it finishes.

There is a video tutorial to go with this article. The video extends the script a little to add some features.

Watch the video here.