General
This feature allows you to calculate desired values, such as a sum or count, and write the calculated result back to any field in the target module. For example, you can:
- Calculate the total amount of a customer’s invoices and store the value in a designated field.
- Calculate the number of family members linked to a contact and store the value on the contact entity.
- Create snapshots for reporting purposes, such as daily membership counts.
Getting Started
Create a report from the module whose field you want to update.
Example: If you want to write the calculation result to a field in the Account module → the source is Account.
- Go to Reports → Create New Report
- Select Account as the source module
- Add necessary joins (e.g., Account → Invoices)
- Select the fields to be calculated (on the Columns tab)
Add the fields you want to perform calculations on.
Example:
- Field: Invoice Amount / Total (0% VAT)
- Calculation: Sum
- Enter Header = the technical name of the target field in the target module
e.g.,cf_invoice_total
Important:
The header field must contain the technical name of the module field you want to update.
You can add multiple calculation fields, for example:
- Sum:
cf_invoice_total - Maximum Date:
cf_last_invoice_date - Count:
cf_invoice_count
Add Grouping if Needed
If the calculation involves a join (e.g., Account → Invoices), add grouping by CRMID so that the calculation is performed per account. Before creating a scheduled task, check the Preview tab in the report to ensure the field values match the intended update.
Create a Scheduled Task
- Go to Settings → Scheduled Tasks
- Add a new scheduled task
- Select the action Update Report to Entities
- Choose the report you just created
- Set the schedule (e.g., daily at 6:00 AM)
The system calculates the values for each group and writes the result to the target fields defined in the report.
Example – Total of Open Invoices for a Member
Collect the total amount of unpaid invoices for a contact and store it in the designated field cf_invoice_total.
- Source: Contacts
- Relation: Invoices
- Projection: Contact → Invoice field Total (incl. VAT)
- Title:
cf_invoice_total - Operator: Sum
- Condition: Status = Sent
- Grouping: CRMID
Example – Number of Open Invoices for a Member
Collect the number of open invoices for a contact and store it in the field cf_invoice_count.
- Source: Contacts
- Relation: Invoices
- Projection: Contact → Invoice field CRMID
- Title:
cf_invoice_count - Operator: Count
- Condition: Status = Sent
- Grouping: CRMID