This article covers the following topics:
An additional field type has been added to the available options when creating a new custom field in the "Admin Module › Fields and Forms". The new “Rollup” field allows Pipeliner Admins to create calculate/aggregate data about related records.
For example, on an Account record, you may want to sum the value of all related Opportunities won this year ⤵
Or for Opportunities, you might want to add a field named “Number of Meetings” so you can rollup the total number of meetings for each Opportunity and subsequently use that in a report to see if the number of meetings held correlates to winning or losing deals ⤵
A “Rollup” field is a read-only calculated field which “rolls up” data on related records. For example, from an Account, a Rollup field can roll up related Contacts, Leads & Opportunities, Activities (Tasks and Appointments) as well the number of entities added to a custom Lookup fields that you might have created
Advanced Properties for Rollup fields include creating Filter criteria to specify exactly which records should be aggregated, selecting a function for the calculation, specifying if the aggregation should be based on the number of records or only on a single field value held on those records and applying User Role-based Field Permissions (note that Rollup fields are intrinsically read-only to users so Field Permissions will, in practice, be used only to determine whether the field is visible or not to users based on their User Role).
Functions that can be used
The following functions can be used when creating a "Rollup" field ⤵
COUNT › Calculate the number of records matching the filter criteria specified (e.g. a number of activities with a type of “Customer Visit” linked to an Account).
SUM › Calculate the sum of any numeric field (Number/Float/Currency fields) (e.g. total won Opportunity amount this year for this Account).
MIN › Find the minimum value for Date & Numeric fields. (e.g. show the date of the first call made to a customer).
MAX › Find the maximum value for Date & Numeric fields. (e.g. show the date of the last call made to a customer).
AVG › Find the average of numeric fields (Number/Float/Currency fields — e.g. what is the average value of opportunities linked to this account).
FIRST › Display the first instance of a specified record based on the selected sort order field and filter criteria.
LAST › Display the last instance of a specified record based on the selected sort order field and filter criteria (e.g. you might have multiple NPS scores associated with Tasks linked to an Account and you want to see the last score added).
NOTE: The Last & First can also return records. So it is not only the field value but a clickable record that will be visible on the form.
Adding This Year’s Won Revenue for an Account
Enter the Admin Module — from the Pipeliner CRM Application, click on the App Switcher in the top left corner and choose “Administration” ⤵
NOTE: Only Users with Admin Rights will see the Administration menu
Select "Fields & Forms" from the menu and then click on the Accounts tab. Click on the Fields tab and then the “Create New” button to add your new field.
This example is based on creating a Rollup field on Accounts based on the SUM of Opportunity Values filtered by Closing Date is “This Year” (using the Dynamic period option) and "Status = Won".
In Type, select “Rollup” and then name your field - our example is “This Year’s Won Revenue”. In Records to Summarize, we’re going to select “Leads & Opportunities” ⤵
Now we need to add the Filter criteria. Click on “Filter” and then switch on filtering ⤵
Now start to add your filter criteria ⤵
We want our field to roll up the values of this year’s won Opportunities so our filter would look like this ⤵
Click on “Save” when you’ve added the exact filter criteria that you need.
Next, choose your Function. We’re going to select “SUM” as we want to total up the "Opportunity Values" ⤵
As we’ve selected SUM as our function, we now need to specify the field which should be totalled. In Record Field, we’re selecting “Opportunity Value” ⤵
You can hide this field from users based on their User Role using “Edit Permissions”. This article has more information on Field Permissions.
Click on “Save” to save your field.
Now you need to place your field on the Account Form so click on the “Forms” tab and then on “Edit Form” ⤵
Drag your new field from the right-hand panel and place it on the Form and then click on “Save” and remember to "Publish" your changes ⤵
Once published, your new field will now display when you open up an Account record giving instant visibility for your users of how much has been closed for that Account this year! Add a similar field for “Last Year’s Won Revenue” and your Account Managers can immediately see how this year is tracking vs last ⤵
NOTE: Users can click on the symbol on the right of the field to drill down into the data ⤵
As an aside, they can then jump straight to the Products tab using the Trend View to see how those revenue values are reflected in the sales of actual Products or Product Categories.
You could also choose to add this field to the Preview panel or the Tooltip (QuickView) for Accounts.
Back in Fields & Forms in the Admin Module, click on Accounts and then the Interface tab. Click on “Account Preview” and then “Edit Fields” ⤵
Drag your new field into one of the 12 fields users can see in the Preview (or one of the top 6 fields) if you want it to be included in the Tooltip (QuickView) ⤵
Click on “Save” and you’ll then need to Publish again. Your field will now be visible in the Preview panel in the Compact View (or when selecting a record in the "List View"). This article has more information on Customizing the Record Preview ⤵
Adding Number of Customer Visits for an Account
Some companies have a target number for annual “Visits” to their key customers. Customers using this sort of Account Planning also often add a custom field to specify what the target number should be. You can easily see both on the Account once added to the Form.
This example is based on creating a Rollup field on Accounts based on the COUNT of the number of Activities related to that Account, filtering by Activity Type = “Customer Visit”, Activity Status = “Completed” and Date is “This Year” (using the Dynamic period option).
The process for creating the field and adding it to the Form is the same so we’ll just cover the options and filters here ⤵
NOTE: In the filter, we’re using a custom Activity Type of “Customer Visit”. Custom Activity Types are available to Business and Enterprise Tier customers ⤵
The function this time will be COUNT as we want to count the number of Customer Visits.
NOTE: We’ve also added a custom field for users to fill in with the “Annual Visits Target” so they can easily see how many visits should be completed vs how many have been completed to date and have saved and published ⤵
On an Account, each time a user adds a new “Customer Visit” activity, the “Customer Visits Attended This Year” field will update ⤵
Here’s the Activities tab for the Account showing the 3 completed "Customer Visits" ⤵
And the updated total ⤵
NOTE: You can see from the Linked Items and Linked Items Types fields that we’ve added to the "List View" that the Customer Visits are linked directly to the Account record. This is very important to make sure they are totalled by the "Account Rollup" field ⤵
If the Customer Visit was linked to a Contact — instead of an Account — and was visible in this list because of the “Show related activities” option shown below, which is enabled by default, then it would not count! ⤵
Last Customer Visit for an Account
Building on the previous example, over time the Activities tab for any record can — and should! — become very busy with lots of different activities. For specific and important Activity Types — like our Customer Visit — you can add a Rollup field that can display a link to the LAST (or FIRST) instance of the activity.
So we’re going to add a Rollup for the Last Customer Visit which will give us a preview and quickly click through to the LAST Customer Visit added. We’ve selected LAST as our function and, importantly, in Sorted By Field, chosen End Date (when the Customer Visit Appointment finished) as our sort ⤵
And here’s the end result on our Account ⤵
NOTE: The Sorted By field is really important to sort your list of selected records so the LAST one can be displayed. Here’s the Activities tab with End Date added to the "List View" so you can see that the last one “Q3 Customer Visit” — based on End Date — is the one displayed in the field on the Account Detail ⤵
Hover over the field on the Details tab to see the Tooltip (QuickView) and/or click on the link to open up the activity itself ⤵
NPS Survey Scores for a Contact
This example uses our Google Forms integration to add a custom task “NPS Survey” to a Contact whenever they fill in a survey form. Our Rollup fields will then display the first score given and the latest score.
Here’s a very simple form showing the Google Forms Add-on set up to create a new task “NPS Survey” whenever the form is submitted and to map the score out of "100" given by the Contact to the NPS Score field on the Activity ⤵
The Activities would look like this. We’ve added the NPS Score field to the Activity List View ⤵
We need two Rollup fields, this is the one using "FIRST", sorted by "Due Date" ⤵
And here’s the second which shows us the latest — or "LAST" — score ⤵
And here’s how they display, allowing your users to easily see how satisfied the Contact is with our service over time ⤵
Additional ways to use Rollup Fields
Rollup fields can be used in List Views, Reports, Filters and Automatizer processes.
Here’s an example of an Automatizer process that auto-updates Account Class based on the This Year’s Won Revenue field we created earlier making sure that all our Accounts are classified correctly and, following on from that, are handled in the right way by our Account Management and Customer Success teams. Just to add an extra dimension to this already slick process, it also automatically updates the Annual Visits Target based on Account Class so your users don’t need to fill it in! ⤵