Formula Rule Type

Formula Rule Type
Formula rules are derivation rules that compute values and returns this value as 
. Formula rules can reference other columns in the row as 
 as well as parent columns. Specify formula rules using JavaScript notation. When you change the referenced attributes, 
CA Live API Creator
 keeps the column value in sync by invoking your formula rule using reactive logic. 
CA Live API Creator
 invokes your formula rule during logic execution with full forward chaining and dependency-based ordering (
CA Live API Creator
 scans the JavaScript code for 
In this article:
Null Handling
CA Live 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, 
    CA Live API Creator
     returns the value as null.
  • parent.attribute
    . If a parent does not exist, 
    CA Live API Creator
     returns the 
     value as null. If the parent exists, 
    CA Live API Creator
     returns null numeric attributes as 0 and returns null string attributes as the empty string.
  • attribute
    CA Live API Creator
     returns null numeric attributes as 0 and returns null string attributes as the empty string.
Create a Formula Rule
 You have defined an active database with at least one entity (table or view).
Follow these steps:
  1. In API Creator, in the Create section, click 
    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. Click 
    Create a Rule
  3. Select the 
     rule type, the entity to which it applies, and then click 
    Create Rule
    The formula rule page appears.
  4. Define the parameters of the rule:
    The title for the formula rule. The title for rules does not affect logic processing. To apply a default value, leave this field blank.
    The name of the entity and the column being derived.
    The topic that you want to associate to this rule.
    For more information about topics, see Manage Topics.
    The code for your formula rule. You can access and insert JavaScript code examples into the code editor.
    You can have 
    CA Live API Creator
     return an object and assign it to a row attribute. This object represents the current date, time, or timestamp on the database server at the instant the resulting SQL is executed on the database server. You can do this by way of the 
     method or the 
    For more information about these methods, see The SysUtility Object.
    For more information:
    Select to activate the rule.
    See the context help.
    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.
    The name of the formula rule. To apply a default value, leave this field blank.
  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 JavaScript 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 
 variable. Formula rules can also reference old values by way of the 
 variable, for example:
return 100 * ( - /;
For more information about the 
 variable, see Customize your API.
Define Formula Rules that Reference JavaScript Functions
You have added the JavaScript library that contains the function to your API and you have defined the library as available to JavaScript event programming.
if(row.create_timestamp === null)
return new Date();
return row.create_timestamp;
Define Formula Rules that Reference Parent Data
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 Sample API, the 
 formula for lineitems table, the LineItem references its Purchaseorders' 
 attribute as follows:
return row.itemOrder.is_ready;
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.
Parent references are provided for transaction update logic, not for retrieval. You do not need to define new child columns derived from parent columns for retrieval.
Best Practice: 
Create parent subresources that optimize database and network traffic.
For more information about how to create a parent subresource, see Manage Subresources.
Single-Level Parent Reference
For performance reasons, 
CA Live API Creator
 supports cascade for parent references, but not grandparent references. You can access grandparent data, without cascade, by avoiding direct use of the 
 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 ( > 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 ( > 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
You can also use ternary expressions, as shown in the following code snippet:
return > 100 ? 5 : 3;
Access Database Services and Other Utility Functions
Most formula rules are simple expressions, perhaps with conditional logic. You can also provide access to database services and other utility functions your logic might require using context variables, including the 
 object, by employing JavaScript.
For more information about the list of JavaScript context variables, see Extensibility.