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.

Process-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 process that calculates an aggregation on invoices and writes the result to fields on a source object.

Info

The process-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 process 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 process ignores draft invoices.
  • Aggregating blank fields may produce a blank result.
  • The limit of records per parent is 50.000.

Setting up the process-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__c Calculates the sum of Balance__c of all non-draft invoices for a defined source object record.
INV_SUM_GrandTotal__c Calculates the sum of GrandTotal__c of all non-draft invoices for a defined source object record.
INV_COUNT_Id__c 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 Process

Calculating the aggregation and updating the source record requires a process that calls the appropriate Apex class provided by JustOn. The process criteria that start the execution depend on your use cases.

  1. In Setup, open Process Builder.

    In Salesforce Lightning, navigate to Process Automation > Process Builder.

    In Salesforce Classic, navigate to Create > Workflow & Approvals > Process Builder.

  2. Click New.

  3. Specify a name and an API name as required.
  4. Set the process to start when A record changes.
  5. Click Save.
  6. Select the object Invoice.
  7. Set the process to start when a record is created or edited.
  8. Define the process criteria as required.
  9. Select Apex as immediate action, and specify the class Aggregate fields and write results to target records.

  10. Set the following Apex variables:

    • For Record Id, set the Field Reference value [ONB2__Invoice__c].Id.

    • For Fieldname for the Target Record, set the String value to the field that contains the ID of the source record.

      To set the opportunity as the target for the value aggregation, following the example, set ON_Opportunity__c.

    • Optionally, add a row, set the variable Prefix and set the string value to the prefix of your aggregation fields.

      This is required if you do not use the default prefix INV.

  11. Click Save.

  12. Click Activate.

This triggers JustOn to execute the process when your defined process criteria are true. It aggregates the configured invoice fields and writes the results to the target fields on the source object record.