Calculations

Calculations, functions, JSON, and SQL — the logic layer of every FileMaker solution.

1

If vs Case in FileMaker calculations

Understand when to use the If function and when to use Case. Case is almost always clearer — here's why, and when If still makes sense.

2

JSONGetElement and JSONSetElement in FileMaker

FileMaker's native JSON functions let you read and build JSON without plugins. Learn the syntax for reading values, building objects, and working with arrays.

3

ExecuteSQL basics in FileMaker

FileMaker's ExecuteSQL function lets you query any table without a relationship. Learn the syntax, common patterns, and the most common pitfall: date and text quoting.

4

Type coercion in FileMaker: GetAsNumber, GetAsText, GetAsDate

FileMaker fields have types, but calculations sometimes return the wrong type for the context. Learn how GetAsNumber, GetAsText, and GetAsDate coerce values and why type mismatches cause silent bugs.

5

IsEmpty() vs = "" vs = 0 in FileMaker calculations

IsEmpty, comparison to empty string, and comparison to zero all test "emptiness" differently in FileMaker. Learn exactly what each one checks and why mixing them up causes silent bugs.

6

Substitute() for text replacement in FileMaker

Substitute() replaces all occurrences of a search string within a text value. Learn how to chain multiple substitutions, use it to sanitize input, and understand its case-sensitive behavior.

7

Text parsing in FileMaker: Left, Right, Middle, and Position

FileMaker's text parsing functions let you extract any substring from a text value by position or by searching for a delimiter. Learn Left(), Right(), Middle(), and Position() for real-world parsing tasks.

8

PatternCount(): searching and counting occurrences in FileMaker

PatternCount() counts how many times a substring appears in a text value. Learn how to use it for existence checks, occurrence counting, and basic text analysis.

9

Date functions in FileMaker: Date, Day, Month, Year, DayName, DayOfYear

FileMaker has a rich set of date component functions. Learn how to construct dates from components, extract day/month/year, get day names, and calculate day-of-year values.

10

Timestamp functions in FileMaker: Timestamp, GetAsTimestamp, date arithmetic

Timestamps combine a date and time into a single value. Learn how to construct and parse timestamps, perform time arithmetic, and handle the common pitfalls of timestamp comparisons.

11

Aggregate functions in FileMaker: Sum, Count, Average, Max, Min

FileMaker's aggregate functions operate across related records in a portal or repeating field. Learn how Sum, Count, Average, Max, and Min work in calculation fields and how to use them on related data.

12

Let() for named variables in FileMaker calculations

Let() allows you to declare named variables within a calculation, avoiding repeated sub-expressions and making complex formulas readable. Learn the syntax, scope, and best practices.

13

While() for iterative loops in FileMaker calculations (FM 19+)

Introduced in FileMaker 19, While() enables iterative loops entirely within a calculation — no scripts required. Learn the four-argument syntax, accumulator patterns, and when to use While() vs a script loop.

14

Custom functions in FileMaker: when to create them and recursion

Custom functions extend FileMaker's calculation language with reusable, named functions. Learn when a custom function is the right abstraction, how to handle recursion limits, and best practices.

15

Summary fields in FileMaker: types, uses, and when to choose calculation instead

Summary fields aggregate data across the current found set in real time. Learn the five summary field types, when to use a summary field vs a calculation, and why context matters for summary results.

16

Stored vs unstored calculations in FileMaker: performance impact

Stored calculations write their result to disk and are fast to read; unstored calculations are recomputed every time they are accessed. Learn which to choose and how unstored calculations affect performance in large solutions.

17

List() for gathering related values in FileMaker

List() returns a return-delimited list of all non-empty values from a field across related records. Learn how to use it for aggregating text, building display values, and feeding other list functions.

18

Common Get() functions in FileMaker calculations

FileMaker's Get() functions return information about the current environment — date, time, user, layout, record count, and more. Learn the most useful ones and how to use them in calculations and scripts.

19

JSONListKeys, JSONGetElementType, and iterating JSON in FileMaker

FileMaker's JSON functions go beyond get/set. Learn how JSONListKeys iterates object keys, how JSONGetElementType identifies value types, and patterns for processing unknown JSON structures.

20

Value list functions in FileMaker: ValueListItems, FilterValues, UniqueValues

FileMaker has a set of functions specifically for working with return-delimited value lists. Learn ValueListItems for retrieving named value list contents, FilterValues for set intersection, and UniqueValues for deduplication.

21

GetAsURLEncoded and GetAsText for URL and display formatting in FileMaker

GetAsURLEncoded percent-encodes a text string for safe inclusion in a URL. Learn when to use it, what characters it encodes, and how it pairs with GetAsText for formatting non-text values.

22

Filter() for extracting specific characters in FileMaker

Filter() returns only the characters from a text string that appear in a specified mask string — making it the standard tool for extracting only digits, only letters, or any custom character set.

23

Calculation context in FileMaker: how layout context affects results

A FileMaker calculation does not exist in a vacuum — it is always evaluated in a specific table context. Learn how layout context determines which table's records a calculation sees, and how unstored calculations can return different results on different layouts.