Using AppleScript to Automate Business Workflows

/2 Comments/in , , /by

I was surprised recently to see an announcement about an updated version of the ebook FileMaker Pro Scripting Book with AppleScript (English Edition) and also pleased that others are still using the combination of FileMaker Pro and AppleScript to automated tasks using software that has been around for several decades.

AppleScript is the scripting language created by Apple that lets you directly control Mac apps using instructions written in an English-like scripting language. It was first introduced in 1993 when System 7 was the current macOS operating system. In 1995 Claris (still in its first incarnation under the Claris brand name) released FileMaker Pro v3 which included the Perform AppleScript script step which allowed you to call AppleScript commands from a running FileMaker script.

I’ve drifted in and out of AppleScript development over the years but recently turned to it as a way of automating some repetitive tasks as part of the process of releasing updates to our suite of FileMaker integration solutions. Every time we release an update to a product we have to perform a number of steps which include:

  • making a copy of the master development files
  • opening the new copy and performing a FileMaker script to clear out any test data
  • setting the File Options to use a default login
  • create a .zip file of the new version
  • upload the .zip to our online store via FTP

We also release an updated trial version at the same time which includes the above steps but also some additional steps that involve the FileMaker Pro Developer Utilities to remove admin access to the files.

We were performing each of these tasks manually and sometimes we would get distracted and forget to perform a step or perform the steps in the wrong order and then have to start all over again. The process was very much prone to human error. I wrote last year about how you should Automate What You Hate and this was definitely something I avoided and did not enjoy. After thinking about how I could automate all or parts of this process I knew AppleScript would be the most likely candidate.

For a few months in my spare time I had been developing a FileMaker solution to manage these tasks and automate them but hit a brick wall when I realised that my FileMaker Script, which was using the Perform AppleScript script step, couldn’t call FileMaker scripts as part of the AppleScript whilst the FileMaker script was running. It would generate this error:

"FileMaker Pro 19 got an error: No user interaction allowed." number -1713

After several failed attempts to work around this I abandoned the FileMaker solution and resorted to using AppleScript directly and just running the script manually using the Script Editor application which has worked a treat. I use Panic’s Transmit for handing the FTP upload and that has great support for AppleScript. My AppleScript skills were a bit rusty but thanks to Google and a bit of trial and error I was able to get a working script in a matter of hours.

The process now involves a simple double click to run the script and within seconds the AppleScript performs a series of commands using the macOS Finder, FileMaker Pro and Transmit that is free of any human errors. It now makes releasing updates to our products an enjoyable experience and something we are now likely to do more frequently rather than putting this off.

If you’re using Macs in your business and finding yourself manually performing a series of tasks multiple times it is worth investigating whether this can be scripted using AppleScript. It might not be as exiting as modern API integrations but this venerable piece of technology can still help businesses automate repetitive manual tasks that are prone to human error as we have been recently reminded.

Optimising Parsing JSON Arrays using the JSONGetElement Function

/0 Comments/in , , /by

Using the JSONGetElement function you can query JSON data for a specific element by an object name, an array index, or a path. For example you might be querying an API like Xero or Shopify for a set of recent Invoices or Orders which you would like to download into your FileMaker solution. Most APIs use pagination to limit the number of records that are returned in response to your query. The number of records returned might be set by the API vendor (e.g. you will always get 50 records) or you might be able to specify the number of records to return by using a query parameter.

The response from the API will typically be a JSON Array – Claris use the following example JSON data in their documentation:

{
    "bakery" : 
    {
        "product" : 
        [
            {
                "id" : "FB1",
                "name" : "Donuts",
                "price": 1.99,
                "stock" : 43,
                "category" : "Breads",
                "special" : true
            },
            {
                "id" : "FB2",
                "price": 22.5,
                "name" : "Chocolate Cake",
                "stock" : 23,
                "category" : "Cakes", 
                "special" : true
            },
            {
                "id" : "FB3",
                "price": 3.95,
                "name" : "Baguette",
                "stock" : 34,
                "category" : "Breads", 
                "special" : true
            }
        ]
    }
}

This sample JSON contains a bakery object with an array of three product objects. To get the value of a particular object you can use JSONGetElement function and specify the array index and object name. For example if you wanted to get the value of the name object of the second product object in the array you would use:

JSONGetElement ( $$JSON ; "bakery.product[1]name" )

which would return ‘Chocolate Cake’. You would typically have a counter that you would set in your FileMaker script to increment the array index – remember to start your counter at 0 as JSON Array indexes are zero based. Whilst this approach works reasonably well for small JSON Arrays once your JSON Array contains larger results (50, 100, 150 records etc) then you will get better performance by breaking out each individual record into it’s own FileMaker variable and then referencing that with your JSONGetElement functions. The benefits are even more noticeable when your JSON data contains multiple nested arrays.

FileMaker’s JSON parser is much slower when it has to continually parse the JSON array to target the 1st record, then the 2nd, 3rd and so on. If you have over a hundred records in your JSON array (as well as each record having their own arrays, such as an Order with line items, tax items, refund items etc) you will get much better performance if you break out each record into it’s own FileMaker variable and target that. You want to avoid parsing any large JSON arrays as much as possible.

In our FileMaker integration solutions we have changed from using this type of structure in a loop:

JSONGetElement ( $response ; "orders[" & $counter & "].id" )

to extracting each record from the JSON array in a loop into a $record variable:

JSONFormatElements ( JSONGetElement ( $response ; "orders[" & $counter & "]" ) )

then we can simply target each object like this:

JSONGetElement ( $record ; "id" )

We’ve seen performance improvements of over 80% when converting scripts to this new format when testing our fmEcommerce Link (Shopify Edition) solution by downloading all Orders and all Products from Shopify. If you’re parsing large JSON arrays we highly recommend moving away from continually having to parse the JSON array to extracting each record from the array in your loop and perform your JSONGetElement queries on that instead.

Here’s a short video that demonstrates the technique (you can also watch this on YouTube here):

You can also download the file we used in this demonstration here.

Understanding FileMaker System Formats and Resetting the File Locale

/0 Comments/in , /by

When you create FileMaker files, FileMaker Pro uses your computer’s system formats to determine how dates, times and numbers display and sort. If you open or share a FileMaker file created with different system formats, you can use your computer’s system formats or match the formats used when the file was created

I was recently working on a FileMaker solution (originally created by our customer) and started to notice some strange results when working with dates and numbers. I’m based in Australia so I expect to see the following regional settings for dates, numbers and currency as shown in the Language & Region System Preferences on my Mac:

I was in the process of manipulating a date to change the format used when making an API request – here’s how the date field looks on the FileMaker layout:

I like working with FileMaker lists and often substitute out the date separator character for a FileMaker carriage return ¶ using this syntax:

which didn’t give me the expected result. That was my first clue that there was something different with the date formats for the file I was working with. Shortly after I needed to import some geocodes that were to be used for mapping a customer’s address against a defined delivery zone (see earlier article on this here). Here’s a screenshot of the geocodes in Excel that I was importing into FileMaker:

and here’s how they appeared after being imported info FileMaker:

You can see that the decimal separator has changed from a full stop/period to a comma – once again I knew there was definitely something different with the number formats for this file. This was confirmed again shortly afterwards when I was adding in a test to ensure that the user was using FileMaker Pro 19.1.3 or later due to some changes in how the FileMaker.PerformScript() function operates. GetAsNumber ( Get (ApplicationVersion) ) was returning this:

whereas I was expecting this format:

Viewing the File Locale Settings

If you’ve been developing with the FileMaker Platform for a while you might recall seeing this dialog box when opening a file that was sourced from outside of your normal region (e.g. an Australian developer being sent a file from a developer in North America):

This was FileMaker Pro alerting you to the difference in the settings between the file (e.g. US regional settings) and your local macOS/Windows system settings (e.g. Australian). You could (and still can) manually toggle the use of System formats via the Format menu:

as well as using the File Options>Text tab settings for each FileMaker file:

The default for this setting was changed to “Always use current system settings” with the release of the FileMaker Pro 8.0v2 update back in December 2005 – previous it was set to “Ask whenever settings are different”. This was a welcome change and solved most of the issues as far as displaying and entering dates, times and numbers. Developers could also use the Set Use System Formats script step in their startup script to enable this option.

However you can still encounter issues when the file locale settings are different to the system locale settings as described above, most often when importing or exporting data and not having the correct source format set etc.

Claris have recently provided a programatic way to determine the locale settings for a file so you can be more proactive about identifying if there is a difference between the file’s locale settings and your macOS/Windows system settings. The recently released FileMaker Pro 19.1.2 Update included 2 new Get functions which return information about the operating system’s locale and the current file’s locale:

Get(SystemLocaleElements) – returns a JSON object with information about the client system’s locale.

Get(FileLocaleElements) – returns a JSON object with information about the current file’s locale.

Using these 2 functions you can now compare the system locale settings with the current file locale settings to see if they are different. You can see the JSON object that is returned by using the Data Viewer to add these under the Watch tab. You can also use the JSONGetElement function to target particular JSON keys, including the Misc.Active key which indicates whether the active locale for the current user is the system’s locale (true) or the file’s locale (false):

JSONGetElement ( Get(SystemLocaleElements) ; "Misc.Active" )

If you want to compare the system locale with the file locale to see if they are the same you can use the following test:

JSONGetElement ( Get(SystemLocaleElements) ; "LocaleID.IDNum" ) = JSONGetElement ( Get(FileLocaleElements) ; "LocaleID.IDNum" )

Here’s some other examples that you might find useful:

JSONGetElement ( Get(SystemLocaleElements) ; "LocaleID.Name" ) returns the name of the system locale.

JSONGetElement ( Get(SystemLocaleElements) ; "Date.Sep" ) returns the Date separator character for the system locale.

JSONGetElement ( Get(SystemLocaleElements) ; "Num.Decimal" ) returns the decimal separator character for the system locale.

JSONGetElement ( Get(FileLocaleElements) ; "LocaleID.Name" ) returns the name of the current file locale.

JSONGetElement ( Get(FileLocaleElements) ; "Date.Sep" ) returns the Date separator character for the current file locale.

JSONGetElement ( Get(FileLocaleElements) ; "Num.Decimal" ) returns the decimal separator character for current file system locale.

Using these new functions I was able to determine the exact locale settings for the file I was working on. In this case these revealed that the file had a locale of Germany, the Date separator was the . character and the Number decimal separator was the , character. This explained why I was having issues with importing dates and numbers and evaluating the GetAsNumber ( Get (ApplicationVersion) ) function.

Changing the File Locale Settings

Version 19.5.1 of the Claris FileMaker Data Migration Tool now lets you specify a new locale for a target file. Use the syntax “-target_locale {locale}” to change the locale of the resulting file. See the Claris FileMaker Data Migration Tool Guide for for supported locales and additional details. You can download a copy of the Claris FileMaker Data Migration Tool here.

If you don’t want to use the Claris FileMaker Data Migration Tool you can perform the following actions to reset the file locale settings:

  1. save a clone of the file you wish to reset. A cloned copy of a FileMaker Pro file contains all the contents of the original file with the exception of the record data and the default locale information. 
  2. open the clone on a system with the required regional formats you wish to use. When FileMaker Pro or FileMaker Pro Advanced opens a clone, the operating system’s current locale information is added to the clone.
  3. import your data from the original file to the newly cloned file one table at a time.

We would definitely recommend using the Claris FileMaker Data Migration Tool here as it can migrate data from all tables at once and also reset the locale for the target file.

Dynamic Delivery Zone Checking with FileMaker Pro 19

/1 Comment/in , , /by

One of our favourite features of the FileMaker 19 Platform that was released earlier this year is the ability to interact with a FileMaker web viewer using the new JavaScript integration capabilities. This update allows FileMaker scripts to directly call JavaScript functions and pass multiple parameters, and also allows the JavaScript running in the web viewer to run a FileMaker script in the current file and pass in a script parameter.

I was looking forward to taking advantage of this in client projects over time and was recently able to use this feature to help a client solve a particularly challenging problem in a relatively short space of time.

Our client runs a food delivery service, delivering gourmet recipe mealkits to customers homes each week. With the COVID 19 restrictions in Australia they have seen a lot growth in the past 6 months and an increase in the number of weekly orders. They have a defined delivery zone setting out the boundaries of where they will deliver to which is changing over time, but they needed a way to determine whether a customer’s address was inside or outside their set delivery zone.

I’ve done a lot of work with the Google Maps JavaScript API over the years and wondered whether that could be of any assistance here. A few minutes of research led me to their Geometry library which includes a containsLocation() function that can determine whether a given point falls within a polygon. All that is required is the geocode of the address in question (latitude and longitude) and a polygon representing the area you wish to check against, which itself is a series of geocodes that define the boundaries.

To create the polygon representing the delivery zone boundaries I turned to Google Earth which has a tool that allows you to draw a line or a shape representing the area you wish to define. I created a region for parts of Sydney, Australia that would be my test delivery zone region using Google Earth which is represented by the yellow lines in this screenshot:

I was then able to export that area as a .kml file which contained the series of geocodes that made up the boundaries of my test delivery zone. I then converted that file into a .txt and imported the geocodes into a FileMaker table so I could then recreate the delivery zone polygon that was required by the Google Maps API.

I also used the Google Maps Geocoding API to convert customer addresses into a geocode which is also required to pass to the Google Maps API to determine if it is inside or outside the polygon. I was now ready to bring this all together to have a button on my FileMaker layout that allowed me to programatically check an address against the delivery zone and return a result indicating if it was inside or outside, as well as visually showing the location of the address on the map so you could visually where it was in relation to the boundaries.

Using the new Perform JavaScript in Web Viewer script step in FileMaker Pro 19 I was able to call a JavaScript function to check the customer’s address and have that in turn call a FileMaker script with the result as the parameter. When setting up your web viewer you will need to make sure you enable the new Allow JavaScript to perform FileMaker scripts option (this is disabled by default when adding a new web viewer to a layout):

To have your JavaScript return a result back to FileMaker by calling a FileMaker script and passing a parameter you will need to call this function:

FileMaker.PerformScript ( script, parameter );

In my example I am calling a FileMaker script named Update Zone Check with a parameter of zoneCheckResult which returns true if the customer address is inside the delivery zone, or false if it is outside:

FileMaker.PerformScript('Update Zone Check', zoneCheckResult);

Here’s a screenshot showing a successful result (you can see the address pin is inside the shaded delivery zone):

and here’s a screenshot showing a record where the address is outside the delivery zone:

In each case the Inside Delivery Zone field in the bottom left corner was updated dynamically by the FileMaker JavaScript interaction. We’ve put together a short video showing this in action which you can view below or directly on YouTube here.

N.B. the FileMaker Pro 19.1.2 Updater introduced a new version of the FileMaker.PerformScript() function that allows you to control how running scripts are handled when JavaScript calls a FileMaker script. A new function:

FileMaker.PerformScriptWithOption ( script, parameter, option )

now lets you specify 6 options that determine how a currently running FileMaker script is handled. The FileMaker.PerformScript() function is still supported and uses the default ‘Continue’ option. See the release notes for further details on the available options.

FileMaker 19 Platform Compatibility Update

/0 Comments/in , /by

Claris today released the FileMaker 19 Platform and we’re pleased to report that all Databuzz products are compatible with the FileMaker 19 Platform. We haven’t encountered any issues with the following products in our testing with the FileMaker 19 Platform:

fmSMS – we have sent messages from multiple SMS Gateways, checked Account balances and checked the status of sent Messages successfully. Tests were performed using both fmSMS v3.5 and fmSMS v4.

fmAccounting Link (Xero Edition) – we have been able to authenticate, download from Xero to FileMaker and upload from FileMaker to Xero successfully. Tests were performed using both fmAccounting Link (Xero Edition) v1 and v2.

fmEcommerce Link (WooCommerce Edition) – we have been able to authenticate, download from WooCommerce to FileMaker and upload from FileMaker to WooCommerce successfully

fmEcommerce Link (Shopify Edition) – we have been able to authenticate, download from Shopify to FileMaker and upload from FileMaker to Shopify successfully

fmESignature Link (DocuSign Edition) – we have been able to send a signing request, check the status, download the completed PDF and form data successfully.

fmAccounting Link (MYOB AccountRight Edition) – we have been able to authenticate, download from AccountRight to FileMaker and upload from FileMaker to AccountRight successfully. Tests were performed with the on premise version and the MYOB Cloud version.

If you encounter any issues with any of our products and FileMaker 19 Platform please let us know. We’ll be writing more articles about some of the new features in the FileMaker 19 Platform over the coming weeks – you can subscribe to our newsletter to be notified when they are released or follow us on Twitter or Facebook.