Optimising Parsing JSON Arrays using the JSONGetElement Function

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.

Databuzz 2020 End of Year Sale – Last 2 Days

Just a reminder that our End of Year Sale ends in 48 hours. We only have one sale each year and you can save 20% on all Product Licenses until December 31, 2020 (AEDT), including:

Use the coupon XMAS2020 at checkout on any purchase and the 20% discount will be applied to your order. The sale runs until the end of the day on December 31, 2020 (Australian Eastern Daylight Time). Click here to start shopping. fmSMS licenses can be purchased from the fmSMS website.

Free trial versions are available for all of our products – please contact us to request a trial version.

All our prices are in Australian dollars – if you’re purchasing from outside of Australia you’ll also get to take advantage of the low Australian dollar (1 Australian Dollar equals approximately 0.74 US Dollars).

Databuzz 2020 End of Year Xmas Sale – 20% Off All Licenses

The Databuzz End of Year Xmas Sale is now on – all Product Licenses are now 20% off, including:

Use the coupon XMAS2020 at checkout on any purchase and the 20% discount will be applied to your order. The sale runs until the end of the day on December 31, 2020 (Australian Eastern Daylight Time). Click here to start shopping. fmSMS licenses can be purchased from the fmSMS website.

Free trial versions are available for all of our products – please contact us to request a trial version.

All our prices are in Australian dollars – if you’re purchasing from outside of Australia you’ll also get to take advantage of the low Australian dollar (1 Australian Dollar equals approximately 0.74 US Dollars).

fmESignature Link (DocuSign Edition) v1.3 Update

We’ve just released another free update to fmESignature Link (DocuSign Edition), our FileMaker solution for integrating with the DocuSign eSignature platform. This update includes a number of new examples for sending and updating DocuSign Envelopes, including:

  • we’ve added an example for sending a signing request using Perform Script on Server
  • you can now void Envelopes that are not completed from fmESignature Link
  • you can resend the email request to all pending recipients. This can be helpful when you need to remind recipients that have not completed the signing process
  • you can now edit the email address of an Envelope recipient and resend the email notification. This is helpful when you’ve entered the wrong email address for a recipient and need to update this and resend it to their new email address
  • you can now delete Envelope recipients whose status is still pending
  • you can now set the Event Notification Webhook URL in the Requests table (as well as in the Templates table) to allow for greater flexibility in setting this for ad hoc requests
  • you can now set the Email Body of the DocuSign emails that are sent when sending a request from fmESignature Link

The Email Body that you can set via the DocuSign API is also referred to as the email “blurb” and the standard email body if not specified typically looks something like the highlighted text in this screenshot:

You can now specify some custom text to appear in place of the default email body which will look like this:

The full list of changes are listed in the version history notes here. Existing customers can download this version from the link on your original order email (contact us if you need the link to be reset etc). We’re working on the next update to fmESignature Link that will include examples for sending requests for Payments via the DocuSign API and specifying the language used for the signing process and hope to have this released within the next few weeks.

fmMMS Now Supports CDYNE MMS Gateway

fmMMS, our FileMaker solution that lets you send and receive MMS messages from the FileMaker Platform, has just been updated to include support for the CDYNE MMS Gateway based in the USA. You can send and receive MMS messages in the United States and Canada with fmMMS and CDYNE.

You can download a trial version of fmMMS to test with the CDYNE MMS Gateway from here. You can also sign up for a trial account with CDYNE. More information on using the CDYNE Gateway with fmMMS is available on our support site.

Understanding FileMaker System Formats and Resetting the File Locale

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.

fmAccounting Link (Xero Edition) Now Supports Xero Quotes

We’ve just released a free update to v2 of fmAccounting Link (Xero Edition), our FileMaker solution that integrates with the Xero Accounting Software, to include support for Xero Quotes. Xero introduced Quotes back in 2015 but it wasn’t until this year that developers were able to finally able to retrieve, create and update Quotes in Xero via the Xero API. It’s been a long time coming and one of the most requested features for the API with over 1300 votes alone!

We had planned to release an update to support Quotes earlier this year but we had to focus on adding support for OAuth 2.0 and releasing v2 of fmAccounting Link (Xero Edition), but we’re pleased to have finally added support for Quotes into v2 of the fmAccounting Link solution.

With this update you can now perform the following quote related tasks in FileMaker:

  • download all quotes from Xero (can filter by date range like Invoices)
  • create a new quote and upload to Xero
  • revise an existing quote and update in Xero
  • change the Status of a quote and update in Xero (e.g. from Draft to Sent)
  • download the quote PDF from Xero
  • update the quote history and notes
  • upload attachments for the quote
  • download quote history and notes
  • download quote attachments

We’ve also added the ability to convert a Quote in the fmAccounting Link file to an Invoice to save you having to re-enter this manually. Here’s a short video showing the Quotes integration in action:

We also added support for emailing Invoices via Xero from the fmAccounting Link file. When you click the Email Invoice button on the Invoice Details layout you will now get a prompt to choose how to send the invoice:

When you choose the Xero option it will trigger the email of a sales invoice out of Xero. The invoice must be of Type ACCREC and a valid Status for sending (SUMBITTED, AUTHORISED or PAID).

The email will be sent to the primary email address of the contact on the invoice and any additional contact persons that have IncludeInEmails flag set to true. The sender will be the user who authorised the app connection. The subject and body of the email will generated from the organisation’s default template.

The full list of changes are listed in the version history notes here. Existing customers can download this version from the link on your original order email (contact us if you need the link to be reset etc).

fmMMS Now Supports Plivo MMS Gateway

fmMMS, our FileMaker solution that lets you send and receive MMS messages from the FileMaker Platform, has just been updated to include support for the Plivo MMS Gateway based in the USA. You can send and receive MMS messages in the United States and Canada with fmMMS and Plivo.

You can download a trial version of fmMMS to test with the Plivo MMS Gateway from here. You can also sign up for a trial account with Plivo. More information on using the Plivo Gateway with fmMMS is available on our support site.

fmSMS Now Supports Sendsei SMS Gateway

fmSMS, our FileMaker solution for sending and receiving SMS messages from the Claris FileMaker Platform has been updated to support the Sendsei SMS Gateway based in Australia.

Sendsei uses local carriers to guarantee 100% delivery rate and can send messages globally to over 30 countries including Australia, Hong Kong, India, New Zealand, Singapore, the UK, the US, and many more.

To download a trial version of fmSMS to test with the Sendsei SMS Gateway visit the fmSMS website here:

https://www.fmsms.com/fmsms-now-supports-sendsei-sms-gateway/

fmSMS Now Supports The SMS Works SMS Gateway

fmSMS, our FileMaker solution for sending and receiving SMS messages from the Claris FileMaker Platform has been updated to support The SMS Works SMS Gateway based in the UK.

The SMS Works is a low cost SMS API for developers and their prices are amongst the lowest in the UK. You can signup for a trial account with The SMS Works and receive 50 free credits to use for testing.

To download a trial version of fmSMS to test with the The SMS Works SMS Gateway visit the fmSMS website here:

https://www.fmsms.com/fmsms-now-supports-the-sms-works-sms-gateway/