Value Aggregation

To support reporting purposes, for example, you can set up Juston to aggregate invoice and invoice line item fields on related object records. Generally, there are two ways to do so:

Value Aggregation Using Prefixed Fields

JustOn allows for aggregating the values of specific invoice line item fields on subscriptions or items.

To this end, you configure aggregation fields on the item or subscription, where the API name is the same as the invoice line item field, prefixed with CALC_. To aggregate, for example, the quantity of a particular line item, you map the field Quantity__c (the API name) of the invoice line item to the field CALC_Quantity__c (again, the API name) of the item.

The aggregation is executed during the finalization of the invoice.

Info

The value aggregation is available as of JustOn 2.42.

Rules and limitations
  • You can only aggregate number fields (currency, number, percent and formula fields).
  • Decimal places of aggregation fields must be equal to the source fields to avoid rounding issues.
  • Empty fields result in CALC_ fields with a zero (0) value.
  • Canceled invoices and cancellation invoices are ignored by the calculation.
  • Partial credits are included in the calculation, so check beforehand how it will affect the calculated quantities, that is, when refunding only a currency amount.
  • The limit of aggregation fields per subscription or item is 100.
  • The limit of invoices per subscription or item is 50.000.
Examples
Subscription Field (API Name) Description
CALC_PosTotalNet__c Calculates the sum of the PosTotalNet__c field of all invoice line items that belong to the same subscription whose invoice status is either Open, Paid or Settled.
Item Field (API Name) Description
CALC_Quantity__c Calculates the sum of the Quantity__c field for all invoice line items of this item.
CALC_PosTotalNet__c Calculates the sum of the PosTotalNet__c field of all invoice line items that belong to the same item whose invoice status is either Open, Paid or Settled.

To add aggregation fields to the item or subscription:

  1. Navigate to the fields list of the Item or Subscription object.

  2. Click New to create the aggregation fields as required.

    You can aggregate the values of all fields with numeric values, like quantity, price, discount amount, etc.

    Note that the aggregation fields must have the same API name as the source fields, prefixed with CALC_.

For help about creating fields, see Managing Object Fields.

Flow-Based Value Aggregation

Your business may require to show certain information of related invoices on an account or the source object based on which invoices are generated, like opportunities or contracts. To this end, you can set up a flow that calculates an aggregation on invoices and writes the result to fields on a source object.

Info

The flow-based value aggregation is available as of JustOn 2.53.

Think of the following example: You (repeatedly) generate invoices based on opportunities, that is, you produce multiple invoices from one opportunity. Now you want to show the number of generated invoices and the sum of the invoice totals on the corresponding opportunity record.

value_aggr_process
Aggregating invoice values on a related parent record

If configured accordingly, the flow counts the produced invoices as well as sums up the grand total of all produced invoices, and then writes the result to the corresponding fields of the opportunity.

Info

The implemented aggregation uses SOQL aggregate queries internally where the result is grouped by the field that points to the parent.

Rules and limitations
  • The flow ignores draft invoices.
  • Aggregating blank fields may produce a blank result.
  • The limit of records per parent is 50.000.

Setting up the flow-based value aggregation involves two tasks:

Creating Aggregation Fields

On the related source object, you create the target fields for the values to be aggregated. These fields must comply with the following naming pattern: <PREFIX>_<FUNCTION>_<FIELDNAME>.

Field Name Part Description
PREFIX Specifies the group of aggregation target fields on the source object (by default INV).
If you use another prefix for your aggregation target fields, make sure to specify it when defining the process variables.
FUNCTION Specifies the intended aggregate function: AVG, COUNT, COUNT_DISTINCT, MIN, MAX or SUM.
FIELDNAME Specifies the API name of the field that is to be aggregated.
Aggregation target field examples
API Name Description
INV_SUM_Balance Calculates the sum of Balance of all non-draft invoices for a defined source object record.
INV_SUM_GrandTotal Calculates the sum of GrandTotal of all non-draft invoices for a defined source object record.
INV_COUNT_Id Calculates the number of invoice records linked to the source object record.

To add the aggregation fields on the source object:

  1. Navigate to the fields list of the source object.

  2. Click New to create the aggregation fields as required.

    Make sure to comply with the naming pattern as described above.

    For help about creating fields, see Managing Object Fields.

Creating Aggregation Flow

Calculating the aggregation and updating the source record requires a flow that calls the appropriate Apex class provided by JustOn.

Note

Depending on your use cases, the flow setup and the objects to involve will vary.

  1. Click to enter Setup, then navigate to Process Automation > Flows.
  2. Click New Flow.
  3. Select Record-Triggered Flow and click Create.

  4. Configure the Start element.

    Option Description
    Object The object whose record modifications are to update a source record, must be Invoice
    Trigger The type of record change that triggers the flow
    Conditions The use case-specific trigger conditions, one or more filter criteria for evaluating certain field values
    Optimize for Actions and Related Records

  5. Click and add the following Action element.

    Option Description
    Action The Apex class to call, must be Aggregate fields and write results to target records
    Input Values Invocable variables for the Apex class
    Fieldname for the Target Record: string value that specifies the API name of the Invoice field that contains the ID of the source record
    Record Id: specifies the reference to the ID field of the relevant invoice, like {!$Record.Id}
    Prefix: optional string value that specifies the prefix of your aggregation fields, required if you do not use the default prefix INV

    Specify a label and an API name as required.

  6. Click Save.

    Specify a label and an API name as required.

  7. Click Activate.

    When the defined conditions are met, JustOn triggers the flow. It aggregates the configured invoice fields and writes the results to the target fields on the source object record.

    For help about creating flows, see Flows in the Salesforce Help.

Example use case: Aggregate values of invoices created from an opportunity

Assume the following example: You (repeatedly) generate invoices based on opportunities, that is, you produce multiple invoices from one opportunity. Now you want to show the number of generated invoices and the sum of the invoice totals on the corresponding opportunity record when a new invoice is finalized.

Following this example, you need the following aggregation target fields on the Opportunity object:

Field Data Type Description
INV_COUNT_Id Number Calculates the number of invoice records
INV_SUM_GrandTotal Number Calculates the sum of GrandTotal of all non-draft invoices

Then you set up the flow as follows:

Flow Element Option Value
Start Object Invoice
Trigger A record is updated
Conditions All Conditions Are Met (AND)
1: ONB2__Status__c Equals Open
2: ONB2__Status__c Is changed {!$GlobalConstant.True}
Action Action Aggregate fields and write results to target records
Input Values Fieldname for the Target Record: ON_Opportunity__c
Record Id: {!$Record.Id}

This triggers JustOn to execute the flow when a new invoice created from the opportunity is finalized, that is, set Open: It calculates the number of generated invoices and the sum of the invoice totals and writes the resulting values to the aggregation fields on the opportunity.