Intro

What is Claris FileMaker? A platform for developing (______).

What’s with this blank? Right, that’s the part. It is rather difficult to determine its best use case scenario. And judging from my knowledge, I can’t come up with one.

If I ever wager on a proper use case, my money is on building early stage prototypes. It wouldn’t make too much sense otherwise.

Scenarios where low-code platforms might work:

Small business applications: simple CRMs, inventory trackers, project management tools, small contact management systems, event scheduling tools, invoicing…

That sounds like a long list.

Is it, though?

Why not use out-of-the-box tools? You can use Airtable or Google Contacts for robust contact management, they’re free for small-scale use and come with great built-in features. You can have Calendly, Square Appointments for appointment scheduling, Shopify for inventory management… Now this is what I would consider a long list. More importantly, those dedicated software solutions are way more cost-effective, user-friendly, and they provide more migration options.

Anyway, let’s not stray too far from the topic.

These notes are based on the FileMaker Pro 14 User's Guide. Although it's not the latest version, it seems Claris hasn't significantly changed the core functions. I believe this guide will serve my purposes adequately.

What are we talking about when we talk about Claris FileMaker?

Claris FileMaker, or, Claris, is consisted of following products:

• FileMaker Pro - client and development software

• FileMaker Server - software for sharing or hosting an app (FileMaker solution) with a team

• Cloud - Server but it’s on AWS

Update: they no longer use AWS for cloud service since Jan, 2022

• Go - client software for iPhone and iPad

• WebDirect - well, you go with this one when you don’t have an iPhone or iPad

• Connect - for connecting FileMaker with popular third-party apps

• Studio - for collecting data, Claris Platform exclusively

What are those Claris files, exactly?

It seems that those acronyms are used interchangeably: solution, app, system, database.

Under the hood, there are components like tables, layouts, scripts, objects, and records. However, these terms don't always align with the concepts we're familiar with in traditional development.

Fields in tables

Claris FileMaker has its own set of field types, which, somewhat map to SQL database field types.

• Text VARCHAR, TEXT, CHAR

FileMaker text fields can hold up to 1 billion characters. FileMaker doesn’t support defining a maximum length for a text field like VARCHAR(255) in SQL, which might not be ideal for storage efficiency.

• Number INT, FLOAT, DECIMAL, etc.

FileMaker number fields offer up to 16 digits of precision, which is close to FLOAT or DOUBLE but does not reach BIGINT or DECIMAL. While it’s doable with relatively large numbers, calculations involving very large or very small numbers may lose precision due to floating-point rounding errors.

Storing large integers as text fields could be a workaround to implement BIGINT-like functionality, custom scripting and calculated fields might be another option, but they’re not even close to the reliability of SQL.

• Date DATE

It holds dates only, using the Gregorian calendar.

• Time TIME

• Timestamp DATETIME, TIMESTAMP

Note that FileMaker’s Timestamp combines date and time but does not offer time zone handling.

• Container BLOB

In FileMaker, containers are built into the application’s design, namely, performance might slow down then managing large files directly in the database.

• Calculation Similar to computed or generated columns

They don’t store data unless specified as “store”, namely, performance might be impacted if recalculated often in large datasets. Although FileMaker supports a wide range of built-in functions, it doesn’t support SQL-like user-defined functions.

• Summary Aggregate functions (SUM, AVG)

• Auto-Enter Default values, AUTO_INCREMENT

In summary, I wouldn’t consider FileMaker suitable for applications requiring precise control over large numeric values or extensive datasets.

Relationships

In chapter 3, the user guide explains the basics of how to:

plan a FileMaker Pro solution

define and modify fields

define tables

create layouts

work with fields, objects and parts on a layout

What grabbed my attention in this part is matching fields for relationships.

While FileMaker is indeed categorized as a relational database management system, it handles relationships differently than SQL-based ones.

To begin with, instead of writing SQL join statements, users drag lines between fields to define relationships.

Defining one-to-one and one-to-many is straightforward enough, all you need to do is connecting fields with unique identifiers (say, primary keys) on each side.

As for many-to-many, it’s not supported directly. A typical approach is to add a join table in the Relationships Graph and have it linked to the other tables via one-to-many. Wait a second, that sounds an awful lot like MyBatis, doesn’t it? It does resemble MyBatis in some ways. In MyBatis, many-to-many is established by defining association mappings explicitly, handling via nested queries or result maps. They both lack the automatic many-to-many handling many ORMs offer.

Another aspect that concerns me is FileMaker's lack of constraints, particularly the absence of enforced foreign keys. I can envision a scenario—one I encounter more often than I'd like—where orphaned records persist. Unlike situations where I might have forgotten to write constraints, here there's no mechanism to prevent this issue from occurring.

The same goes for primary keys, there’s no formal enforcement of a primary key constraint.

While it’s accessible to display fields from tables on layouts or in calculations, it’s implicit rather than declarative. The lack of an actual join makes queries quite cumbersome. “Lookups” and “Portals” might suffice in some cases, but I wouldn’t get my hopes up when it comes to complex joins or dynamic filters. Scripting is a way to work around, but it it can get inefficient. On top of it all, FileMaker doesn’t come with recursive queries support.

Limits, limits everywhere

Some might argue that for small businesses without large datasets or complex calculations, FileMaker is perfectly adequate. However, I remain skeptical.

Imagine a scenario where a local bakery use FileMaker for their management system, they want to evaluate ingredients usage trends, inflation impacts on costs, and product popularity over time—FileMaker can handle some aspects but might fall short in many.

1. Each product may contain multiple ingredients, and some ingredients are used across several items. This requires a many-to-many relationship setup for accurate inventory and cost calculations, as each ingredient impacts multiple products.

1. Ingredient usage isn’t just a simple one-to-one or one-to-many relationship.

1. Ingredients often experience price fluctuations, the system should be able to track historical price changes, which requires precise data tracking.

1. Understanding which products sell best at different times (weekly, monthly, or seasonally) involves querying data over specific time frames—something SQL handles well but FileMaker cannot. Not without custom scripting.

1. SQL databases integrate seamlessly with reporting tools like Power BI or Tableau, making it efficient to report detailed visualization of trends, costs, and usage. FileMaker’s basic reporting features might not be enough.

While FileMaker is easy to use for basic operations, small businesses like bakeries face complex, data-heavy needs that it wasn’t designed to handle efficiently.

Every system has its limitations, but when there are too many, they become obstacles. The way I see it, small business needs aren’t necessarily “small”, and they demand and, dare I say, deserve a system that can handle their full data complexity without hindrances.

Even if my boss were to encourage me to explore FileMaker for new opportunities, as someone who isn’t a complete newbie in coding, I find it challenging to hold it in high regard.

To summarize it, Claris FileMaker falls into a challenging middle ground: it’s insufficient for developers needing flexibility and power, but often overwhelming for common users due to its complexity and limitations. While it provides some easy-to-use tools, it lacks the robust capabilities developers need for advanced data handling, customization, and scalability. This leaves FileMaker best suited for very basic applications, where both advanced development and user simplicity aren’t critical.

Even so, there are numerous dedicated, purpose-built software solutions available for consideration.