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

Sum Rule Type

The sum rule declares 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 more information about foreign key relationships, see 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 sums instead of events. Adjustment is by way of one-row updates, instead of expensive aggregate SQLs, like select sum(...).

  1. In API Creator, select Manage, Rules, and click the Create New Rule.
  2. Select the Sum rule type, the table to which it applies, and click Create Rule.
  3. Define the parameters of the rule:

    Sum name

    The name of the sum rule. 

    Tip: Leave this field blank for the system default.

    Table/column

    The name of the parent table and column representing the sum result.

    Topics

    You can associate this rule with a topic.

    Child entity/column

    Defines child role (first combo box) and attribute (second combo box) being summed.

    Child Qualification

    Boolean JavaScript expression designating the child rows that contribute to the sum; syntax notes:
    • JavaScript means you are using logical operators such as && for "and", || for "or"
    • Equals can be specified either as "=", or "=="
    • Not equals is !=
    • do not use the row.<attrName> - simply use the 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).

    SQLable

    Indicates whether you can optimize the rule by executing it in SQL instead of in JavaScript.

    Just In Time

    You can indicate formulas and aggregates as Just in Time (JIT) rules. You can define JIT rules on persistent attributes and on rules of type formula, sum, count, min and max.

    If the value in the database is null, the rule is executed on the first read and the value is stored and returned. To prevent rules with null return values from executing on every read, the rule should not return a null value.

    1. Click Active and Close to return to the list of rules.
    The sum rule is created.

    Effect

    The value of the attribute is updated 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. Observe that sum processing is triggered by changes to the child, as visible in the log.

    A sum is not recalculated from scratch, but rather adjusted as necessary. For instance, when you add a new (qualifying) child to the parent, the sum attribute is incremented by the child's amount. The performance impact is small.

    For more information:

    Usage Notes

    To maintain high performance, sum values are assumed to be correct on disk. For proper operation, if you define new sums on existing data, initialize these using SQL tools.

    For more information about synchronizing data with logic using a SQL tool, see Data Sources.