Changes to Querying the FileMaker System Tables in FileMaker Pro 19.4.1

/8 Comments/in , , /by

Update for Claris FileMaker Pro 20 (2023) – FileMaker Pro v20 includes new functions that return information about the base tables instead of all table occurrences making it much easier get a list of base tables. Details can be found in this article.

Back in July 2012 I wrote an article about a new feature in the FileMaker Pro 12 Platform – the ability to query the hidden System Tables for information about the schema of your FileMaker Pro files using the new ExecuteSQL function. That article – Using ExecuteSQL to Query the Virtual Schema/System Tables – has become one of the most popular articles on our site and continues to be one of the most visited articles each month.

Every FileMaker Pro database file includes two system tables: FileMaker_Tables and FileMaker_Fields. Using the ExecuteSQL function to query these tables has a number of practical applications, including:

  • return a list of all unique underlying Base Tables for all Table Occurrences in the current FileMaker Pro file (see this custom function)
  • return a sorted list of all Field Names for specified Table Occurrence in the current FileMaker Pro file (see this custom function)

You can get further details on the results of these queries in the previous article and also the Claris FileMaker SQL Reference. There hasn’t been any changes to this functionality since FileMaker Pro v12 was released until this week with the release of Claris FileMaker Pro 19.4.1. The FileMaker Pro 19.4.1 Release Notes include mention of a change to allow you to perform faster SQL queries about fields defined in a FileMaker Pro file by using the new system table named FileMaker_BaseTableFields.

Previously to get a list of base table names you would use the following query:

ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_Tables" ; "" ; "" )

This would retrieve the value from the BaseTableName column from the response, which includes all table occurrences in the current file. You can now speed this up by using the following query:

ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_BaseTableFields" ; "" ; "" )

which returns the same response but is a faster query as FileMaker_BaseTableFields includes only the source (or base) tables. An example of the result when using our fmSMS solution is a simple list of all the base tables for the file:

Accounts
BulkSessions
Contacts
CountryCodes
DeliveryReceipts
GatewayCodes
Gateways
Interface
MergeFields
Messages
Navigation
Replies
Senders
ServerSideErrors
Templates
Webhooks

The FileMaker_BaseTableFields table includes the following columns:

  • BaseTableName – The name of the base table that contains the field.
  • FieldName – The name of the field.
  • FieldType – The SQL data type of the field.
  • FieldId – The unique ID for the field.
  • FieldClass – One of three values: Summary, for summary fields; Calculated, for calculated results; or Normal.
  • FieldReps – The number of repetitions of the field.
  • ModCount – The total number of times changes to this base table’s definition have been committed.

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.

fmEcommerce Link (Shopify Edition) Update November 2019

/0 Comments/in , , , /by

We’ve just released another free update to fmEcommerce Link (Shopify Edition), our FileMaker solution for integrating your FileMaker solution with the Shopify platform. This update is mainly concerned with changes that Shopify have made to their API that require integrators to make some small changes to their API requests.

UPDATED 21 March 2020: Shopify have announced that due to the current COVID-19 situation and impact on businesses they have decided to postpone the April 1 version removal to July 1, 2020.

API Versioning: earlier this year Shopify announced that they were introducing API Versioning which allows them to introduce new versions of the API without breaking previous versions of the API. Versions will be released on a quarterly cycle, on the first of January, April, July, and October and are named after calendar dates, such as 2019-04 for the April 2019 release.

As a result of this change they require you to include the API Version in your HTTP request URLs. For example a request to the Orders Admin API endpoint currently looks like this:

/admin/orders.json

With API Versioning include it will look like this:

/admin/api/2019-10/orders.json

To support API Versioning we’ve added a new custom function – ShopifyAPIVersion – to store the API Version number which can be easily updated over time. We’ve also updated all the URLs that are generated to include this, for example:

$$fmShopifyWebsite  &  "/admin/api/" & ShopifyAPIVersion & "/customers/count.json"

Shopify do allow you to continue to call the Admin API with no version, and they will return with the oldest supported version. However, they strongly recommend that you start including version numbers in order to make your application version-aware and anchor your code to a specific set of features that are guaranteed to behave in the same way for the next 12 months.

If you want to read more about API Versioning we recommend the following Shopify pages:

Deprecation of Page-Based Pagination: as part of the changes to API Versioning Shopify are deprecating page-based pagination and changing to what they call cursor-based pagination. Some customers have already started receiving notification emails from Shopify about these changes warning them to update by April 1, 2020 to avoid breaking changes.

The 2019-07 and 2019-10 Release of the API include these changes and no longer support page-based pagination, and as the API version 2019-04 will be removed on April 1, 2020 you will need to update your requests to support cursor-based pagination before then. We’ve done all the hard work for you so you can copy and paste the changes across to your existing integration or simply download the latest version of the fmEcommerce Link (Shopify Edition) solution if you are using the fmEcommerce Link file without modifications.

A typical page-based pagination request looks like this:

$$fmShopifyWebsite  &  "/admin/customers.json?limit=" & $limit & "&page=" & $page

You can see the “page” parameter is specified here – support for this is being removed. Instead you will make an initial request specifying the “limit” as you currently do, and if there are additional pages of results Shopify will let you know by including a response header that looks like this:

Link: <https://acmecorp.myshopify.com/admin/api/2019-10/customers.json?limit=5&page_info=eyJsYXN0X2lkIjoyODYzOTIxMjM0MTIsImxhc3RfdmFsdWUiOjODAwMDAsImRpcmVjdGlvbiI6Im5leHQifQ>; rel="next"

If there is another page of results to request you can check for a “rel=”next” value and grab the associated URL to use as the URL for the next request. When there is no rel=”next” Link returned in the response headers you no longer need to make any further requests.

We’ve updated all the scripts that request multiple records from Shopify (Get All Customers, Get All Products etc) that were using page-based pagination to the new cursor-based pagination method. We’ve added a new custom function for the native branch (getNextLink) to capture the “next” URL, and for the plug-in branch we’re using the BE_HTTP_ResponseHeaders plug-in function to do this. We’ve updated these scripts to reference the “next” URL as follows:

and have added an additional variable to store the “next” URL after each request if it exists:

Finally we’ve changed the exit condition for the loop from referencing the page number to the existence of a “next” URL:

If you want to read more about cursor-based pagination we recommend the following Shopify pages:

If you don’t update to the new cursor-based pagination format and make a request to a version of the API that doesn’t support page-based pagination you will get an error like this:

User Customisable Maximum Results Setting: we’ve also added a new field in the Interface table (RecordsLimit) to allow you to specify the maximum number of results to return when downloading multiple records from Shopify. Previously we were hardcoding the $limit variable to 50 which is the Shopify default if no value is specified. They do allow a maximum of 250 which could prove more efficient when downloading large numbers of Orders, Products etc from Shopify:

The full list of changes are listed in the version history 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). As with previous updates if you are using one of the Database Design Report analysis tools like BaseElements or FMPerception you can use these to run a comparison report between your current version/integration and the new v1.5 release to identify the changes.

Troubleshooting Errors with FileMaker Server Auto Update plug-ins feature

/in , , , , /by

I’ve been spending a lot of time recently deploying solutions that require plug-ins and having FileMaker Server distribute them automatically to clients via it’s nifty Auto Update feature. Inevitably you will encounter issues for some clients when attempting to download the plug-in: permissions issues with the plug-ins on the server, not having the plug-in in the correct location etc. They are usually easy to resolve once you know what the error is.

When you write a script to download a plug-in you end up calling the FMSAUC_UpdatePlugIn function. This is a function of the AutoUpdate plugin (yes you need to use an existing FileMaker plug-in to download a plug-in), which thankfully by default is always installed by FileMaker Pro/Pro Advanced (there are only 3 functions for this plug-in: one checks the version (used to see if the plug-in is installed), another to find the plug-in you’re after on the FileMaker Server computer, and the third to download the plug-in). Assuming you have the plug-in installed in the correct location on your FileMaker Server with the appropriate permissions set (see the FileMaker Server 11 Guide to Updating Plug-ins for the full details) you’ll end up calling the FMSAUC_UpdatePlugIn function to initiate the download of the plug-in. If successful this function will return 0 like a normal successful script step, and if unsuccessful it will return an error number in the range of -6 to 101.

I like to capture the error number and if necessary display that to the user along with a more human readable error description. There are some custom functions that you can use to return the description for the error code. For example you capture the error code into a variable using the Get (LastError) function and then pass that to the custom function which will return the error description. For example using the custom function linked above ErrorToEnglish ( 101 ) returns “Record is missing” and saves you the trouble of looking up the error code in FileMaker’s help. However when using the FMSAUC_UpdatePlugIn function you won’t able to use this as it has it’s own list of error codes which overlap with existing list of FileMaker error codes. For example error 101 means “The function call from the client computer to the computer running FileMaker Server failed. The server computer might be running a previous version of FileMaker Server.” when returned by the FMSAUC_UpdatePlugIn function but could return “Record is missing” from the Go to Record/Request/Page script step.

I ended up creating another custom function that you can call when performing the FMSAUC_UpdatePlugIn function that will return the appropriate description for any errors encountered when attempting to download a FileMaker plug-in from FileMaker Server. Note you could also extend the existing ErrorToEnglish custom function to include a 2nd parameter to indicate whether it is a script step or the FMSAUC_UpdatePlugIn you are capturing the error code for, rather than have separate custom functions.

FileMaker Announces Schedule for FileMaker Developer Conference 2010 in San Diego

/0 Comments/in , , , , , /by
FileMaker, Inc. have announced the sessions and workshops schedule for the FileMaker Developer Conference 2010, the largest annual gathering of worldwide FileMaker independent and corporate database developers, trainers and users. This year’s conference will be at the Sheraton San Diego Hotel & Marina, August 15 to 18, 2010.
This year’s conference will feature more than 80 sessions and workshops, the most ever offered, led by recognized FileMaker experts. Key sessions will focus on FileMaker database development best practices and techniques including FileMaker Server configuration, content sharing between FileMaker and Microsoft Office, FileMaker and the Cloud, working with calculations and more. Conference sessions and workshops will also include detailed information about designing databases on a large scale and displaying information through reports.
Databuzz is pleased to announce that Andrew Duncan will be presenting two sessions at this year’s conference. The first session is titled “Integrating SMS/TXT Messaging to Your FileMaker Solution Session” and is part of the Integration/Web stream. In this session attendees will learn how to integrate the ability to send SMS messages directly into your FileMaker solution. The session will cover:
  • what is required to start sending SMS messages from FileMaker Pro – how to use simple FileMaker Pro native technologies to send an SMS (e.g. scripts steps, Script Triggers and Web Viewers)
  • when to consider using external plug-ins instead of native technologies and which plug-ins to use
  • when to consider using a hardware modem and how to configure ESS for this
  • how to receive incoming SMS messages directly into your FileMaker Pro solution
The second session is also part of the Integration/Web stream and is titled  “Building a FileMaker Pro/Custom Web Publishing Solution for Internal and External Users”. Many FileMaker in-house solutions often require limited access by external parties that do not use FileMaker Pro. In this session we will walk you through how to approach the development of a “hybrid” solution that has 2 types of users: in-house staff using FileMaker Pro and external customers/users using Custom Web Publishing. Find out when and how to use the FileMaker API for PHP to give access to external users to your FileMaker solution. Several case studies will demonstrate how these problems have been solved and what lessons we have learned along the way (things I know now that I wish I had known then).

Attendees can save US $200 on the conference price by registering by May 21, 2010. You can get all the details at the DevCon website

Cleaning up Smart Quotes in FileMaker Pro v10

/in , , , , /by

One of my pet hates is the use of “smart quotes” in FileMaker Pro. Smart quotes are the curly quotation marks and curly apostrophes and are usually entered in 2 ways:

1. the setting for smart quotes is enabled by default for the creation of new .fp7 database files on Mac OS X but not for Windows XP, so anytime you enter an apostrophe or a quote mark you will be using smart quotes.

2. pasting in text from Microsoft Word, which also has a preference setting for smart quotes and which is also enabled by default (you can get instructions for how to turn this off on Windows here)

Here are some examples of how they appear:

Single Left Smart Quote: ‘

Single Right Smart Quote: ’

Double Left Smart Quote: “

Double Right Smart Quote: ”

The smart quotes setting in FileMaker Pro can be found by opening a database file and going to the File menu > File Options, and click the Text tab (note that you must log in using the Full Access privilege set to edit items in the Text tab). Here you can toggle smart quotes on and off- if you enable smart quotes when it was previously off this will only affect new typing as any existing data will not be edited automatically.

For many users smart quotes are not problematic, but when it comes to exchanging data with other database platforms (e.g. SQL) and systems (particularly web based systems) their presence will often cause data exchange processes to break or for text not to be displayed correctly, which means you then have to deal with any smart quotes in your data. I was recently working on submitting FileMaker data to on online system and was required to URL encode my data. Everything was working well until one day data was being truncated for no obvious reason. After many tests I realised that some of the apostrophe and quotation characters were not being URL encoded correctly (I was using this handy custom function to URL encode the data before submitting it via HTTP POST). Once I zoomed in on the text to 400% and changed the font to a sans serif font all was clear – the text was using smart quotes which the customer has enabled as this was the default setting for a new .fp7 file on Mac OS X. I quickly turned that off which meant any new data entered directly into the fields would not be using smart quotes, but all the existing data needed to be cleaned up. I created a new custom function RemoveSmartQuotes (text) which uses one of the new functions in FileMaker Pro v10 to reference a character without having to actually type/enter the character into a calculation.

The following calculation uses the Char function to identify any instances of the single and double smart quotes/apostrophes and replace them with plain (straight) quotes and apostrophes:

Substitute (

text ;

[ Char ( 8216 ) ; “‘” ] ; // left smart quote
[ Char ( 8217 ) ; “‘” ] ;  // right smart quote

[ Char ( 8220) ; “\”” ] ; // left double smart quote
[ Char ( 8221) ; “\”” ]   // right double smart quote

 

)

I’ve dcreate a custom function that uses this calculation that you can copy/paste into your file which should appear shortly on Brian Dunning’s custom function site. This custom function can then be used in a number of ways to clean up text that cannot have any smart quotes in them, for example as an auto enter calculation on a field or in a script that transforms data. If you’re using a version of FileMaker Pro before v10 you simply need to change the use of the Char function with the actual smart quotes themselves and remember to escape the double quotes.

Databuzz now FileMaker 10 Certified

/0 Comments/in , , , /by

We’re pleased to announce that Andrew Duncan recently sat the FileMaker 10 Certification exam and passed!

certified-10-logo_2clr_small

We aim to maintain our certification with each new version of FileMaker that is released. FileMaker Certification lets our customers know that we’re experts in the FileMaker industry and is the only credential sponsored by FileMaker Inc

We’re FileMaker 9 Certified!

/0 Comments/in , , , , /by

Andrew Duncan finally found time to sit for the FileMaker 9 Certification exam today and we’re pleased to announce that he passed with flying colours. We aim to maintain our certification with each new version of FileMaker that is released. FileMaker Certification lets our customers know that we’re experts in the FileMaker industry and is the only credential sponsored by FileMaker Inc.

 

FileMaker 9 Certified!

FileMaker DevCon 2008 Post Mortem

/1 Comment/in , , , , , /by

It’s been a few weeks now since I returned from the FileMaker 2008 Developer Conference in Phoenix, Arizona. It was a whirlwind trip this time – there and back in 6 days with lots of jetlag and not much sleep, no thanks to Qantas for cancelling my return flight and delaying my departure. I’ve finally settled back in and can report on some of the highlights from the conference and the product showcase. I spent most of my time attending any PHP related sessions and learnt a lot about the power of PHP in conjunction with the FileMaker PHP API. I’m currently working on a project that is an upgrade from a CDML/FileMaker Unlimited 5 database driven site to a FileMaker Server 9/PHP driven site. There’s lots of new stuff to learn and I’m impressed with the capabilities of PHP compared to CDML. I particularly enjoyed Lance Hallberg’s sessions on the PHP API fundamentals and the advanced features.

The two sessions on using the Web Viewer to interact with your FileMaker tables and as a dynamic interface were both well attended. Both highlighted the power of the MBS plugin and the Fusion Reactor plugin – the amazing Fusion Reactor also won the FileMaker Excellence award for Solution of the Year. Congratulations to Craig and the gang at Digital Fusion for their amazing plugin which is one of the most truly innovative plugins to be released in the last few years.

In the product showcase I ended up buying some new tools and upgrades:

BaseElements 2 – Goya have released their update to v2 of their DDR Analysis tool BaseElements. This is almost a must have for the professional FileMaker Developer. I’m still using both Inspector and BaseElements as each have their strengths but spend most of my time with BaseElements. SixFriedRice were demoing an early released of a web hosted service – FM Lumen – which they promise will provide deeper and faster analysis of the FileMaker DDR. I can’t wait to give that one a try.

ClipManager – My FM Butler have released a beta of v3.0 of their handy tool Clip Manager which allows you to store and manipulate the contents of the FileMaker clipboard. It’s also at present one of the only ways to copy custom functions and you can use it to build up a library of fields, scripts, layouts and other elements that you can quickly add to your solutions. v3.0 is also the first release that runs on Windows as well as the Macintosh.

Web Services Plugin – I already have a developer licence for the amazing Web Services plugin from FM Nexus. This was featured in the opening keynote and FM Nexus released a beta of v2 which makes it easier to load functions on the fly by storing the WSDL file inside a field in your database.

I also bought the VTC PHP Training CD so I can totally immerse myself in the FileMaker API for PHP. I look forward to attending the next DevCon in 2009 in San Francisco – finally a new city and new new hotel!