Formula Rule Type

Formula Rule Type
lac40
Formula rules are derivation rules that compute the value of a 
entity.column
as the
return
value. They can reference other columns in the row as 
row.attribute
 as well as parent columns. Specify formula rules using JavaScript notation. When you change the referenced attributes, API Creator keeps the column value in sync by invoking your formula rule using reactive logic. API Creator invokes your formula rule 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
Prerequisite:
You have defined an active database with at least one table or view.
  1. In API Creator, in the Manage section, click
    Rules
    .
    If your API does not have existing rules, the Welcome to Rules page appears. If your API has existing rules, they are listed on the By entity tab by default.
  2. Complete one of the following:
    • If you have not defined any rules, on the Welcome to Rules page, click
      Create a Rule
      .
    • If you have at least one rule defined, on the By entity tab, click 
      Create New Rule
      .
  3. Select the 
    Formula
    rule type, the entity to which it applies, and then click 
    Create Rule
    .
    The following image shows the formula rule page:
    Screen Shot 2017-03-03 at 4.37.59 PM.png
  4. Define the parameters of the rule:
    Formula name
    The name of the formula rule.
     
    Leave this field blank for the system default.
     
    Entity/column
    The name of the entity and the column being derived.
    Code
    The code for your formula rule.
    For more information the syntax for formula rules, see Formula Rule Syntax.
    Active
    Select to activate the rule.
    Default:
     Cleared
     
     The API must be complete.
     
    SQLable
    See the context help.
    Default:
     Cleared
    Just In Time
    Select to specify that
    CA Live API Creator
     recompute the data values. You can only use JIT if null is not a possible value. If you specify a formula rule as JIT and the value in the database is null,
    CA Live 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 
    CA Live API Creator
    from executing rules with null return values on every read by ensuring that the rule does not return a null value.
    Specifying formula rules as Just in Time (JIT) can affect performance whenever new values are computed on the fly.
    For more information about JIT, including how to specify to have 
    CA Live API Creator
     recompute your rules when/as it detects null vales, see Synchronize Data with Logic.
     
    Default:
     Cleared
  5. Click 
    Activate and Close
    to return to the list of rules.
The formula rule is created.
Formula Rule Syntax
You can use the following JavaScript code examples or explore the examples that are available in API Creator.
For more information about the JavaScript code examples that you can access and insert into the code editor in API Creator, see Code Examples.
Define Formula Rules that References Attributes
You can define formula rules for an entity and can reference any column of that table or view by way of the
row
variable. Formula rules can also reference old values by way of the 
oldRow
variable, for example:
return 100 * (row.total - oldRow.total) / row.total;
For more information about the
row
variable, see Customize your API.
Define Formula Rules that References JavaScript Functions
Prerequisite: 
You have selected the function's containing JavaScript libraries.
You can define formula rules that reference JavaScript functions. For example:
//create_timestamp 
if(row.create_timestamp === null)
    return new Date();
else
    return row.create_timestamp;
Define Formula Rules that Reference Parent Attributes
Formula rules can 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;
API Creator cascades changes in the parent columns to each related child row. You can reference parent data without cascade processing by creating a parent copy rule.
 For more information:
Parent References 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, API Creator 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
Define Conditional Formula Rules
Formula rules 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;
You can also use ternary expressions, as shown in the following code snippet:
return row.total > 100 ? 5 : 3;
Null Handling
API Creator reduces null pointer exceptions by handling references to null attributes in expressions in the following ways:
  • parent
    . If a child references a missing optional parent role name, API Creator returns the value as null.
    Employee
    logic can refer to
    onLoanDepartment
    , which API Creator returns null if the foreign key is null.
  • parent.attribute
    . Is null if no parent. If the parent exists, API Creator treats the parent as described for
    attribute
    references.
  • attribute
    . To simplify null checking, API Creator returns null numeric attributes as 0 and returns null string attributes as the empty string.
Context Variables and Logic Context
Most formula rules are simple expressions, perhaps with conditional logic. 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.