by Anders Monsen of MightyData
One Tuesday, May 8, I had the privilege to present the new calculation functions in FileMaker 12. This was the eighth webinar in the 12 Days of FileMaker 12 series by FM Academy.
There are over a dozen new functions in FileMaker 12, several of which accompany significant new features such as enhanced container fields, new window styles, and a new plug-in updating methodology. Yet it doesn’t end there, as there is a pair of functions that deal with ID values, some FileMaker Go specific functions, and native access to FileMaker’s SQL engine with the ExecuteSQL function.
Container Functions
Two new categories appear in the calculation dialog: Container Functions and Mobile Functions.
New Calculation Functions
The former provides the ability to get the height and width of content in containers using GetHeight() and GetWidth() functions. This returns height and width in pixels for images. Other items in the container field, such as movies or files, return 0. In addition, we have the ability to generate a thumbnail of an image with the GetThumbnail() function.
Since FileMaker 12 now enables us to store containers in external folders, checking the validity of the stored data reveals whether this has been changed or tampered with outside of FileMaker. Using VerifyContainer() shows 0 or 1 depending on whether the files have been changed or deleted, or if there are no changes.
Mobile Functions
There are two specific mobile functions that deal with Location information: Location() and LocationValues(). Previous versions of FileMaker in mobile devices were forced to get geolocation information using JavaScript. By making this native you can get longitude and latitude from Location and store these in your database. LocationValues displays a return-delimited list containing longitude, latitude, altitude, horizontal accuracy, vertical accuracy, and age in minutes.
Both functions accept two parameters, one required and one optional. The first is the accuracy in meters, and the second is the length in seconds allowed before timeout.
Updating Plug-ins
Another new feature in FileMaker Pro 12 radically changes the method to update plug-ins. Previous versions managed this through a daunting and often complex AutoUpdate process via FileMaker Server. There is now a new Install Plug-in script step, and a function to get a return-delimited list of currently installed plug-ins: Get( InstalledFMPlugins ). This returns the plugin name, version, and current state – Enabled, Disabled, or Ignored. One thing to note is that the version is only returned if the developers entered this in the resource file (Windows) or info.plist.file (Mac), so keep this in mind when setting up this process.
ID and Tab Panel Functions
The new pair of ID functions each return a long string of alphanumeric characters, but are completely different in purpose. Get( UUID ) returns a universal unique identifier, a unique 16-byte string in the format 8-4-4-4-12, such as E10FD6CB-2CB3-471A-B4B5-3CC2509F49EE. This function lets users create unique keys, and avoids key collision when merging records from multiple tables, for example.
Meanwhile, Get( PersistentID ) captures the unique identifier of the device accessing your solution, either desktop or mobile. Whereas you still can get the account name or user name, the new string like AF376F280690001931BFED4E890FADDB shows the device ID.
Along with some new tab control script triggers there are two new functions.
- Get( TriggerCurrentTabPanel )
- Get( TriggerTargetTabPanel )
When coupled with the script triggers, navigating from tab to tab will return values via these functions. The first shows the originating tab panel (number and object name) and the second the current tab panel (number and object name). The naming might be a little confusing, as the values are activated when going to a new tab, so the “current” is actually the target. Still, with these values we have a way to track and control navigation between tab control panels.
FileMaker Server has an option for SSL certificates. The new Get( ConnectionState ) function will show the security state of the file’s current network function, such as if it uses a non-secured network or a fully verified SSL certificate.
Connection and Window-Style Functions
A new feature in FileMaker 12 is the ability to have two new window states. Previously new windows were all document windows, so if you wanted to prevent the users from closing them you needed to set up a script, pause and then start the script again, or use script triggers to validate some action prior to allowing the users to continue.
Now you can set up a floating document window and a dialog window. So, to go along with this, we have a function that returns a number as to the state of the window, which we can use to determine action based on that window. Get( WindowStyle ) will return the following:
- 0 – Window is a document window
- 1 – Window is a floating document window
- 2 – Window is a dialog window
Execute SQL Function
Finally, a function that deserves a webinar all on it’s own, and in fact FM Academy just had such a webinar: Greg Lane’s walk-through of the ExecuteSQL() function.
This is a function that previous required plugins. There are some limitations compared to the plug-in functionality, because ExecuteSQL() only permits use of the SELECT statement. This means no ability to update, insert, delete, drop, or replace via SQL. Yet, having this as a new tool in FileMaker creates great opportunity. The function is formatted as follows:
ExecuteSQL( sqlQuery ; fieldSeparator ; rowSeparator { ; argument … } )
The sqlQuery is the SELECT statement, placed inside double quotes, such as “select first_name from contacts”. The fieldSeparator and rowSeparator default to a comma and return when you use “” for the parameters. You can use other separators such as tab, pipe, etc.
The optional arguments parameter lets you use a record’s field value as a parameter, useful when querying the context of individual records. I’ve previously blogged about ExecuteSQL (insert link). Learning this function will give you a great new tool in your FileMaker development.
Watch the recording of the webinar below for more coverage of these functions.
