FileMaker Field Utilization

A few years back I worked on a rewrite of an old solution. It had probably close to a thousand fields overall, and the file size was a few hundred megabytes. It was messy in all kinds of different ways. One of these was the myriad of fields that seemed to have been haphazardly added over the course of time, seemingly without any felt need for planning.

The result was fields which had, for example, values in five records, but no values in 624,321 records.

Well, one of the tasks in a project like this is to figure out which portions of the solution can be left behind, and fields like these seemed like good candidates.

The challenge was to figure out the extent to which each of the fields was utilized without having to do all of it manually.

With FileMaker 12’s ExecuteSQL function, this job is relatively straightforward. I’ve put together a little utility file that does this for us. It queries one of the FileMaker virtual tables – FileMaker_Fields – which I first learned about from a post by Andrew Duncan.

Here’s the query that I use:

SELECT    TableName, FieldName, FieldReps
FROM      FileMaker_Fields
WHERE     FieldClass = 'Normal'
AND       FieldType not like 'global%'
AND       TableName <> ?
ORDER BY  TableName ASC, FieldName ASC

This query returns all “regular” fields; i.e. fields that are not calculations, summaries, or globals.

It then iterates through each field and determines how many records have a value in that field.

The result is a set of data that reveals the utilization of each field. To use this in your solution, create a table occurrence for each of its tables in this file.

Here’s a link to the sample file

Take a look below for a screenshot of an analysis of a sample file, which I created using the Contacts starter solution, populated with a thousand records of fake data which I got from fakenamegenerator.com