Formula Rule Type

Formula Rule Type
lac31
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).
 In this article:
2
Create a Formula Rule
  1. In API Creator, go to Manage, Rules, and click 
    Create New Rule
    .
  2. Select the 
    Formula
    rule type, the table to which it applies, and click 
    Create Rule
    .
    The following image shows the Manage, Rules, formula page:
  3. Define the parameters of the rule:
    Formula name
    The name of the formula rule. 
    Tip:
     Leave this field blank for the system default.
    Table/column
    The name of the parent table representing the count result.
    Code
    See the context help.
    Active
    Select to activate the rule.
    Note:
     The definition must be complete.
    SQLable
    See the context help.
    Just In Time
    See the context help or the Mark a Formula as Just in Time section.
  4. Click 
    Activate and Close
    to return to the list of rules.
The formula rule is created.
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
Prerequisite:
 You have selected the function's containing JavaScript libraries.
Formulas can reference JavaScript functions. 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;
}
Formula rules must return a value that is appropriate for its column. For example, a formula rule for a string column must return a string. Numeric types are automatically converted as needed.
For example:
 
if (row.total > 100)
  return 5;      // INVALID - *must* return a value
If a formula rule returns nothing, then the value of the column is unchanged. For example:
if (row.value > 1000) // Do not change
  return;
Formulas can also include 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.