Docs‎ > ‎CA Live API Creator‎ > ‎Rules‎ > ‎

Sum

The Sum Business Logic 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 example, to derive the balance as the sum of the unpaid order totals:

Derive balance as sum(purchaseorderList.amount_total where paid=false)

Sums are far quicker to define than Events, and they are quite efficient: adjustment is via 1-row updates, not expensive aggregate SQLs like select sum(...).

You define a sum via API Creator.  Click the Create New Rule, and select the Rule type and the table to which it applies.


Next, you define the parameters of the rule (further described below):


When complete, click Active and Close to return to the list of rules.

Your Qualification can include nulls, like this to check null (or != null):

kit_item_ident = null

Parameters


 Sum name  You can supply your own text, but we recommend leaving this blank for the system default
 Table/Column  The name of the parent table and column representing the sum result
 Children  Defines child role (first combo box) and attribute (second combo box) being summed
 Qualification  Optional - 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
 Active  Check to make rule active (requires the definition be complete)

Effect

The value of the attribute will be updated to reflect the sum of the specified attribute in the child objects whenever necessary.  This includes child objects being added to and removed 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 a new (qualifying) child is added to the parent, the Sum attribute will be incremented by the child's amount. This means that there is very little performance impact.


Usage Notes

To maintain high performance (per the adjustment discussion above), sum values are assumed to be correct on disk.  So, if you define new sums on existing data, you need to initialize these for proper operation.

You can do that using SQL tools, like this.