CA Live API Creator 4.1

5.4 5.3 5.2 5.1 5.0 4.1 4.0 3.2 3.1.1 3.1
English

Sum Rule Type

Sum Rule Type
calac41
Sum rules are derivation rules that declare a parent attribute to be maintained as the sum of a designated child role's summed attribute, with an optional qualification to filter child objects. Parent reference names are derived from foreign key relationships.
For example, to derive the balance as the sum of the unpaid order totals:
Derive balance as 
sum
(purchaseorderList.amount_total where paid=false)
Use sum rules instead of events. The impact on performance is small. Adjustment is by way of one-row updates instead of expensive aggregate SQLs, like 
select sum(...)
. 
CA Live API Creator
 updates the value of the attribute to reflect the sum of the specified attribute in the child objects whenever necessary. This includes adding and removing child objects from the parent object, as well as modifications to the children objects that change their qualification in the sum or the summed value. Sum processing is triggered by changes to the child. For example, when you add a new (qualifying) child to the parent,
CA Live API Creator
 increments the sum attribute by the child's amount.
You can view the changes in the log.
Best Practice:
 To maintain high performance,
CA Live API Creator
 assumes that sum values are correct on disk. For proper operation, if you define new sums on existing data, initialize them using SQL tools.
For more information about how to synchronize data with logic using a SQL tool, see Database Connectivity.
For more information:
  • About the update adjustment logic of rules, see Performance.
  • About the log, see Debug.
Create a Sum Rule
Prerequisite:
 Your API is connected to an active database that contains at least one entity or view.
Follow these steps:
  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. Click 
    Create a Rule
    .
  3. Select the 
    Sum
     rule type, the entity to which it applies, and then click 
    Create Rule
    .
  4. Define the parameters of the rule:
    Title
    The title for the sum rule. The title for rules does not affect logic processing. To apply a default value, leave this field blank.
    Required:
     No
    Entity/column
    The name of the parent entity and column representing the sum result.
    Topics
    The name of the topic to which this rule is associated.
    Child entity/column
    Defines the child role (first combo box) and attribute (second combo box) being summed.
    For more information about the child role, see Database Connectivity.
    Child Qualification
    The boolean JavaScript expression designating the child rows that contribute to the sum.
    Syntax Notes:
    • Use logical operators such as && for "and", || for "or".
    • Specify equals as "=" or "==".
    • Specify not equals as !=.
    • Use the attrName, not row.<attrName>.
    Child qualifications can include nulls, for example, the following qualification check null (or != null):
    kit_item_ident = null
    Optional: 
    Yes
    Active
    Select to activate the rule (The definition must be complete).
    Default: 
    Cleared
    SQLable
    Indicates whether you can optimize the rule by executing it in SQL instead of in JavaScript.
    Default:
     Cleared
    Just In Time
    Select to specify that 
    CA Live API Creator
     recompute the data values.
    If 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. To prevent 
    CA Live API Creator
     from executing rules with null return values on every read, the rule should never return a null value.
    Specifying sum rules as Just in Time (JIT) can affect performance whenever
    CA Live API Creator
     computes new values 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.
    Name
     
    The name of the sum rule. Leave this field blank for the system default.
  5. Click
    Activate and Close
     to return to the list of rules.
The sum rule is created.