Number formatting: dollar amounts, percentages, and display

Intermediate

Format numbers as currency and percentages using FileMaker's Text() function number format masks and avoid the common pitfalls of mixing display with data.

What you'll learn

How to use Text() with a format mask for currency and percentage display
How to format a number in a concatenated string without field formatting
How to strip formatting when performing arithmetic on a formatted field
When to use field display formatting vs. a calculated display field

FileMaker stores numbers as unformatted values and applies display formatting through field format options or calculated display fields. When formatting is needed inside a calculation -- for export, email templates, or concatenated strings -- use the Text() function with a format mask. Separating data from display formatting is one of the most important principles in FileMaker data modeling.

1/4

Text() with number format masks

Text(number; formatMask) converts a number to a formatted string. Masks use # for optional digits, 0 for required digits, "." for decimal, and "," for thousands separator.

FileMaker Script

// Dollar amounts:
Text ( 1234.5  ; "$#,##0.00" )  // -> "$1,234.50"
Text ( 0       ; "$#,##0.00" )  // -> "$0.00"
Text ( -500    ; "$#,##0.00" )  // -> "$-500.00"

// Percentages (multiply by 100 first if value is a decimal fraction):
Text ( 0.857   ; "#0.0%" )      // -> "85.7%"
Text ( 0.857   ; "#0%" )        // -> "86%"  (rounded)

// Thousands, no decimals:
Text ( 45000   ; "#,##0" )      // -> "45,000"

Time and timestamp arithmetic Boolean calculations and truthiness in FileMaker