FileMaker Meta Data Pt 3

Thursday 8th January, 2015

If you’re working with old databases, one thing you may need to confront is lack of consistency with primary key naming, but you can easily sort the problem with a fairly simple custom function. Anybody for interested in PrimaryKey($tablename;True)?

Recently I’ve been helping to refurbish a customer’s database. The database is live (ouch!) and isn’t data-separated. Anyone who’s worked on several large FileMaker solutions will already have their own views on how to do it, and working on someone else’s solution will have to fight the urge to ‘fix’ everything. Sort of second-system-syndrome by proxy I guess. But primary key naming, that’s an itch that needs scratching…

An ID’s an ID for A' That…

Naming conventions for fields are fertile ground for religious flame-wars, so proceed at your own risk over the next statement.

I don’t care what convention people use for naming fields, as long as:

1. it is a convention(!),
2. it’s consistent,
3. it’s concise,
4. it’s comprehensible (easily understood),
5. it’s computable (programmatically efficient),
6. and it doesn’t need me to learn Hungarian.

Oh, wait, point 6 might be a touch evangelical and detracts from the alliteration, so let’s stick with just the first five points, eh?

So for instance, you could use any of these,

id
individual_id
person_id
people_id
ID_PEOPLE
peopleID
ref
people_ref
...

and I’d be (reasonably) happy.

These,

code
individual number
individual_number
Person Num
PeopleNum
...

would cause me some angst. And in case it wasn’t obvious, I’ve listed that first set of candidates in order of preference. Why? Simplest maxim first…

User lowercase

Lowercase is usually just fine. I’m programmed to think that CAPS IS SHOUTING. It causes people real pain having to read ALL CAPS. So let’s say NO, NO, NO TO CAPS. (Sorry.)

Avoid spaces

If you need to do something with your filename later on (for SQL Queries say) then programmatically not having to deal with spaces is easier; if you need to present a field name to the user as part of a dialog, getting from individual_id to Individual ID is a trivial text transformation, and less likely to cause problems than using an injudicious field name in SQL. (Although you should probably be using custom functions for quoting field names in your SQL queries; I’ll get to that some other time.)

Use words that are more easily pluralised

Similarly, it’s easier if you choose words that you can pluralise with an ‘s’. It makes going from individual_id to the string “Individual” trivial, as opposed to say people_id to the string “Person”. (I realise that this seems an Anglo-centric point of view, but as someone who speaks languages other than English, not having to work out the plural of a noun is a recurring pleasure.)

Use the right word

I must spend about 5% of my time worrying over le mot juste.

For example, number or code are not synonymous with id, and frankly I don’t care what your thesaurus says (you do use one right?). ‘Number’ on it’s own is just that - a number. As part of a noun phrase it’s great for modelling specific ideas: phone_number is a great field name, as is fax_number and serial_number. (Wait, isn’t serial_number equivalent to id? Possibly, but id is more concise, and I’m sure you can think of real-life examples where a serial number does not always have the properties you get from an id field i.e. uniqueness.)

I’d probably be okay with ref, but even then, why not go the whole hog and use id?

id: The Weapon of Choice

So, yes, id is my weapon of choice. Why? Above all it is consistent. Without looking, I know the name of every primary key field in the database. And why is this important? Because a lot of my FileMaker scripts are generic; that is, they’re explicitly written so that you can cut and paste them between solutions. 10 generic scripts can do the work of 7n scripts, for n tables in a standard solution. But generic scripts need your solution to follow some straightforward conventions for table names, field names and layout names.

We are talking about field names here, but essentially the same rules apply for table names. Again, it’s a religious matter, and one for a separate post… but suffice to say, for the base tables in a FileMaker solution I prefer to opt for easily pluralisable table names, for same reason as above. Additionally, it deals with the problem of getting the string “individual’s name” from just the name field: for the individual table - yes, you’re way ahead of me - the field name will be individual::name, so it’s just a question of Substitute( fieldname ; ["::";"'s "]). (For the same reason, I dislike seeing fields called customer::customer_name. Redundancy? We’ve heard of it.)

The Fix

Okay, so now you’re au fait with the basis for my id fetish, how do we deal with, ahem, misnamed fields in our old solution? With a custom function! In fact we’ll need several custom functions (“Remember kids, it’s custom functions all the way down”)…

What’s our starting point?

Say we want a generic script for deleting portal records. We’re going to call our script from a button on the portal row. The portal, which points to, for example a customer to invoices table occurrence, already knows the id of the portal row record: it’s the value of the (for sake of argument) customer to invoices::Invoice Number field. ( Invoice Number? Michael-T-Murine!)

Our button setup dialog will look something like,

Perform Script["Delete Portal Record"; Parameter: customer to invoices::Invoice Number ]

where Delete Portal Record is the name of our script. For a generic script as well as the id value of the record, we need to pass in the name of the table. Again, for sake of argument, we’ll use a return delimited string, that is “tablename‚àÇrecord id”. (In real life I use parameter encoding functions and Alexander Zueiv’s cracking custom function for turning them into variables auto-matically, but that’s another story/post.) How do we get the table name? Enter our first custom function:

/*
Custom Function:
GetTableName( myField )
*/
Let(
    fn = GetFieldName ( myField );
    Left( fn ; Position( fn ; "::" ; 1 ; 1 ) - 1 )
)

The GetFieldName() call returns a fully-qualified field name as a string - “customer to invoices::Invoice Number”, and we use the Left() call to pull out just the bit before the double colons. (The Let statement just saves us having to recalculate the fieldname twice.)

So, now our button setup dialog looks like this:

Perform Script["Delete Portal Record"; Parameter: GetTableName( customer to invoices::Invoice Number ) & ∂ & customer to invoices::Invoice Number ]

Uh oh!

In fact, the string we pass in will consist of the Table Occurrence name, and the id value of the record. Our fix-it script could work with this, but as you see it’s just a list of Case statement tests. In order to save coding now, we’ll find out the basetable for the table occurrence, and use that for our Case statement.

(In case you’re already confused over Table Occurrences and Basetables, here’s another explanation to confuse you further: The basetable is the name of the table as it appears in the ‘Tables’ tab of the Define Database dialog, e.g. invoices. The table occurrences that you work with are the names for a basetable that appear in the relationship tab of the Define Database dialog, e.g. customer to invoices. Table occurrences are about the context. The data is always stored in the underlying basetable, but which data you see, depends on which table occurrence you start from and the relationships you follow to get to the data. Note to self: this is an explanation that needs working on!)

Passing field names

In the previous post, I used a field to keep hold of the basetable, but here we need to calculate it on the fly, which is no possible thanks to FileMaker’s built in SQL schema. Here’s what that snippet looks like:

ExecuteSQL (
	"SELECT BaseTableName
	FROM FileMaker_Tables
	WHERE TableName = ?" ;

	"" ;
	"" ;
	myTableName // the name of the table as a string
)

(Yes, I like to lay out my SQL queries using tabs. I just find it easier.)

So we can hand in “invoice to note” or “just those notes belonging to account records” strings (table occurences) to the function and it will give us the name (“note”) of the basetable. And truly, I have seen similarly named table occurrences before…

With that in mind, here’s the fix-it custom function:

/*
Custom Function:
PrimaryKey( myTableName ; FQFN )
*/
Let(
	[
		// calculate the base table name to save on tests
		t = ExecuteSQL (
			"SELECT BaseTableName
			FROM FileMaker_Tables
			WHERE TableName = ?" ;
			"" ;
			"" ;
			myTableName );
		pk = Case (
				t = "Activity" ; "Activity ID" ;
				t = "Agreement" ; "agreementID" ;
				t = "Document" ; "documentID" ;
				t = "Domains" ; "Domain ID" ;
				t = "Projects" ; "Project Number" ;
				t = "Equipment" ; "EquipmentID" ;
				t = "Supplier" ; "supplierID" ;
				"id"
			)
	];

	// return the ( possibly fully qualified ) name of the primary key for the table
	Case (
		pk ≠ "" ;
		Case ( FQFN ; myTableName & "::" ; "" ) & pk ;
		 ""
		)
)

Ignoring the SQL which you’ve already seen, the rest of the function is a simple series of assignments. If the basetable string is in the list, then it has a special case id field name, and if not then it will just id!

One slight twist - there’s also a function parameter FQFN which lets us ask for the fully qualified field name, or just the field name without the table occurrence prefix.

Now, in the old solution, I can cut and paste in my generic scripts , but wherever I see something like:

Set Field By Name [$tableName&"::id"; $id]

I can just use:

Set Field By Name [PrimaryKey( $tableName ; True ) ; $id]

to make it all work seamlessly.

I promise to put together a longer posting on generic scripting, and how far you can use reflection in FileMaker.