One of the little annoyances with database management is that a lot of database systems address items by their text names – the names the developers give them. For example, if I’m going to write a SQL query for extracting information, I have to include names (or aliases) that match what the fields and tables are called.
This situation works great, unless you have to change the name of an item. Then you have to be very, very careful to catch all the instances where that name appears. Otherwise, you’ll break parts of your code (and it can be very hard to trace down).
One of the beautiful things about FileMaker is how it keeps track of all your stuff, not by the item’s name, but by an internal ID. So, if I’m using default FileMaker structures, I can change names of objects all day long and not break anything.
Now, that does come with a bit of a price. In many cases, you have to hard-code those objects into your system. That can lead to some problems with flexibility. If you follow our blog, you’ll know I’m a fan of abstraction and indirection, where you pull away from the hard ties to database objects.
The flip side of abstraction, though, is now you’re using plain text names for objects – and that can put us right in the same boat with other systems, where code breaks after items’ names are changed. Wouldn’t it be great if you could have the best of both worlds?
Fortunately, you can. I was introduced to the concept of “wrapping” parameters in functions some time ago on another blog (and, sadly, I can’t remember whose it was, or I’d give credit). The author was using this little gem:
GetFieldName ( {field object} )So, a real call might look like:
GetFieldName ( client::firstName )
This function returns the string, “client::firstName”. You might say, “So what? I can just type that.” Ah, but if you were to change the field name to, say, nameFirst, then a raw string of text will no longer evaluate properly. By using GetFieldName, FileMaker takes care of the work for you, because it’ll automatically update the field the function acts on.
Okay, well, so if I need a field name, I wrap it in GetFieldName and that solves all my indirection problems, right? Not exactly.
At this year’s Developer Conference, I attended a session by Martha Zink, of Soliant Consulting. She demonstrated how to wrap SQL queries to avoid them breaking. A normal query might look something like this:
SELECT client.firstName, client.lastName
FROM client
WHERE client.firstName LIKE 'a%'
This is just held in raw text, usually, so if you change the field names, it breaks. What Martha demonstrated was how to wrap the field calls so they update automatically if you change the names. Her technique looks like this:
Let ( [
theFields =
cfSQLSafeFieldName ( client::firstName ) & “, ” &
cfSQLSafeFieldName ( client::lastName ) ;
theTable =
cfSQLSafeTableName ( client::firstName ) ;
theWhere =
sqlSafeFieldName ( client::firstName ) & ” LIKE ‘a%'”
] ;
“SELECT ” & theFields & ” ” &
“FROM ” & theTable & ” ” &
“WHERE ” & theWhere
)
When it resolves, the query looks like this:
SELECT "client"."firstName", "client"."lastName"
FROM "client"
WHERE "client"."firstName" LIKE 'a%'
So, what’s with all the quotes? That’s an additional benefit of this particular technique – it automatically escapes field and table names to prevent the query from breaking if the field or table name is illegal in SQL space, or if it contains a reserved word.
I modified Martha’s basic Custom Functions with new names and a couple of minor tweaks. You may also have a need to extract the table or field name from a field object without the quotes, and I built some Custom Functions for that, too.
Just in case you’d like to use this technique, I’ve attached our CustomFunction library database. It contains the CFs you see above, plus lots more.
I have found this wrapping technique invaluable, especially when working on larger systems. Being able to abstract parameters without worrying about things breaking is a huge time saver.
If any of this looks interesting to you, please don’t hesitate to contact us. We’d love to explain further.
