One of my favorite new features of FileMaker Pro 12 is the ExecuteSQL function, which allows you to perform a SELECT SQL query against your FileMaker tables. ExecuteSQL allows you to use SQL to create calculations that otherwise might be very difficult or cumbersome. For example, in the past, if you wanted to get the list of values that had been used anywhere in the field areaCode in your contact table, you had to first create a value list using that field, then create the calculation: ValueListItems ( Get ( FileName ) ; “AreaCodes” ). It worked, but it was a bit of a hack to have to create that extra value list.
Now, all you need is:
ExecuteSQL ( "SELECT DISTINCT areaCode FROM contact" ; "" ; "" ) /* The last two empty parameters tell FileMaker to use the default comma and carriage return as column and row separators */
Okay, so it does require learning a bit of SQL (check out the W3Schools’ SQL Tutorial). But many queries are straightforward, and using SQL this way can simplify the relationship graph.
This is my first week at The Support Group, and I get to kick it off with FileMaker 12!
FileMaker 12 is here and with it comes a new file format. However, this isn’t your father’s file format change (i.e. fp5 to fp7), full of architectural rewrites, modifying scripts, and changes in behavior. If planned for correctly, it is a fairly painless and worthwhile adventure, chalk full of performance increases and development possibilities (awesome layout themes, better and easier charting, more robust container field behavior and many more).
As with any file format change (whether it’s Word, Excel, or our FileMaker), you’ll need to be aware of some rules:
After you upgrade, give it some testing of course (this never hurts). Since you can’t run FileMaker Server 11 and Server 12 on the same machine, you may need to run separate servers while you test.
Follow these simple rules and you should have no issues, only joy. You can take my word for it, having myself converted and tested a 102 file, 8 GB solution that contains some really old school elements hauled all the way from FileMaker Pro 3 (remember when you had to copy and paste dates, because the set field script step wouldn’t work with dates? - yikes).
What’s a well-known, national entertainment organization to do when they need a way to track the music for their many television programs? Turn to FileMaker Pro, of course.
Six years ago the company faced a significant challenge: how best to log all of the music “cues” that are used for their television shows? It is not unusual for a single episode to have anywhere from 30-50 different tracks; given the number of shows the company airs this can add up to an average of 10,000 cue sheets per quarter. The producers need to record the song usage not just for their own internal purposes but so that it can be accurately reported to performance-rights organizations such as ASCAP and BMI. It was clear they needed a database, so they approached The Support Group with their vision for building one. The result was a cue-tracking system that logs each cue sheet by program name, airdate, territory and show number, ultimately allowing each user to enter the list of related cues for each sheet, which consists of the song title, composer name, and publisher information. The collected data is then used to generate thousands of pdf files, which are then reported for royalty collection on a quarterly basis. The new reports are much more efficient and accurate than the old manually prepared ones, and generating them from the database requires considerably less man hours.
Sometimes, when writing a FileMaker script to do a find, sort, or export, doing it manually first can save you LOTS of time.
I was just tasked with creating a script to export all the fields on a layout. The layout, however, is non-trivial and contains fields from multiple different tables, and the relationship graph I inherited is very complicated and nearly impossible to decipher. It would have taken me HOURS to identify the table occurrence and field name of every field on this layout and then set up the export in a script.
What I did instead was first perform the export manually. When exporting manually, the Specify Field Order for Export dialog defaults to showing you all the fields on the current layout, including the related fields (when writing a script, the same dialog only allows you to choose each table, since there is no “current layout” for the script to use). I chose the Move All button to select all the fields on the layout, went back to editing the script, added the Export Records script step, and chose Specify export order. The last, just-performed export was already there by default. I was done in minutes.
You can use the same idea to speed up specifying a sort order (where you can again choose the current layout’s fields) or search (where it’s easier to find fields laid out on the screen, rather than hunting around for them in the Edit Find Request dialog).