Make FileMaker calculations clearer with Let

A calculation becomes harder to review when the same expression appears several times and the business decisions are hidden among parentheses. Let gives intermediate values names, so you can read the calculation in the order it works.

Our example calculates one discounted line total. It is a practice rule, not a tax or invoicing specification. Let is available from FileMaker Pro 7; FileMaker Pro is required and sold separately.

1. State the rule in ordinary words.

For this exercise, multiply quantity by unit price, subtract a percentage discount, then apply a percentage tax to the discounted amount. Round only the final amount to two decimal places.

Quantity: 3
Unit price: 12.50
Discount rate: 0.20 (20%)
Tax rate: 0.08 (8%)

The expected subtotal is 37.50, the discount is 7.50, the discounted amount is 30.00 and the final amount is 32.40. Writing these checkpoints first gives you something independent of the formula to compare.

2. Give each intermediate value a name.

FileMaker calculation

Let ( [
    quantity = 3 ;
    unitPrice = 12.50 ;
    discountRate = 0.20 ;
    taxRate = 0.08 ;
    subtotal = quantity * unitPrice ;
    discountAmount = subtotal * discountRate ;
    discountedTotal = subtotal - discountAmount ;
    total = Round ( discountedTotal * ( 1 + taxRate ) ; 2 )
] ;
    total
)

Evaluate this in Data Viewer. Expect the numeric value 32.4; a field formatted to two decimal places may display 32.40. Display formatting and numeric rounding solve different problems.

Let evaluates its definitions in order, so a later definition can use a preceding one. The final expression after the closing square bracket is the value the calculation returns.

3. Inspect one part at a time.

In your practice copy, temporarily replace the final total with discountAmount. Expect 7.5. Then try discountedTotal, expecting 30. Restore total afterward.

This is useful when the output looks plausible but is wrong. If the subtotal is correct and the discount is wrong, you have narrowed the investigation. A formatted expression helps you see the structure, but it cannot decide whether your business rule is right.

4. Replace constants deliberately.

Once the sample works, replace the first four values with your own numeric fields or script inputs. Confirm their table occurrence context. Validate quantity and prices before calculating; decide whether negative quantities represent returns and whether rates outside zero to one are allowed.

The unprefixed names above are local to this Let calculation. They are different from script variables beginning with $ and global variables beginning with $$. Prefer these local names when the intermediate result is needed only inside this expression.

Do not add currency symbols or thousands separators to numeric inputs. Keep the numeric result separate from the label or display format used in your layout.

5. Test the rules that might change.

Zero discount: expect 40.50.
Zero tax: expect 30.00.
Quantity zero: expect 0 under this rule.
100% discount: expect 0.
Blank or invalid inputs: verify your validation response rather than accepting an accidental zero.

Rounding at every intermediate step can produce a different result from rounding once at the end. Your actual document rules may require per-line or per-tax rounding; make that choice explicit and test it with fractional values.

Use the calculation formatter to review indentation and delimiters. Let can make repeated expressions easier to maintain, but adding it does not by itself establish correctness or better performance. Keep a small set of known input/output examples whenever you revise the formula.

PUT IT INTO PRACTICE

Try it with Filesoft.

Open the calculation formatter

Want a guided learning path? Explore the free FileMaker courses.

Continue reading

Reference: Let · Round. Examples are learning aids; check them in your own FileMaker working copy.

Make FileMaker calculations clearer with Let.