Blurring the Distinction between Schema and Data, part 1

The other day I had the privilege and the pleasure to give a POE presentation entitled Runtime Code, a.k.a. Blurring the Distinction between Schema and Data, in the room behind this window at the Ace Hotel in Portland, Oregon.

The overall goal of the presentation was to explore various ways one might move business or presentation logic out of its normal location in the schema layer, and into either a) the data layer, or b) some other, non-standard, schematic realm. An example of the former might be to store object names or calculation syntax as data in a table; an example of the latter might be to change the behavior of an object (e.g., a field or script) simply by renaming the object itself.

GetFieldName() and Get(ScriptName)

We started out by looking at a demo file from an article I wrote shortly after FM 11 was released, GetFieldName: New in 10, Improved in 11.

The full story is spelled out in the original article, but the basic idea in the demo is that you can define a field to use GetFieldName(Self) so that the field can know its own name, and use that knowledge intelligently — in this case to display three recent years of information side by side.

The field defs are identical but the field names (of course) are not…

…so when it comes time to increment each field by one year, you simply…

• rename pax_2013 to pax_2014
• rename pax_2012 to pax_2013
• rename pax_2011 to pax_2012

Along the same lines, if a user opened the file in FM 10 or earlier, I didn’t want to prevent them from doing so, but I did want to warn them that the demo would not function properly, so I included a subscript to test the FM version at startup like so…

…which provided an opportunity to make use of the Get(ScriptName) function. Of course the technique could be extended to include incremental versions, e.g., 13.0 v2, if you were so inclined. Mine just tests for the main version, but either way if you change your mind about the minimum version number, rather than updating code inside the script, you simply rename the script itself.

Runtime code, XML validation & more

Next up, Colleen Hammersley was kind enough to share some screen shots from a complex FM 11 solution. In the first example, the cost calculation logic is stored in a Formula field so that different items can have their cost calculated correctly. One benefit of doing it this way would be that authorized users could change costing logic simply by editing data in the portal.

Here is some XML constructed on the fly during the generation of a quote…

…which is then transformed to HTML and used to produce the quote in a web viewer (very cool) (!!!).

She also uses XML as part of a validation procedure.

And finally, I really like the way she uses grouping (“Hem”, “Pole Pocket”, “Lining”, “Grommet”) to make clear what would otherwise be confusing in a dynamic and complex fabrication process with varying attributes per item. Of course the attributes are data, but note that the attribute labels themselves (Width, Location, etc.) are also data in this example. This is a fantastic use of a portal.

International Addressing (runtime code)

From there, we moved on to a demo I built about ten years ago, after implementing the technique in a client solution.

The problem I was attempting to solve was how to properly format mail labels in the configuration expected by the postal system of the destination country. For example, in the US, Canada and Australia, we format our addresses like this…

…but in Mexico they put the postal code before the city and state…

…in England they don’t have states or provinces, and the postal code goes on its own line…

…and so on. It turns out there are at least 22 different ways the “city line” of a mail address can be configured (for more information, see Frank da Cruz’s Compulsive Guide To Postal Addresses), and approximately 250 different entities that qualify as a “country” according to the US postal service. What I ended up doing was creating an “international_addressing” table with 22 records representing configuration rules for 22 different flavors of city line…

…a “countries” table with 250 records which looked like this…

…and which functioned as a join table linking “contacts” to “international_addressing”.

Finally, the mail label was defined like so, with the salient portion highlighted:

Internal Layout IDs, part 1

Next I fired up a multi-file demo that I had originally presented to the DIG-FM user group in the year 2002, but subsequently updated to the .fp7 file format and then recently updated again with a few small changes to the navigation subsystem.

Have you ever watched one of those “making of” documentaries where the documentary was more interesting than the film it was based on? Well here we have an analogous situation. The original purpose of the demo (to explore FM 6 value list behavior in minute detail) does not concern us, but my attempt to simulate PowerPoint within FileMaker by having bullet points appear on demand, does. Of course we didn’t have tab panels back then, so this simulated tab control actually consisted of 15 separate layouts… 13 of which required bullet points.

Additionally there was only one record in the parent file (yes, file — bear in mind that this solution was originally created in FM 6, and recall that in those days each table required its own file). So I decided to use the internal id of each layout as a relational key, and while one could use a custom function, the syntax is non-recursive and we already had the requisite design functions back in the FM 6 days. Using modern syntax, here’s how you derive the ID for the active layout:

GetValue ( LayoutIDs ( Get( FileName ) ) ; Get ( LayoutNumber ) )

And as Mikhail Edoshin pointed out the other day on the FMP Experts list, you can use a pair of empty quotes in place of Get(FileName), so this would work just as well:

GetValue ( LayoutIDs ( "" ) ; Get ( LayoutNumber ) )

Why should we care about internal layout ids? Why not just use the layout name or the layout number? Because they’re brittle, that’s why. A layout name or number can easily be changed, but a layout id will never change (even if a file is cloned). So I made note of the internal id for each layout I wanted bullet points to appear on, e.g.,

…and then stored the bullet point text in a related table, like so:

The next step was to relate the main table to the bullet text table…

…and finally to place a portal on each eligible layout, like so:

Incidentally, the preceding is not the “standard use case” for internal layout ids. Typically they are used for navigation purposes, and I could not resist retrofitting the demo accordingly. As mentioned above, the  simulated tab control in this demo consists of 15 separate layouts. Since I had already made note of the internal id for each of these layouts, I wrote a simple navigation script…

…and called this script from every navigation button, like so:

You cannot natively go to a layout by id, only by name or by number, so the nav script translates the id back to layout number at runtime via this custom function.

Note: In this example the layout ids are hard coded as script parameters on each button, but the next example is more dynamic.

Internal Layout IDs, part 2

The next solution we looked at features a “nav array” used to store a) button labels, b) internal layout ids…

…and c) to facilitate the generation of appropriately named $$variables at solution startup. For instance, the highlighted row above produces a multiline variable named “$$Admin.0.4”, populated with the button label as well as internal ids for destination layouts:

There is also a button palette where each button, rather than passing a hard-coded internal layout id as a parameter, passes its own button number instead.

Clicking the button invokes the “navigation” script which among other things uses this bit of code to select the destination layout id…

…the result being that the button palette can be reused on many different layouts and if we want to change the behavior of a particular button, we do so by editing data in the nav array.

Support for Multiple Languages (dynamic variable instantiation)

The above solution also makes use of Matthew Leering’s Multilingual LabelMaker, which stores $$variable label names and corresponding text like so:

Normally $$variables are created via the Set Variable script step and the variable name must be hard-coded, but in this case, whenever the language preference is set or changed, the variables are created (or updated) dynamically, by looping through the records and invoking this code for each of them.

(For more information on this technique, see Dynamic Variable Instantiation.)

We end up with a collection of (dynamically generated) variables populated with the appropriate text like so…

…and which can be placed on layouts as merge variables…

…with this as the end result:

But Wait, There’s More…

At this point we’ve reached approximately the half way point of my presentation, the remainder of which will appear in an upcoming article. Stay tuned and happy FileMaking.