Tips for Recognizing Unexpected Behaviour in FileMaker’s PHP API

Recently, I’ve been working on a project that takes an existing FileMaker database, and adds a custom PHP front-end for access via the web. Fortunately, FileMaker offers a light-weight, easy to use PHP API. Having never worked with the FileMaker API, I found that it was extremely easy to pick up and use, but sometimes behaved in ways that a person with experience using various SQL drivers might not expect.

Today I’m going to share a few of the problems that I came across, and how I was able to solve them.

Querying layouts, what to look for when a find command is returning an error object instead of a result

Using the FileMaker API, instead of querying tables directly, we query the layouts. This can cause some unexpected issues due to a number of reasons, the most obvious being that you have the wrong layout. Often times a FileMaker developer will create a specific layout for web access. This layout may be named something different, and if you were not the one who developed the FileMaker solution, you should ask the developer which layout you should use. A common problem that I encountered was that the layout I was querying did not have all of the fields from the table that I needed. If the layout does not have a field object, you will not be able to access it, even if it exists in the FileMaker table that layout is based on.

Empty result sets do not return a result object

Unlike any of the other database drivers that I have ever used, instead of returning an empty result set, FileMaker will return an error object if your find command has no results. It took a little while for me to realize this at first, but once you know what to expect, this shouldn’t cause much of a problem.

FileMaker Date Formats

Anybody who has worked with a database knows the issues that can be caused when trying to perform a query based on a date condition, FileMaker is no exception. During my adventures with the FileMaker API, I came across a problem with not only finding dates, but also inserting date values into the database. Most current database drivers support at least entering dates into the database in almost any format, they will perform the necessary conversions needed without you having to worry about it. FileMaker will not accept just any date format, in order to get my dates to work, I had to use the PHP date format ‘n/d/Y’ whether or not this is the only format accepted, or if it just happened to be the way the file that I was working with was set up, I don’t know. The documentation on the subject is, as far as I can tell, nonexistent. I would love to know more about the subject myself, but if you’re having trouble with dates I suggest you try the format worked for me.

Complex conditional queries

Normally I wouldn’t specify queries where a condition or set of conditions does NOT equal a value as a complex query, but in the FileMaker API it is. Instead of simply being able to change an equals operator (‘=’) to a not equals operator (‘<>’) in a SQL query, FileMaker forces you to call a completely different function, to create a different type of object to perform any query whose condition is not a simple Boolean equals. Instead of creating a new find command, you need to create a new compound find command, which in turn must have at least one find request. These requests can be changed to omit records who are true when a certain condition is met, or change two or more conditions from always being logical AND queries, to logical OR.

None of the issues discussed in the blog are inherent problems with the FileMaker API, but rather inconsistencies with other database solutions. Once you get used to the way FileMaker does things, you will find that there are reasons why these things are the way that they are, and learn to recognize other possible problems before they arise. I hope that this blog helped at least one person with an issue that they were having using the FileMaker PHP API.