Use this link to share with your colleagues:
All About Roll-Up Fields (and Best Practices): https://help.pm.leapevent.tech/a/976019
You're probably already familiar with some Roll-Up Summary fields; think of the "Lifetime Donation Amount" field on the Account.
PatronManager comes with several fields like this built in, but sometimes you might need to create additional ones for particular purposes. For example, you might want to run an Account report for a mailing and have it include the total amount donated in the previous calendar year.
However, there's more than one way to solve for problems like that! Below, we'll talk about:
- What a roll-up field is
- Best practices: consider these roll-up field alternatives
- Best practices: should I build a new, custom roll-up field
- Adjusting how packaged roll-up fields work (e.g. "Amount Donated Last # Days)
- Creating/editing custom roll-up fields
Examples of built-in PatronManager roll-up fields
What is a Roll-Up Summary field?
A Roll-Up Summary is a special kind of Salesforce field that displays a value on a master record based on autocalculated values from detail records.
For example: in the Lifetime Donation Amount field on an Account, the Amounts of each detail record (Donations) are being summed, and the result is displayed on the master record (Account).
Roll-Up Summary fields are only available for objects that have a particular kind of connection. The most common place to use Roll-Up fields in PatronManager is on the Account, for Donations, so that's what we'll be using in our examples here.
Best practices: consider these Roll-Up field alternatives
Roll-Up fields are great, but they aren't always the best solution. For one thing, you have a limited number available. Here are two other methods that can help you accomplish similar goals:
CRM Snapshots
CRM Snapshots make it easy to query the data in your system with just a few clicks, and several of them include bonus roll-up data.
Note: if you haven't used CRM Snapshots before, we recommend reading this article first.
There are several options available in the "Donations" sub-tab within CRM Snapshots. In addition to letting you filter the Snapshot to show only patrons who meet your Donation criteria, many of them also give you the total amount of the Donations that met those criteria, on the same line in your report -- much like a Roll-Up field!
Check out the Snapshot called "Find accounts that donated a certain amount between certain dates". This Snapshot is great to use for a one-off Roll-Up summarized by Account; for example, if you need a donor listing for your program.
Fill it out according to the instructions you'll find on each step. When you view the completed Snapshot, you'll see a list of Accounts, and a special column called "Donation Total", which is the total amount of all Won (Posted) Donations for each Account, based on your Snapshot criteria.
Snapshots like this one allow you to filter by Close Date range, Donation Record Type, and Fund. You can also specify a minimum/maximum total Donation amount. They're quick and easy to create, and they don't count toward your Roll-Up field limit. For single-use situations like a program list or an appeal letter, they're a great choice!
There are also several options available in the "Tickets" sub-tab within CRM Snapshots. Two in particular will give you a column that functions like a Roll-Up field, specifically to show the number of unique Events purchased that meet the criteria you specify.
Check out the ones called "Find patrons who bought tickets to ‘more than’ or ‘at least’ or ‘exactly’ X events in a timeframe" and "Find patrons who bought tickets to ‘more than’ or ‘at least’ or ‘exactly’ X events in a given category".
There are also a few CRM Snapshots in the "Full 360" sub-tab that can function like Roll-Up fields. In particular, check out the one called "Find patrons who have ordered tickets OR made donations", which will give you a column for the total amount each patron spent on each!
Summary reports
Reports that include detail from a child object (like Donations or Ticket Order Items) can be grouped by a parent record (like Account). The report can then summarize any number or currency column, and show the total on the grouping level. You can then hide the details to see (for example) a list of Accounts with the total amount of their Donations that meet your report criteria -- much like a Roll-Up field!
Donation reports can be filtered, grouped, and summarized to help you see Account Donation totals, while also giving you access to Donation details. If you're looking for more Donation detail than a Roll-Up field can provide, this might be the best solution.
There's a built-in report called "All Donations by Account" that will give you a great starting point! Be sure to "Save As" before customizing it further. If you start from a different Donation report, this article will show you how to group it by Account.
You can filter the report to show only the particular kinds of Donations you're looking for, and add columns if you'd like.
Then run the report, click on the gear and check or uncheck the Details box to toggle back and forth between seeing a list of Accounts and the total amount of their Donations that meet your filter criteria, or showing the detail of those Donations:
You can build these kinds of reports for things other than Donations, too! To read more about summary reports, check out this Help tab article.
Static Fields
One of the main benefits of Roll-Up Summary fields is that they calculate continuously in real time. If the data isn't changing anymore (for example, if you want to see the total amount donated in a prior fiscal year, after all the relevant Donations have already been entered into PatronManager), you could use a static field instead.
If you're comfortable performing data updates, you can run reports in PatronManager and update a field like this yourself. If you'd like our assistance, head over to our Data Project Services and fill out the form to kick off a project.
Best practices: should I create a new, custom roll-up summary field?
Before you dive in, ask yourself these questions:
PatronManager comes pre-built with a number of useful fields that roll up Donations to the Account, including:
- Lifetime Donation History (Amount)
- Lifetime Donation History (Number)
- Amount Donated Last # of Days
- Amount Donated This Fiscal Year
- First Donation Date
- First Donation Amount
- Last Donation Date
- Largest Donation Date
In addition to these fields that are always included with PatronManager, there's a good chance your organization already has some custom-built Roll-Up Summary fields. Before you create a new field, glance through the list of Account fields in setup (you'll find instructions below) to see if someone else has already built it!
If you're looking for, say, the total amount donated by each Account between October 22, 2016 and October 21, 2017 so that you can use it for program acknowledgments for your next show, that's probably single-use, because you'll use a different date range for each new show. If you need a single-use field, try a CRM Snapshot instead!
On the other hand, if you're looking for the amount donated in FY19, you might refer to that repeatedly. If that's the case, a Roll-Up Summary field might be the best solution.
If your end goal is something like a program list or a mailing, you probably only need to see the results in a report, and a CRM Snapshot might work better.
If you need to see it on the page layout, though, so that you can look up Sue and David Johnson and see at a glance that they donated $1,200 in FY19, a Roll-Up Summary field is likely the way to go!
If you're doing more detailed donor research, you might want to see not only the total that each Account has given to your Capital Campaign, but also the dates and amounts of those Donations. In that case, a Donation report, summarized by Account and filtered for only Capital Campaign Donations, would be the better solution.
If you don't care about the specific details of each Donation and only need to see the total, a CRM Snapshot or a Roll-Up Summary field is better.
Adjusting packaged roll-up fields
If you've noticed your packaged roll-up fields (e.g. "Amount Donated in the Last # Days") don't seem to be correct, it's probably one of two issues:
- The roll-up field is including/excluding donation record types you don't want it to include/exclude (e.g. "In-Kind" records or "Soft Credit" donations)
- The roll-up field doesn't have the right date range (i.e. the "# of Days" is incorrect)
Either way, you can often adjust these things in Organization Settings!
Creating/editing custom roll-up fields
Ready to create a brand new Roll-Up Summary field? Head to our How to Create and Edit Custom Roll-Up Fields article for full instructions.