Group by Rollup Example

The following page details the Group By - total sales by month and salesrep example.

It is common to sub-total object by grouping them. For example, we might to group orders by year, month and salesrep, to monitor sales activity. These totals are maintained in the table empsales.

But, since empsales rows are not real entities (just sub-totals), it is cumbersome to create them. For that, we can use Managed Parent to create them automatically. The result is we can implement our Group By.

The derivations to compute the foreign key and total_sales are straight-forward.

Bill Of Materials Price Rollup

Consider when a Product price is changed and that product is a component of a kit. The Requirement must be that each of the kits reflect the price change (e.g., if a bolt increases in price, so do all the kits that use bolts: wings, fuselages, etc).

Recall the database structure. The following logic illustrates the solution, which are four rules.

They operate as follows when an End User changes a Products.price:

  1. The Product.price is referenced by product_billofmaterials.value. So, when products.price is changed, parent references cascade (Forward Chain) to the product_billofmaterials rows to refire the referencing logic (value) for all the Kits using this component Product
  2. The product_billofmaterials.value value is referenced by Product.sum_components_value, so the system performs automatic adjustment processing for each kit using the component
  3. The products.price references the sum_components_value, so this recurses on steps 1 and 2 for each kit using the component.
This example illustrates key rule behaviors:

Business Logic Pattern

This example illustrates the Replicate Junction pattern, reflecting end-point changes.

For more information about the Replicate Junction pattern, see Logic Patterns.