Blurring the Distinction between Schema and Data, part 2

Welcome back for part 2 of my recap of the session I presented last week at the 2014 Portland PauseOnError un-conference.

Recreating ScriptMaker

A recurring theme, as we saw in part 1 and will see again today here in part 2, is that you can store “runtime code” as data. The most extreme example I’ve encountered is a proof of concept created and presented by Dr. Ray Cologon at DevCon 2006, the Text Script Interpreter, a.k.a. TSI.

11-1-2014 3-44-59 PM

The idea is that the entirety of FileMaker scripting can (not should, but can) be represented and interpreted textually, either as records in a table…

11-1-2014 4-12-02 PM

…or simply as pure text.

11-1-2014 4-18-56 PM

I can only imagine the prodigious investment of time and mental energy required to design and build the “meta script” to interpret and run the code (a feat rendered even more impressive when you recall it was done using the FM 8.5 feature set).

11-1-2014 4-32-06 PM

One benefit of going to all this trouble is that every script run can be logged, like so:

11-1-2014 4-07-28 PM

I asked Dr. Cologon if he had anything to add, and he kindly responded:

While the demo served as a proof of concept (and to some extent, a provocation, perhaps), elements of the technique do have practical application and can be used to significantly reduce the number and complexity of scripts throughout a solution, while increasing the scope and operational flexibility of the solution. The motivation for creating the TSI was in part to demonstrate the logical end-point and its achievability – i.e. that you could actually have a solution that required only one main script (plus a couple of secondary handlers perhaps), and by doing so to encourage folk to consider when and how far along such a road they could or should venture.

One of the applications of the technique is that it can be adapted to provide end-users with a simplified scripting environment (and simplified scripting syntax) where they can compose simple sequences of commands and then have the solution execute them, without the developer having to anticipate all the combinations and sequences that users may require – and without users having to gain proficiency with the full gnarly complexity of scripting as *we* know it. Users can then create and save their own scripts as data, operating within a script vocabulary and functional constraints established by the developer, without ever having access to the “raw” scripting engine itself. So there are indeed real-world applications, albeit not necessarily for the full TSI implementation as shown in the demo.

Perform script via Open URL

Next we looked at a work-in-progress by John Renfrew where all scripts are called via “Open URL”, rather than via “Perform Script”. In the old days (prior to FM 13), we had to use a plug-in if we wanted to call a script by name, but now we can do it 100% natively in FileMaker. (Note: this technique requires FM 13.0 v2 or later to work consistently on all platforms, and in both standalone and hosted environments.)

This first example uses a hard-coded script name (“GoToTraineeDetail”), and nicely shows the structure of the URL.

11-3-2014 3-22-47 PM

The next example also uses Open URL, but in a more dynamic manner.

11-3-2014 5-41-09 PM

Here it is in layout mode. Each button represents a row in an invisible portal, and note that the “button” is actually repetition 1 of a repeating field containing the button label text, while rep 2 of the same field contains the script name.

11-3-2014 5-39-47 PM

When the user clicks the button, the script name is derived (from whichever row the user clicked in) like so:

11-3-2014 5-48-32 PM

And finally, this version checks the contents of a global field — if the global field is empty then a hard-coded script name (“GoTo THIS record”) is performed; otherwise whatever script name happens to be sitting in the global field will be performed.

11-3-2014 5-59-13 PM

This allows the same button on the same layout to do any number of different things, depending on circumstance.

Script Name or Script ID?

At this point I proposed it would be feasible to perform a script by internal ID rather than by name. FileMaker has ScriptNames and ScriptIDs functions, so, hypothetically speaking you might use those functions to populate a Scripts table, and then mark the scripts you wanted users to have access to.

11-3-2014 6-27-06 PM

You would provide access to those scripts via an interface available only to authorized users.

11-3-2014 6-28-56 PM

This would actually be a two column value list, with only the 2nd column visible…

11-3-2014 6-35-28 PM

…so the user would see the script name, but behind the scenes you would be storing the id. And then at runtime, you would convert the id back to the name…

11-3-2014 6-42-56 PM

…the main advantage being that your code would not break if a script were renamed (you can learn about the custom function here: ValuePosition: the Function FileMaker Forgot).

And while on the subject of script ids, I couldn’t resist mentioning Fabrice Nordmann’s amazing FM_Name_ID custom function, which translates almost any FileMaker object name into its corresponding id, and vice-versa.  You can read about here: Avoid Hard Coding (I also mentioned it here: Avoiding Brittleness).

xmCharts (Hard Coding vs. Dynamic Coding)

Next is an extremely basic example of storing runtime code in a table — so basic, in fact, that “code” may be too fancy a word. Here we are using xmChart to produce pie charts showing wine (and other) sales by country of manufacture for a given region and time period (we use the term “country” loosely as you can see).

11-4-2014 9-33-04 AM

The client wanted each country to always have the same color, rather than the color being assigned automatically by the charting engine. Fortunately xmChart is happy either way (to auto-assign colors, or to let you assign them), and while we could have hard coded the country/color combinations into a Case statement, we chose instead to create a table for countries and color codes.

11-4-2014 12-45-45 PM

During chart generation, a looping script builds up the $fill variable like so:

11-4-2014 12-10-20 PM

The $fill variable ends up containing something like this…

11-4-2014 12-16-22 PM

…and it makes its appearance near the bottom of this block of chart code. (One of my favorite things about xmCharts is they are created from blocks of code, so are easy to tweak, debug and re-use.)

pie chart calc 2

I probably would have been happy to always write my xmChart code in the above manner, but I happened to strike up a conversation with a fellow xm-enthusiast, Martha Zink. She pointed out that the recipe for each type of chart one might want to generate could be stored as a record in a template table…

11-4-2014 12-35-11 PM

…and then at runtime, with appropriate substitutions, the template could produce the chart code…

11-4-2014 12-35-37 PM

…resulting in a chart like this:

11-4-2014 12-45-23 PM

Or (same data, different template) this:

11-4-2014 4-45-04 PM

This will likely be explored further in an upcoming FileMaker Hacks article, so I won’t go into any more detail now, but the demo file is available: xmCharting-POE-2014. You won’t need the plug-in to view existing charts, but to create new charts, or re-create existing ones, requires either an evaluation copy or a registered version of the xmChart plug-in — in the case of the former, you will see DEMO splashed across your chart in big letters for any charts you choose to generate or re-generate.

Faux Merge Fields

From there, we moved on to a more mundane use of this technique, which you can see in this bare bones demo file (runtime merge code). The technique is potentially very powerful, but the demo shows a minimal implementation.

There is a Templates table…

11-5-2014 7-42-41 AM

…and an Aliases table where the Input field contains faux merge fields and the Output field contains runtime code.

11-5-2014 7-44-42 AM

When the user chooses a template…

11-5-2014 8-27-34 AM

…this custom function translates the Input into the Output…

11-5-2014 8-20-47 AM

…with something along the lines of this as a result.

11-5-2014 8-29-56 AM

Note: for debugging purposes, if you hold down the Ctrl key during letter generation, you will see the merge data in red.

11-5-2014 8-34-59 AM

At this point the session was rapidly nearing the end, but we had time to take a quick look at two more uses of runtime code that have already appeared here on FileMaker Hacks.

User-Friendly Excel Exports

Along the lines of the previous example, this technique stores code in a table and facilitates exporting data from FileMaker to Excel…

11-5-2014 10-23-32 AM

…for example, from this…

11-5-2014 9-50-46 AM

…to this (note that row 1 contains the user friendly column headings):

11-5-2014 9-59-50 AM

Here are the original articles:

Virtual Calcs

And, finally, we wrapped things up by looking at “virtual calculations”…

11-5-2014 10-05-21 AM

…where calculation code is stored in a script, and loaded at startup…

11-5-2014 10-11-23 AM

… and which I wrote about extensively last year.

A Few Final Words

Thank you to everyone who attended the session. And thank you to Matt Navarre, Gerald Chang and everyone else who made POE/Portland happen this year. It was a fantastic un-conference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s