HORNEKS ANS

FileMaker: Ad Hoc Calculations in Numeric Input Fields

by · Published · Updated

Sometimes a numeric input field needs more than a single number. You may want to add several amounts, apply a percentage, or calculate a sales price. Instead of opening a separate calculator and copying the result into the field, you can perform the calculation directly from your FileMaker solution.

FileMaker’s Evaluate function provides one way to do this, but its calculation engine exposes more functionality than a simple input calculator needs. Depending on the expression and the solution’s design, user-entered calculations could query data or affect global variables. Asking users to enter database references such as table::field also makes a routine calculation unnecessarily cumbersome.

The ACF plugin now offers a built-in calculator for this purpose. For a price field, for example, users can add amounts or calculate a sales price, then send the result back to the field.

You can make the calculator available through a custom menu item with a keyboard shortcut. A script reads the current field, opens the calculator, and writes the accepted result back to that field. The script can also supply a JSON object containing variables relevant to the current context, such as sales price, cost price, or quantity. Users select these variables by name without needing to know the underlying database structure.

Variables can contain either numbers or formulas. For example, a variable named db could contain the formula s-c, referring to supplied sales-price and cost-price variables. The calculator evaluates the definition behind the scenes while keeping db in the user’s expression.

The calculator returns a JSON object containing the result value, a status of ok or cancelled, and, when accepted, the expression used. You can retain the expression and its variable definitions for repeated calculations.

The calculator evaluates expressions using only the supplied variables, built-in mathematical operations, and its function library. It does not execute ACF functions or access host runtime data. It follows the usual order of operations: multiplication and division take precedence over addition and subtraction.

The calculation engine is adapted from the FormulaCalculator project and maintained as an independent copy within the ACF plugin.

Simple Layout

Standard Layout

Available Variables

The Simple layout with the variable list open:

Implementation Example

The following ACF function opens the Simple calculator with two numeric variables, s and c, and a formula variable, db. It returns the accepted result, or converts the original expression to a number if the user cancels. The calling FileMaker script handles reading and updating the field.

This example requires ACF Plugin 1.8.0.12 or later.

function RunCalcFloat (string expr)
    expr = substitute (expr, ",", ".");
    json var = JSON ("s", 149.90, "c", 77.70, "db", "s-c");
    json res = ACF_calculatorDialog ( expr, var, "Simple");
    if (string(res.status) == "ok") then
        return float(res.value);
    end if
    return float(expr);
end

See a more complete example in the ACF-Library:

https://horneks.no/acf-library?id=137

You may also like...