Customizable Rollups Recipes

Recipe: Last Gift Fund

If you use General Accounting Funds, you may have a need to see the Last Gift Fund on an Account or Contact record. Using Customizable Rollups and Process Builder, you can make this work!

Note Special thanks to community member Dana Belcher for suggesting this recipe.

Note This recipe assumes you're using a single GAU Allocation on each Opportunity, but that may not be a reality for you. If you set up the recipe as follows and split your Opportunities to two or more GAU Allocations, NPSP will just pick one and display it on the Target record.

1. Create a custom field on the Account object.
   1. Data Type = Lookup Relationship
   2. Related to = General Accounting Unit
   3. Field Label = Last Gift Fund
   4. Description = Displays the General Accounting Unit that this Account's most recent gift was allocated to. Populated automatically via a Customizable Rollup.
   5. Help Text = Displays the General Accounting Unit that this Account's most recent gift was allocated to. Populated automatically via a Customizable Rollup.
2. Create a custom field on the Opportunity object.
   1. Data Type = Lookup Relationship
   2. Related to = General Accounting Unit
   3. Field Label = Related GAU
   4. Description = Displays the General Accounting Unit from the related Allocation. Populated via Process Builder, and used in a Customizable Rollup on Account to display the Account's most recent gift allocation.
   5. Help Text = Displays the General Accounting Unit from the related Allocation. Populated via Process Builder, and used in a Customizable Rollup on Account to display the Account's most recent gift allocation.
3. Use Process Builder, or another tool, to copy the General Accounting Unit from the Allocation to the Opportunity. Here is how we did it in Process Builder:
   1. In The process starts when picklist, choose to start the process when A record changes.
   2. Object = GAU Allocation
   3. Start the Process = only when a record is created
   4. Criteria Name = Does it have an opportunity?
   5. Criteria for Executing Actions = No criteria—just execute the actions!
   6. Add an Immediate Action.
      1. Action Type = Update records
      2. Action Name = Copy GAU
      3. Record Type = Select a record related to the npsp__Allocation__c, then select Opportunity
      4. Criteria for Executing Actions = No criteria—just execute the actions!
      5. Field = Related GAU
      6. Type = Field Reference
      7. Value = General Accounting Unit Record ID. In the field you'll see [npsp__Allocation__c].npsp__General_Accounting_Unit__c.Id
   7. Be sure to click Activate!
      

      Note Opportunities that existed before the process was created won't be updated. You can update old Opportunities manually or by performing a mass update via a tool like the Salesforce Data Loader/Data Import Wizard, or an alternate third party tool.
      
4. Depending on what Opportunities you want to roll up, you may want to create a filter group. You could also use the out-of-box Allocations: Won filter group.
5. Create a Customizable Rollup with these values:
   1. Target Object = Account
   2. Target Field = Last Gift Fund
   3. Description = Displays the General Accounting Unit from the most recent Allocation for this Account.
   4. Operation = Last
   5. Time Frame = All Time
   6. Rollup Type = Opportunity > Account (Hard Credit)
   7. Filter Group = Allocations: Won (or whichever filter group you want to use)
   8. Field to Roll Up = Opportunity: Related GAU
   9. Date Field = Opportunity: Close Date

Your rollup should look similar to this:

Now if you look at an Account's record, you can see the Last Gift Fund field displaying the General Accounting Unit to which the Account's last gift (Opportunity) was allocated.

Recipe: Last Soft Credit Opportunity

When viewing a Contact, you can already see the value of the last soft credit that the Contact received. It's an out-of-box rollup provided in NPSP (called Contact: Last Soft Credit Amount). But, perhaps you want to also see the Opportunity on which the Contact was given that soft credit. With Customizable Rollups, you can provide this info and have it link directly to the Opportunity.

Note Special thanks for Beth Breisnes for suggesting this recipe!

1. Create a custom field on the Contact object.
   1. Data Type = Lookup Relationship
   2. Related To = Opportunity
   3. Field Label = Last Soft Credit Opportunity
   4. Description = A lookup to the last Opportunity on which the Contact received a soft credit. Populated automatically by a Customizable Rollup.
   5. Help Text =  A lookup to the last Opportunity on which the Contact received a soft credit. Populated automatically by a Customizable Rollup.
2. Create the Customizable Rollup.
   1. Target Object = Contact
   2. Target Field = Last Soft Credit Opportunity
   3. Description = A link to the most recent Opportunity for which the Contact received a soft credit.
   4. Operation = Last
   5. Time Frame = All Time
   6. Rollup Type = Opportunity > Contact (Soft Credit)
   7. Filter Group = Opps: Won (SC)
   8. Field to Roll Up = Opportunity: Opportunity ID
   9. Date Field = Opportunity: Close Date
   

And if you look at a Contact's record, you can see the lookup to the most recent Opportunity from which the Contact received soft credit. Note that you'll have to manually calculate rollups to see results right away.

Recipe: Roll up Payment Details to the Recurring Donation

While you can see a summary of the Opportunity Amount info on a Recurring Donation, you may want a summary of the Payments as well. This is useful if a Contact pays more than the Opportunity amount on a given schedule OR if a Contact's Payment falls through and they haven't paid as much as they committed to.

Note Special thanks to community member Sarah Qunito for suggesting this recipe!

1. Create a custom field on the Recurring Donations object:
   1. Data Type = Currency
   2. Field Label = Payments Summary (Just a suggestion. Feel free to name it anything you like!)
   3. Description = A summary of all Payments for a Recurring Donation. Populated automatically by a Customizable Rollup.
   4. Help Text = A summary of all Payments for a Recurring Donation. Populated automatically by a Customizable Rollup.
2. Create a Customizable Rollup with these values.
   1. Target Object = Recurring Donations
   2. Target Field = Payments Summary
   3. Operation = Sum
   4. Time Frame = All Time
   5. Rollup Type = Opportunity > Recurring Donations
   6. Filter Group = Opps Won (Recurring Donations) or use your own!
   7. Amount Field = Opportunity: Payment Amount Received
   

Recipe: Combine Rollups and Levels

You can easily combine the power of Customizable Rollups with Levels to automatically calculate Levels based on rollup fields.

Let's say you have three Levels (Bronze 500-1499, Silver 1500-2999, Gold 3000+) and you want to populate them based on donations made in the last 365 days. In our example, we'll use the out-of-box Total Gifts Last N Days Customizable Rollup, which is set to roll up the last 365 days worth of data by default. But, you could easily modify the Days Back field in the rollup based on your needs OR clone this rollup and enter a different number of Days Back.

Note Special thanks to community member Jenny Smithson for suggesting this recipe!

1. Create your Levels. If you need more info, see Create and Manage Levels.
   1. Level Name = Bronze
   2. Minimum Amount = 500.00
   3. Target = Contact (or Account)
   4. Maximum Amount = 1499.00
   5. Source Field = Total Gifts Last N Days (this is where you link this Level to the Customizable Rollup!)
   6. Level Field = Level
   7. Previous Level Field = Previous Level
2. Create your other Level fields using the same values above but giving each one a unique Level Name and amount.

Now, when the Total Gifts Last N Days field for a Contact includes a value that corresponds with one of your Levels, the Level field for that Contact will reflect the appropriate value.

Recipe: Total Current Year Payments on Prior Year Pledges

Perhaps you want to see the Payments made this year for pledges from the prior year. Or you want to see last year's Payments on pledges from the year before that. And it's important that these fields are dynamic, so that they don't reflect a specific, hard-coded year.

Note Special thanks to community member Janine Rauscher for suggesting this recipe!

1. Create a custom field on the Account object:
   1. Data Type = Currency
   2. Field Label = This Year Payments on Past Year Pledges (Just a suggestion. Feel free to name it anything you like!)
   3. Description = Displays the total amount of Payments made in this calendar year for pledges (Opportunities) made in past calendar years. Populated automatically via a Customizable Rollup.
   4. Help Text = Displays the total amount of Payments made in this calendar year for pledges (Opportunities) made in past calendar years. Populated automatically via a Customizable Rollup.
2. Create a custom field on the Payment object:
   1. Data Type = Formula
   2. Field Label = Is Opportunity From Prior Year
   3. Formula Return Type = Checkbox
   4. Formula = YEAR( npe01__Opportunity__r.CloseDate ) < YEAR( npe01__Payment_Date__c )
   5. Description = Indicates that the Opportunity associated with this Payment is from a prior calendar year. This field is populated automatically.
   6. Help Text = Indicates that the Opportunity associated with this Payment is from a prior calendar year. This field is populated automatically.
3. Create a Customizable Rollup filter group.
   1. Name = Old Payments
   2. Description = Includes payments where the Opportunity Close Date year is older than the year of the Payment.
   3. Add a filter rule for Paid Payments:
      1. Object = Payment
      2. Field = Paid
      3. Operator = Equals
      4. Value = True
   4. Add a filter rule that indicates that the Opportunity's Close Date year is older than the year the Payment was Paid.
      1. Object = Payment
      2. Field = Is Opportunity From Prior Year (this is the field you created on the Payment object earlier)
      3. Operator = Equals
      4. Value = True
   
4. Create the Customizable Rollup.
   1. Target Object = Account
   2. Target Field = This Year Payments on Past Year Pledges (or whatever you called the field you created in step 1)
   3. Operation = Sum
   4. Time Frame = Years Ago
   5. Years Ago = This Year
   6. Rollup Type = Payment > Account (Hard Credit)
   7. Filter Group = Old Payments (this is the new filter group you created)
   8. Date Field = Payment: Payment Date
   9. Amount Field = Payment: Payment Amount
   

Now, you can look at the Contact and see the list of Payments for the previous year's pledges (Opportunities).

And you can verify that number by viewing the Payments on the Opportunity.

You can easily implement this for last year's payments made on pledges from any prior year. Simply create another custom field on the Account, but give it a unique name. When creating the Customizable Rollup, set the Years Ago field = Last Year.

Recipe: Track Amount Donated for Matched Gifts Only

Currently the Matching Gift related list shows the full amount of all donations, but not the actual matched amount (which can be different if the company only matched a portion of the gift). You can easily create a Customizable Rollup that shows the matched amount.

Note Special thanks to the community for suggesting this recipe.

1. Create a custom field on the Contact.
   1. Data Type = Currency
   2. Field Label = Total Matched Amount (or Matched Gift Amount)
   3. Description = Displays the total amount of matching gifts that have been received as a result of this Contact's donations. Populated automatically by a Customizable Rollup.
   4. Help Text = Displays the total amount of matching gifts that have been received as a result of this Contact's donations. Populated automatically by a Customizable Rollup.
2. Create a filter group that includes two filter rules. The first one should limit the Opportunities to those that are Closed/Won.
   1. Object = Opportunity
   2. Field = Won
   3. Operator = Equals
   4. Value = True
3. Add a second filter rule that limits the Opportunities to only those with the Matching Gift record type.
   1. Object = Opportunity
   2. Field = Record Type ID
   3. Operator = In List
   4. Value = Matching Gift
4. Create a Customizable Rollup that sums up the Amount of matched gifts for this Contact.
   1. Target Object = Contact
   2. Target Field = Total Matched Amount
   3. Operation = Sum
   4. Time Frame = All Time
   5. Rollup Type = Opportunity > Contact (Soft Credit)
   6. Filter Group = Matching Gifts (should be the filter group you created earlier)
   7. Amount Field = Contact Soft Credit: Amount
   

Now, look at the Contact to see the total matched amount. Note that you'll have to manually calculate rollups to see results right away.

And you can verify that the amount is correct by viewing the Contact's list of Opportunities and adding up those with the Matching Gift record type.

Recipe: First or Last Gift Campaign

Create a first or last gift campaign rollup on a contact record that's missing contact information.

Suppose you have Contacts in your system that don't have email or mailing addresses. Why are they in your system if you can't contact them? This sounds like a use case that requires some investigation.

You could run a report of Contacts where Email = blank and Mailing Address fields = blank, then start examining their related Opportunities looking for a pattern. This is a practical but time-consuming solution. Or, you could save yourself some time and create a Last Gift Campaign rollup on the Contact. When you add that field to your report, you can instantly see if there is a pattern based on Campaigns.

For example, you might find that the Contacts were mostly credit card donors at the door of a tasting event where you didn't collect any personal info. But, the swiping process required the auto-creation of a Contact and an Opportunity with the Campaign field auto-set. These folks were just passing through town, bought a tasting ticket, and that's the first and last you'll hear from them. Since the Last Gift Campaign field is filled in with the same Campaign for most records on the report, you can easily group them together. Mystery solved!

Note Special thanks to community member John Fine for suggesting this recipe and writing this extremely helpful use case!

1. Create a custom field for First Gift Campaign on Contact (or Account). Note that you could create a similar field for Last Gift Campaign.
   1. Data Type = Lookup Relationship
   2. Related to = Campaign
   3. Field Label = Last Gift Campaign
   4. Help Text = Displays the Campaign that brought in the Contact's most recent donation. Populated automatically via a Customizable Rollup.
   5. Description = Displays the Campaign that brought in the Contact's most recent donation. Populated automatically via a Customizable Rollup.
2. Create a Customizable Rollup with these values:
   1. Target Object = Contact (or Account if you want to roll up to Accounts instead)
   2. Target Field = Last Gift Campaign
   3. Description = A lookup on the Contact record to Campaign that brought in the Contact's most recent donation, as determined by the Opportunity Close Date field.
   4. Operation = Last
   5. Time Frame = All Time
   6. Rollup Type = Opportunity > Contact (Hard Credit)
   7. Filter Group = Opps: Won (HC)
   8. Field to Roll Up = Opportunity: Campaign ID
   9. DateField = Opportunity: Close Date
   

After rollups are recalculated, you can see the updated Last Gift Campaign field on a Contact record.

You could also add it to the report you're using to track donors without contact information.

You can easily create another rollup for First Gift Campaign by creating a custom field called First Gift Campaign, cloning the Last Gift Campaign rollup, and changing a few key fields:

• Target Field = First Gift Campaign
• Description = A lookup on the Contact record to Campaign that brought in the Contact's first donation, as determined by the Opportunity Close Date field.
• Operation = First

Recipe: Total Gifts Three Years Ago

NPSP provides out-of-box rollups for total giving this year, last year, and two years ago. But perhaps you want to look back farther, to get a bigger picture of your donors' engagement and donation patterns. You can easily clone existing rollups and update the Years Ago field to see more of a donor's giving history.

Note Special thanks to Cori O'Brien for suggesting this recipe!

1. Create a custom field on the Contact object.
   1. Data Type = Currency
   2. Field Label = Total Gifts Three Years Ago
   3. Help Text = Displays the total amount of gifts three calendar years ago for which the Contact was listed as the Opportunity Primary Contact. Populated automatically via a Customizable Rollup.
   4. Description = Displays the total amount of gifts three calendar years ago for which the Contact was listed as the Opportunity Primary Contact. Populated automatically via a Customizable Rollup.

   5. Default = 0

      

      Note We recommend setting the Default Value so that this field isn't blank if a Contact didn't donate three years ago.
2. Either clone the Total Gifts Two Years Ago Customizable Rollup or create a new one with these values. If you're cloning, be sure to update the Target Field and Years Ago fields!
   1. Target Object = Contact
   2. Target Field = Total Gifts Three Years Ago
   3. Description = The total of all gifts three calendar years ago where the Contact is the Opportunity Primary Contact
   4. Operation = Sum
   5. Time Frame = Years Ago
   6. Years Ago = 3 Years Ago
   7. Rollup Type = Opportunity > Contact (Hard Credit)
   8. Filter Group = Opps: Won (HC)
   9. Date Field = Opportunity: Close Date
   10. Amount Field = Opportunity: Amount
   

As you can see, you can easily create Customizable Rollups for 4, 5, 6, 7, 8, 9, 10 years ago (or whatever year you need to see), simply by creating a new custom field and then cloning an existing rollup and changing a couple of fields.

So, now that you have giving information by year, suppose you want to see the total amount a Contact has given over the past four years (this year + last year + 2 years ago + 3 years ago). You can easily sum up this information using a formula field.

1. Create a custom field on the Contact object.
   1. Data Type = Formula
   2. Field Label = Total Gifts Last Four Years
   3. Formula Return Type = Currency
   4. Formula = npo02__OppAmountThisYear__c + npo02__OppAmountLastYear__c + npo02__OppAmount2YearsAgo__c + Total_Gifts_Three_Years_Ago__c
   5. Description = Displays the total amount of gifts in the past four years for which the Contact was listed as the Opportunity Primary Contact. Populated automatically using a formula.
   6. Help Text = Displays the total amount of gifts in the past four years for which the Contact was listed as the Opportunity Primary Contact. This field is populated automatically.
2. Be sure to add it to the Contact page layout, then check it out on the Contact record.
   

Recipe: Identifying Peer-to-Peer Fundraising Influencers

If you plan events that include peer-to-peer fundraising, you want to be able to easily identify constituents who influence others to give. That way you can recognize their contribution by sending a thank you email or posting a note on your event site.

Note Special thanks to community member Ginny Foran for suggesting this recipe!

This recipe follows along with Aria Bennett, who is running in the 2018 Race for Literacy. She has been publicizing her participation in the race to raise money, and thus far has received a number of donations from friends and coworkers. In Salesforce, the Event Coordinators use Campaigns to track the event as a whole, as well as each individual fundraiser or team captain. The following setup is required:

1. Add a new value, Fundraiser, to the Campaign Type field (on the Campaigns object).
2. Add two new Opportunity Contact Roles: Fundraiser, Team Captain.
3. Add a custom field on the Contact object.
   1. Data Type = Currency
   2. Field label = Total Influenced Gifts
   3. Description = Displays the total amount of gifts that this Contact has influenced. Includes all Opportunities where the Contact received a soft credit with the role of Fundraiser or Team Captain. Populated automatically via a Customizable Rollup.
   4. Help Text = Displays the total amount of gifts that this Contact has influenced. Includes all Opportunities where the Contact received a soft credit with the role of Fundraiser or Team Captain. Populated automatically via a Customizable Rollup.
4. Create a Campaign for the 2018 Race for Literacy.
5. Create a Campaign for Aria Bennett 2018 Race for Literacy. Make the 2018 Race for Literacy its Parent Campaign.
6. Create the Opportunities for the people supporting Aria with donations. On each donation, make sure:
   1. Primary Campaign Source = Aria Bennett 2018 Race for Literacy
   2. Add Aria Bennett as a Contact Role with the Role = Fundraiser
7. Create a Customizable Rollup filter group that only shows Opportunities with the appropriate Contact Roles (Fundraiser, Team Captain).
   1. Name = Peer to Peer Opps (or whatever you want to call it)
8. Add a filter rule that includes the Fundraiser and Team Captain Contact Roles. Note: If you only want to see Won Opportunities, add a second filter rule.
   1. Object = Contact Soft Credit
   2. Field = Role Name
   3. Operator = In List
   4. Values = Fundraiser, Team Captain
9. Create a Customizable Rollup with these values.
   1. Target Object = Contact
   2. Target Field = Influencer
   3. Operation = Sum
   4. Time Frame = All Time
   5. Rollup Type = Opportunity → Contact (Soft Credit)
   6. Filter Group = Peer to Peer Opps (the filter group you created in the previous step)
   7. Amount field = Soft Credit: Amount
   

If you look at Aria Bennett's Contact record, you'll see the Influencer field. Reminder: To see the updated field on the record right away, you'll need to manually recalculate rollups.

And you can verify the amount that Aria has influenced by looking at and adding up the Opportunities on her fundraising Campaign.

Aria's off to a great start and will definitely make a big impact on the Run for Literacy!