Calculations
Calculations, functions, JSON, and SQL — the logic layer of every FileMaker solution.
Bring the messy part
Calculations making a weird face at you?
Book consulting when the lesson makes sense, but your actual file has opinions. We can untangle the design, debug the snag, or map the next build.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Recursive custom functions
Write custom functions that call themselves to process lists, traverse hierarchies, and solve problems that loops cannot handle in calculations.
ExecuteSQL in calculations
Query any table in your FileMaker file from a calculation field using ExecuteSQL, enabling cross-table lookups without portals or relationships.
JSON functions in calculations
Build, navigate, and transform JSON data entirely within FileMaker calculations using the JSON* function family.
Date arithmetic and calendar calculations
Use FileMaker's date functions to calculate durations, find weekday boundaries, handle fiscal periods, and format dates for display.
Aggregate functions in related and portal contexts
Use Sum, Count, Average, Min, and Max across related records and understand the difference between portal-row context and parent-record context.
Stored vs. unstored calculations: performance impact
Understand when FileMaker stores calculation results, when it must recalculate on access, and how to design fields for the performance profile you need.
GetNthRecord: accessing other records in a calculation
Use GetNthRecord to read field values from records other than the current one within a calculation field.
Evaluate() for dynamic expressions
Use Evaluate() to execute a FileMaker expression stored as text, enabling runtime-configurable calculations and formula fields.
Text parsing: Position, Middle, Left, Right, Filter
Extract, locate, and clean substrings using FileMaker's native text functions without regular expressions.
Case() efficiency and short-circuit evaluation
Understand how FileMaker evaluates Case() branches and write conditions in the right order for clarity and performance.
Designing reusable custom functions
Apply naming conventions, parameter design, and documentation practices to create custom functions that are easy to discover and reuse across solutions.
ValueListItems and dynamic value lists in calculations
Use ValueListItems() and related functions to read, filter, and construct value list content from within calculations.
Substitute() patterns for text transformation
Use Substitute() with multiple replacements and nested calls to clean, normalize, and transform text values efficiently.
Calculation field design: stored, auto-enter, and triggers
Choose between stored calculation fields, auto-enter calculation options, and script-set fields based on performance, findability, and recalculation behavior.
Timestamps and time zone handling
Work with UTC storage, local display, and FileMaker's ConvertLocalToUTC function to build solutions that are correct across time zones.
List() function advanced patterns
Use List() beyond basic concatenation to build conditional value lists, deduplicate related values, and construct multi-line display strings from related records.
JSONFormatElements and JSON pretty-printing
Use JSONFormatElements to produce human-readable indented JSON, and understand when compact vs. formatted output is appropriate.
GetNthRecord() patterns and pitfalls
Use GetNthRecord() to compare records within a found set, build running calculations, and understand the sort-dependency that makes it fragile in production.
TextFormatRemove() and text style manipulation
Use TextFormatRemove() to strip character-level formatting from text values and understand how FileMaker stores text styles in field data.
Position() function and multi-occurrence finding
Use the occurrence parameter of Position() to find the second, third, or nth occurrence of a substring, enabling robust multi-delimiter text parsing.
PatternCount() in calculations
Use PatternCount() to count substring occurrences, validate list membership, and build conditional logic based on text patterns.
Middle(), Left(), Right() complex parsing
Combine FileMaker's substring functions with Position() and Length() to parse structured strings, codes, and formatted identifiers reliably.
Let() with multiple variable declarations
Master multi-variable Let() to write complex calculations as readable, step-by-step derivations where each variable builds on the previous.
GetValue() and ValueCount() patterns
Use GetValue() and ValueCount() to iterate, slice, and process return-delimited value lists inside calculations.
Custom function recursion deep dive
Write production-quality recursive custom functions using tail recursion, accumulator patterns, and safe depth guards for list processing and tree traversal.
Calculation performance: stored vs. unstored decision framework
Apply a systematic decision framework to determine whether a calculation field should be stored or unstored, balancing find performance, data currency, and server load.
Case() vs. If() -- when to use each
Choose between Case() and If() based on the number of branches, readability, and short-circuit behavior to write cleaner conditional calculations.
GetSummary() across relationships
Use GetSummary() to retrieve summary field values for a specific sort group within a script or calculation, enabling programmatic access to report totals.
Aggregate functions: Sum, Average, Count, Max, Min in context
Apply FileMaker's aggregate functions correctly by understanding how context, table occurrences, and empty values affect their results.
Date calculations: age, duration, and working days
Build accurate date calculations for age in years, duration in multiple units, and business day counts using FileMaker's date arithmetic.
Time and timestamp arithmetic
Perform accurate calculations with Time and Timestamp fields, including duration, elapsed time, and cross-midnight handling.
Number formatting: dollar amounts, percentages, and display
Format numbers as currency and percentages using FileMaker's Text() function number format masks and avoid the common pitfalls of mixing display with data.
Boolean calculations and truthiness in FileMaker
Understand how FileMaker evaluates truth and falseness in calculations, and write concise Boolean expressions that work correctly in all contexts.
ExecuteSQL advanced patterns
Write multi-table JOINs, subqueries, and CASE expressions in ExecuteSQL to query FileMaker data without relationships or portals.
SQL aggregation in FileMaker calculations
Use ExecuteSQL GROUP BY and aggregate functions to compute cross-table totals, counts, and statistics without relationships or portals.
FilterValues patterns and list manipulation
Use FilterValues(), SortValues(), and UniqueValues() to filter, sort, and deduplicate return-delimited value lists in FileMaker 19.2+ calculations.
Compound key calculations
Design and implement compound key fields that combine multiple values into a single match key for relationships, lookups, and de-duplication.
Self-referencing calculations and recursion limit awareness
Understand how circular field dependencies arise in FileMaker, how the engine handles them, and the recursion depth limit that applies to custom function recursion.
Calculation field as index key: tradeoffs
Evaluate when a stored Calculation field makes a good relationship key or find index, and when the tradeoffs in recalculation behavior and storage make a regular field preferable.
Global field calculations and multi-user concerns
Understand how global fields behave in calculations on FileMaker Server, why each connected user has their own global value, and how to design around multi-user global field limitations.
Calculation debugging techniques
Use the Data Viewer, intermediate Let() variables, and structured decomposition to diagnose and fix misbehaving FileMaker calculations.
Designing calculation field libraries
Organize and document a reusable library of calculation fields and custom functions that can be cleanly transferred between FileMaker solutions.
Calculation code review checklist
Apply a systematic checklist to review FileMaker calculations for correctness, performance, edge cases, and maintainability before shipping.