Liquid Field Calculations can be used in most cases when you want to calculate a Field’s value. However, Numeric Field Calculations provide a simpler way to configure some calculation types and offer a filtering mechanism that Liquid Calculations don’t.
Specifically, Numeric Calculations are used to provide a numeric value that is calculated based on the values of an App’s related transactions. They are useful when you need to:
- Calculate the sum, average, minimum, or maximum of values from a Currency, Integer or Decimal Field across a related app’s transactions. For instance, you could calculate the sum of values from an invoice_amount Field across related app transactions.
- Count the number of related transactions that match some parameter(s). For instance, you could populate the number of transactions in a Phase named Abandoned.
Numeric Field Calculations are distinct from Liquid Field Calculations in that they can accept JSON filter parameters, allowing you to restrict which transactions your calculation pulls data from.
Before We Start...
This tutorial will assume you understand the following concepts:
Let's Get Started!
In this tutorial, we'll create a Numeric Field Calculation in a parent App (named Matter) to print the sum of invoice_amount values for related transactions of its child App (named Invoices). We'll also apply a filter to our calculation so that only invoices from a certain vendor are included in that sum.
Note: Numeric Aggregations (e.g., sum, count) that are filtered only work across HasMany / BelongsTo relationships. It does not work across a ManytoMany relationship.
1. Create a New Field for Your Calculation
In the Wizard, Add a new Field for your Numeric Field Calculation.
In our example, we'll be aggregating the sum of an invoice_total Field so we'll Name it sum_of_invoices and make it a Currency Field.
Since this Field's value will be calculated on the View Page, we recommend marking it as Read Only and setting its Display In property to View.
2. Configure the Numeric Calculation
Browse to this new Field's Advanced properties tab.
Select the Calculated checkbox.
From the Calculation Type property, select Numeric.
Click the Mapping Editor box.
3. Provide a Mapping Property
For this calculation to access Field data from a related app, we need to point the Field Calculation to that app in the Mapping property.
Choose Invoices (invoices) from the Configure Mapping drop down.
For example, you want to get the total amount of all invoices related to a legal matter. The HasMany Field is named invoices. Each invoice Record stores its amount in a Currency field called invoice_total.
We want to calculate the sum of all the invoices so choose Invoice Total from the Calculation Field dropdown and set the Numeric Aggregation type to Sum.
In the Mapping field, you can also do filtering. The filter comes directly after the mapping. For example, if you want to add up only invoices from Law Firm A, get them like this:
You can also filter to exclude. This would get you all vendors except Law Firm B.
Update your app.
Test It Out
Congrats! You've created your first Numeric Field Calculation.
To test it out, jump over to the App containing your new Numeric Field Calculation. You may need to relate a new Record to trigger the Calculation.
In the following example, the three related invoice Records each have an invoice_amount of $100, but only two of them have a vendor of "Johnson LLC" so our Sum of Invoices Field has calculated a total of $200.
Aggregating MultiCurrency
You can apply numeric aggregations to MultiCurrency Fields. The Field receiving the total must also be of type MultiCurrency. The related transactions being aggregated will automatically be filtered down to ones where the MultiCurrency Fields have the same currency code as the calculated Field.