Opportunities, Payments and Allocations Recipes

Opportunity: Sum of Paid Payments This Financial Year

Description

Rollup all child payment records for an Opportunity if the Payment Status is “Paid” and the Payment Date falls in the current fiscal year.

Objects, Fields, Relationships

This rollup example uses the Nonprofit Success Pack (NPSP), which has a child object of Opportunity called Payment (npe01__OppPayment__c). There can be multiple payments before an opportunity moves into a fully paid status. The Opportunity field on Payment (npe01__Opportunity__c) is the lookup to Opportunity. Payments each have their own Payment Date field (npe01__Payment_Date__c) and a checkbox to indicate that they are paid (npe01__Paid__c). While this uses NPSP objects and fields as examples, the idea of multiple payments on an opportunity is applicable in many organizations.

Field Value
Parent Object Opportunity
Child Object npe01__OppPayment__c
Relationship Field npe01__Opportunity__c
Relationship Criteria (SOQL Query) npe01__Paid__c = True AND npe01__Payment_Date__c = THIS_YEAR
Relationship Criteria Fields npe01__Paid__c, npe01__Payment_Date__c
Field to Aggregate npe01__Payment_Amount__c
Field to Order By n/a
Aggregate Operation SUM
Aggregate Result Field DLRS_Payments_This_Year__c
Calculation Mode Realtime
Schedule vs Child Trigger Deploy the child trigger for a realtime update whenever a payment is marked paid AND click Schedule Full Calculate to have all records recalculated on the first of each month, so that the relative date filter for the year is kept up to date.

Variations

  • Use for any child payment or transaction records linked to Opportunity, or for child Opportunities related to a parent Opportunity.

  • Create a version for LAST YEAR.

  • Unpaid payment records, so you can see outstanding balance.

Contributed By Jared Henning, Salesforce.com

Opportunity: Sum of Paid Payments This Relative Fiscal Year

Description

This is a variation of the original “Opportunity: Sum of Paid Payments This Financial Year” for orgs that need to track gifts that are paid or scheduled outside of the dates of their fiscal year. This would be for orgs who have this happen enough that particular infrastructure is created for it.

Scenario - Your org gets a 5 year commitment for pledges for $500,000, paid out over the course of 5 fiscal years, and your fiscal year is October - September. However, the donor sends payment 1 for FY23 in October 2022 and the FY24 payment in January 2023. Using payment date, these would both be in FY23 so a workaround is needed!

Your org has decided to have a fiscal year text field on the payment object to get around this that defaults to the current FY but can be manually edited. However, that means that standard rollup types don’t work well or would have to be edited every year. To get to the correct relative fiscal year, you create a formula that returns a numeric value relative to the current fiscal year.

Objects, Fields, Relationships

Field Value
Parent Object Opportunity
Child Object npe01__OppPayment__c
Relationship Field npe01__Opportunity__c
Relationship Criteria (SOQL Query) npe01__Paid__c = True AND Fiscal_Year_Relative_Number__c = 0
Relationship Criteria Fields npe01__Paid__c, Fiscal_Year_Relative_Number__c
Field to Aggregate npe01__Payment_Amount__c
Field to Order By n/a
Aggregate Operation SUM
Aggregate Result Field DLRS_Total_Payments_This_Year__c
Calculation Mode Realtime
Schedule vs Child Trigger Deploy the child trigger for a realtime update whenever a payment is marked paid AND click Schedule Full Calculate to have all records recalculated on the first of each month, so that the relative date filter for the year is kept up to date.

Any test code or other preparations needed:

Creating two fields

  1. Text field for Fiscal Year. In this case the format is FYXX. This is a text field that uses flow automation to default to the FY that it was paid but can be manually overridden
  2. Formula for relative fiscal year. In the example above with the FY being Oct - Sept the formula is: if(month(today())>=10,YEAR(TODAY())+1,YEAR(TODAY()))-VALUE(SUBSTITUTE(Fiscal_Year_Text__c,”FY”,”20”)) This will return 0 for this year, 1 for last year, -1 for next year

Variations

This version is based on paid payments this year, but it could also be made for payments last year or two years ago.

Contributed By Beth Hintze, Attain Partners

Opportunity: List GAU Allocations

Description

Displays a concatenated list of the General Accounting Unit names from related GAU allocation records in a single field. GAU Allocations are a feature of the NPSP, and there can be multiple Allocations on a single Opportunity. The concatenated field allows us to display the value in the related lists and see at a glance how a donor might restrict their giving.

Objects, Fields, Relationships

Field Value
Parent Object Opportunity
Child Object npsp__Allocation__c
Relationship Field npsp__Opportunity__c
Relationship Criteria None
Field to Aggregate General_Accounting_Unit_Name__c
Field to Order By npsp__Amount__c DESC, npsp__Percent__c DESC
Aggregate Operation Concatenate Distinct
Aggregate Result Field DLRS_GAU_Allocations_List__c
Calculation Mode Realtime
Schedule vs Child Trigger Deploy the Child Trigger

Preparation

You’ll require a custom formula text field built on npsp__Allocation__c to display the name of the General Accounting Unit.

This rollup requires test code to function correctly.

Can also be run on a schedule.

Contributed By Rachel Sinex, Pedal Lucid and Maida Rider, Jesuit Refugee Service

Contact: Sum of won Tribute Gifts

Description

Calculate the total amount of won tribute gifts received in honor of a Contact. Note that the relationship between the objects here is via the Honoree Contact lookup (which is an NPSP package field), not the Primary Contact.

Field Value
Parent Object Contact
Child Object Opportunity
Relationship Field npsp__Honoree_Contact__c
Relationship Criteria (SOQL Query) npsp_Tribute_Type__c != null AND isWon = True
Relationship Criteria Fields npsp_Tribute_Type__c, isWon
Field to Aggregate Amount
Order By Field n/a
Aggregate Operation SUM
Aggregate Result Field Sum_of_Tribute_Gifts__c
Calculation Mode Realtime
Schedule vs Child Trigger Child Trigger deployed

Contributed By Amanda Styles, Traction on Demand

Campaign: Total Amount Won from Opportunity Record Type or Lead Source

Description

These are two variations for summarizing the total amount of a specific category of won Opportunities on a Campaign record. The first is for a specific Record Type of ‘Donation’ (from Laurel Taylor), and the second is for Opportunities where the Lead Source is ‘Web’ (from Amy Utkan).

Objects, Fields, Relationships

Field Value
Parent Object Campaign
Child Object Opportunity
Relationship Field CampaignId
Relationship Criteria (SOQL Query) RecordType.Name = Donation AND Stage <> ‘Closed Lost’ or LeadSource = ‘Web’
Relationship Criteria Fields RecordType.Name, Stage or LeadSource
Field to Aggregate Amount
Order By Field n/a
Aggregate Operation SUM
Aggregate Result Field Total_Related_Donations__c or Opportunities_from_Web_Source__c
Calculation Mode Realtime
Schedule vs Child Trigger Child Trigger deployed

Variations

Count Opportunities where a matching gift has been received ( Relationship Criteria: Matching_Gift_Status = ‘Received’, Aggregate Operation: COUNT)

Contributed By Laurel Taylor, Town Hall Seattle and Amy Utkan, BRDPro

Contact: Current Active Recurring Donations

Description

Shows whether or not this contact has 1 or more currently active recurring donations (uses the NPSP Recurring Donations custom object).

Field Value
Parent Object Contact
Child Object npe03__Recurring_Donation__c
Relationship Field npe03__Contact__c
Relationship Criteria (SOQL Query) npsp__Status__c=’Active’
Relationship Criteria Fields npsp__Status__c
Field to Aggregate Name
Order By Field n/a
Aggregate Operation COUNT
Aggregate Result Field Related_Active_Recurring_Donations__c
Calculation Mode Realtime
Schedule vs Child Trigger Child Trigger deployed. Could also be scheduled to run nightly.

Preparation

Because NPSP, I had to do some hunting for test code in GitHub

Contributed By Marc Baizman, Salesforce.com

Back to top

Declarative Lookup Roll-Up Summaries is a tool built and maintained by a community of volunteers through the Open Source Commons program. This is not a Salesforce-owned product. If you need support, go to the Trailblazer Community Group.

Edit this page on GitHub