Opportunities, Payments and Allocations Recipes
Campaign: Campaign Hierarchies of Opportunities
Sum Watch for Changes
Description
This recipe sums on a Campaign the Amount of its received gift Opportunities, for Campaigns that are part of a Campaign hierarchy.
Objects, Fields, Relationships
| Field | Value |
|---|---|
| Parent Object | Campaign |
| Child Object | Opportunity |
| Relationship Field | CampaignId |
| Relationship Criteria | Has_Parent_Campaign__c = TRUE AND (StageName = 'Pledged' OR StageName = 'Accounting Received' OR StageName = 'Distribution Received' OR StageName='Stewardship' OR StageName='Approved') |
| Relationship Criteria Fields | StageName |
| Field to Aggregate | Amount |
| Field to Order By | n/a |
| Aggregate Operation | SUM |
| Aggregate Result Field | Total_Revenue_of_Received_Gifts__c |
| Calculation Mode | Watch for Changes and Process Later |
| Schedule vs Child Trigger | Deploy the Child Trigger |
Preparation
Has_Parent_Campaign__c is a Formula field that checks if the related Campaign has a ParentId.
Contributed By Aron Schor
Campaign: Total Amount Won from Opportunity Record Type or Lead Source
Sum Realtime
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 |
| Field(s) to Order By | 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 and Amy Utkan
Contact: Current Active Recurring Donations
Count Realtime
Description
This recipe 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 | npsp__Status__c=’Active’ |
| Relationship Criteria Fields | npsp__Status__c |
| Field to Aggregate | Name |
| Field(s) to Order By | 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
Contact: Sum of Won Tribute Gifts
Sum Realtime
Description
This recipe calculates 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 | npsp_Tribute_Type__c != null AND isWon = True |
| Relationship Criteria Fields | npsp_Tribute_Type__c, isWon |
| Field to Aggregate | Amount |
| Field(s) to Order By | 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
Contact: Tag Contact as a Major Sustainer
Sum Watch for Changes
Description
This recipe marks a Contact as a Major Donor based on whether or not any of the Contact’s Active Recurring Donations reaches a certain Amount.
Objects, Fields, Relationships
| Field | Value |
|---|---|
| Parent Object | Contact |
| Child Object | npe03__Recurring_Donation__c |
| Relationship Field | npe03__Contact__c |
| Relationship Criteria | Major_Sustainer_Calculation__c > 0 |
| Relationship Criteria Fields | Major_Sustainer_Calculation__c |
| Field to Aggregate | Major_Sustainer_Calculation__c |
| Field to Order By | n/a |
| Aggregate Operation | SUM |
| Aggregate Result Field | Major_Sustainer__c |
| Calculation Mode | Watch for Changes and Process Later |
| Schedule vs Child Trigger | Deploy the Child Trigger |
Preparation
Major_Sustainer_Calculation__c is a Formula field that gives a value of 1 if the Recurring Donation reflects an annual Amount (npe03__Amount__c) a certain number or higher, based on Amount and Installment Period (npe03__Installment_Period__c) and is Active (npsp__Status__c).
Contributed By Aron Schor
Contact: Tag Contact as an Active Sustainer
Last Invocable by Automation
Description
This recipe marks a Contact as an Active Sustainer based on whether or not the Contact’s most recent Recurring Donations is Active.
Objects, Fields, Relationships
| Field | Value |
|---|---|
| Parent Object | Contact |
| Child Object | npe03__Recurring_Donation__c |
| Relationship Field | npe03__Contact__c |
| Relationship Criteria | Active_Sustainer__c = TRUE |
| Relationship Criteria Fields | Active_Sustainer__c |
| Field to Aggregate | Active_Sustainer__c |
| Field to Order By | n/a |
| Aggregate Operation | LAST |
| Aggregate Result Field | Active_Sustainer__c |
| Calculation Mode | Invocable by Automation |
| Schedule vs Child Trigger | Schedule, Child Trigger not deployed |
Preparation
Active_Sustainer__c is a Recurring Donation formula field that uses npsp__Status__c = Active, along with another field unique to our org..
Contributed By Aron Schor
Opportunity: List GAU Allocations
Concatenate Distinct Realtime
Description
This recipe 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 | n/a |
| Field to Aggregate | General_Accounting_Unit_Name__c |
| Field to Order By | npsp__Amount__c DESC, npsp__Percent__c DESC |
| Aggregate Operation | CONCATENATE DISTINCT |
| Concatenate Delimiter | , |
| 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__cto 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 and Maida Rider
Opportunity: Sum of Paid Payments This Financial Year
Sum Realtime
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(s) 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
Opportunity: Sum of Paid Payments This Relative Fiscal Year
Sum Realtime
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 | 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(s) 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
- 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
- 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