Creating APIs‎ > ‎Logic‎ > ‎Rule Types‎ > ‎

Formula Rule Type

Formulas are derivation rules that compute the value of a table.column as the return value. They can reference other columns in the row as row.attribute as well as parent columns. Specify formula derivation rules using JavaScript notation. When you change the referenced attributes, API Creator keeps the column value in sync by invoking your formula using reactive programming. API Creator invokes your formula logic during logic execution with full forward chaining and dependency-based ordering (JavaScript code is scanned for row.attribute references).

For more information:

The following image shows the Manage, Rules, formula page:

Mark a Formula as Just In Time

You can specify a formula as Just in Time (JIT). You can only use JIT if null is not a possible value. If you specify a formula as JIT and the value in the database is null, API Creator executes the rule on the first read, stores the value for that column in the database, and returns the value. You can prevent API Creator from executing rules with null return values on every read by ensuring that the rule does not return a null value. 

For more information about JIT, including how to specify to have API Creator recompute your formula when/as it accesses the data and detects null vales, see Synchronizing Data with Logic.

Reference Attributes

You can define formulas for a table and can reference any column of that table by way of the row variable. Formulas can also reference old values by way of oldRow, so you can specify:

return 100 * (row.total - oldRow.total) / row.total;

For more information about the row variable, see Define Custom REST Resources.

Reference JavaScript Functions

Formulas can reference JavaScript functions, provided you have selected their containing JavaScript libraries. For example:

//create_timestamp

if(row.create_timestamp === null)
    return new Date();
else
    return row.create_timestamp;

Parent References

Formulas can also reference parent attributes (one side of a one-to-many relationship). To reference parent data, use the dot-notation reference to the parent role/attribute. The database commands required to access parent data are automated. This reduces coding, and helps ensure good performance by automatic caching.

For example, in the Make Order Ready example, the LineItem references its Purchaseorders' isReady attribute as follows:

return row.itemOrder.isReady;

Child cascade is automated. Changes in such parent columns are cascaded to each related child row. You can reference parent data without cascade processing by using a parent copy rule type.

For more information:

For Transaction Update Logic

Parent references are provided for transactional update logic, not for retrieval. You do not need to define new child columns derived from parent columns for retrieval.

Best Practice: Use parent sub-resources that optimize database and network traffic.

For more information about using parent sub-resources, see Customize your API.

Single-Level Parent Reference

For performance reasons, the system supports cascade for parent references, but not grandparent references. You can access grandparent data, without cascade, by avoiding direct use of the row variable, for example:

var currentRow = row;
var gpValue = currentRow.parent.grandparent.value; // not row.parent.grandparent.value

If/Else

Formulas can be conditional. Per use of JavaScript, you can use if/else statements, for example:

if (row.total > 100) {
   return 5;
} else {
   return 3;
}

Your formula must return a value, for example:

if (row.total > 100)
  return 5;                   //  INVALID - *must* return a value

You can also use ternary expressions as shown in the following code snippet:

return row.total > 100 ? 5 : 3;

Null Handling

References to null attributes in expressions are treated specially to reduce null pointer exceptions, as follows:
  • parent. If a child references a missing optional parent role name, the value is returned as null. Employee logic can refer to onLoanDepartment, which returns null if the foreign key is null.
  • parent.attribute. Is null if no parent. If the parent exists, it is treated as described for attribute references.
  • attribute. To simplify null checking, null numeric attributes are returned as 0 and null string attributes are returned as the empty string.

    Context, Logic Context

    Most formulas are simple expressions, perhaps with conditional logic as shown above. But you are not restricted. You can employ the full power of server-side JavaScript.

    You can provide access to database services and other utility functions your logic may require using context variables, including logicContext.

    For more information about the list of JavaScript context variables, see Quick Reference.