Docs‎ > ‎API Creator‎ > ‎Specify your Business Rules‎ > ‎

Specify Formula Derivation Rules

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. 

Whenever the referenced attributes are changed, Live API Creator uses reactive programming to invoke your formula so the column value is kept in sync. Contrast this with imperative programming, where such dependency management often accounts for substantial code to detect changes and update dependent data.

Live 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 formula name on the Manage, Rules page:

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 their containing libraries are enabled. 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. See here for important information about Role Names.

The database commands required to access parent data are automated. This reduces coding, and helps ensure good performance by automatic caching.
For example, see the Make Ready Use Case, where the LineItem references its Purchaseorders' isReady attribute as follows:

return row.itemOrder.isReady;

Child Cascade is automated, so that changes in such parent columns are cascaded to each related child row. You can reference parent data without cascade by using a Parent Copy.

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.  It is a Best Practice to use Parent Sub Resources that optimize database and network traffic.

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, as follows:
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:
if (row.total > 100) {
   return 5;
} else {
   return 3;
}

As shown in the example above, your formula must return a value.  You cannot do this:
if (row.total > 100)
  return 5;                   //  INVALID - *must* return a value


You can also use ternary expressions as shown below:
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.  So, 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 below for attribute references

  • attribute - to simplify null checking,
    • null numeric attributes are returned as 0
    • 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.

Live API Creator includes additional context, including logicContext. This provides access to database services and other utility functions your logic may require.