Are your FileMaker scripts idempotent?

Idempotency? Eedemwhat? Huh? Never heard of it?

It is pronounced either ee-dem-po-ten-see or id-em-po-ten-see.

In real life, an example is pressing the button to cross at a pedestrian crossing. When someone presses the button, it registers a crossing request. The pedestrian walk indicator is added into the light changing schedule. If someone across the road also presses the button, there is no further action since a crossing request has already been made. And if someone comes along and presses the button 10 times, it doesn't make extra requests or make the lights change any faster. But try explaining that to some people!

So what has this got to do with FileMaker scripting? For some scripts it is important that if they are run multiple times, there is the same net effect. For other scripts it is not relevant or desired.

Find recent emails

Consider the FileMaker table in which I am writing this email content. I have a button that runs a script to find all recent emails. "Recent" is defined in the script as emails that are scheduled to be sent within the last five days.

When I press the button, the script finds all recent emails. But what happens when I press the button again? The script finds all recent emails. This would seem to be idempotent since if I press it multiple times, it has the same effect as pressing it once.

But what if someone else creates a new email record or deletes an email record from the last five days? If I press the button again, I will get a different found set. Isn't that a different "effect", thus making the script non-idempotent? No. The intended effect of the script is to set the current found set to all email scheduled within the previous five days. And that is exactly what it does every time you press the button. It is idempotent.

Apply a discount

Now consider an invoice or sales record. You have a button that will apply a 10% discount. It does this by adding a line item. The description is "Loyalty discount", the line type is "discount" and the amount is calculated as -10% of the sum total of all "sales" lines.

When you press the button, the discount line item is created.

However if you press the button again, another discount line item is created. This script is non-idempotent. It can have serious effects if a user accidentally double-clicks the button. Or if a user clicks the button, not knowing that a discount has already been added to the invoice.

To make this script idempotent we need to first check if a discount line item has already been created. This is a characteristic of some idempotent processes – they check the state before proceeding.

In this case we could search all the line items for one with a line type of "discount". If a discount is found, we could just do nothing or alert the user that the process failed because the invoice already has a discount applied.

API call

In a few systems that I maintain, there is a process to call an API. This call returns data in JSON format for recently added records. Each time the script runs, some of the records returned have previously been processed into the database. If we simply create new records for every record in the returned JSON, we will necessarily create duplicate records.

The API call process needs to be idempotent in respect that if a record has already been created then it should not be created again. It should simply be ignored because the API record data is immutable and already exists in the database.

This idempotency is effected by processing each JSON record and first extracting the unique API identifier for the record. The FileMaker table is searched for this identifier. If a FileMaker record is found then the process moves on to the next JSON record; otherwise, a new FileMaker record is created and populated with the JSON data (including the API identifier).

The API identifier should be stored in a field with unique value validation. Otherwise, two simultaneous script sessions could both fail to find a record and both create one. If the second record creation fails because the API identifier is already present, the script treats that error as meaning another session has already processed the record and revert as needed.

The desired final state is: for every relevant API identifier, there is exactly one corresponding FileMaker record containing the appropriate data. Repeating the process preserves that state.

Does this matter?

For an experienced FileMaker developer, the natural tendency is to create scripts that “do the right thing.” They may not realise that this quality is often called idempotency.

Not every script needs to be idempotent, and some should not be. But it is always worth asking: “What happens if this script runs twice?”

If the answer is “it safely produces the same intended result,” the script is idempotent. If the answer is “it repeats or compounds an action,” that may be correct—but it should be deliberate and protected against accidental repetition where necessary.