In this session from the 12 Days of FileMaker 12, we explore over a dozen new functions in FileMaker 12. The new features include improved container fields, new window styles, and a new plug-in updating methodology. 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.

New Container Functions

Two new categories appear in the calculation dialog: Container Functions and Mobile Functions.

The former provides the ability to get the height and width of content in containers using GetHeight( ) and GetWidth( ) functions. It 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 changed or not.

New 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 of 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 an Install Plug-in File script step, and a function to return a list of currently installed plug-ins: Get( InstalledFMPlugins ). This result includes the plugin name, version, and current state – Enabled, Disabled, or Ignored. The publisher must enter the version number 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 username, 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 can be  confusing when going to a new tab, so “current” is the actual target. Still, with these values we have a way to track and control navigation between tab control panels.

Window-Style Functions

A new feature in FileMaker 12 is the ability to have two new window states. Previously new windows were document windows. 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.

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.

It is a function that previously required plugins. There are some limitations compared to the plug-in functionality because ExecuteSQL( ) only permits the use of the SELECT statement. It means no ability to update, insert, delete, drop, or replace via SQL. Having this as a new tool in FileMaker creates great opportunity. The function format is:

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. Learning this function will give you a great new tool in your FileMaker development.

FileMaker Calculations Training