Add Additional Fields to the NPSP Lookups

[brianpickett]

The purpose of this suggestion is to add additional fields to the NPSP which complement the existing fields related to donation timing. For example, the NPSP currently provides the First Gift Date, but not the First Gift Amount or First Gift Campaign. This suggestion is to expand, on both the Contact and Account objects, the fields calculated for first gift, last gift, smallest gift and largest gift so that all four categories calculate the date, amount and campaign for that category.

With these fields users can perform better segmentation, knowing the types of outreach specific supporters respond well to.

The concept is to add the following additional fields and associated rollup code to both the Contact and Account objects in the NPSP: First Gift Amount (currency) First Gift Campaign (lookup) Largest Gift Date (date) Largest Gift Campaign (lookup) Smallest Gift Date (date) Smallest Gift Campaign (lookup) Last Gift Campaign (lookup)

Further Detail https://docs.google.com/document/d/19MDBSmGT-Lv28Y4Aq4bFrfMtU-R3JqonXyhJ6YrONbY/edit#

[judisohn]

**lurch: add

[LurchTheButler]

Tracking W-008642

[njjc]

@brianpickett thanks for writing up the spec! @davidhabib had a brilliant idea as we were discussing this, that instead of proliferating rollup fields or somehow allowing UDR for first/last/largest, that we could simply provide lookups on the contact/account to those opportunities. That would allow cross object formulas to pull amount, date, campaign, or any other opportunity information. What's your take on that solution?

[davidhabib]

On further reflection, I'm not sure we can do this efficiently, since I do not believe we can get the Opp Id from the aggregate query that we get the other metrics with. Ie, we are conceptually doing "select min(closedate), max(closedate), min(amount), max(amount) from Opportunity...". I don't think there is any way in this query to also get the oppid for each of those statistics. We'd thus be required to do additional querying to find the appropriate opps, which I think would be too expensive to do in our rollup code.

If I'm wrong, and we can efficiently get this info, then great. And that's where I suggest storing the appropriate opps, rather than the specific fields Brian suggests, just to make it flexible for the user's needs.

[brianpickett]

Agreed ~ linking the opportunities is way better. Nicolas ~ thoughts?

Brian Pickett
North Peak
p: (858) 952-1007
e: brian@northpeakcrm.com <bpickett@gmail.com>

On Tue, Nov 10, 2015 at 10:10 AM, David Habib <notifications@github.com>
wrote:

> On further reflection, I'm not sure we can do this efficiently, since I do
> not believe we can get the Opp Id from the aggregate query that we get the
> other metrics with. Ie, we are conceptually doing "select min(closedate),
> max(closedate), min(amount), max(amount) from Opportunity...". I don't
> think there is any way in this query to also get the oppid for each of
> those statistics. We'd thus be required to do additional querying to find
> the appropriate opps, which I think would be too expensive to do in our
> rollup code.
>
> If I'm wrong, and we can efficiently get this info, then great. And that's
> where I suggest storing the appropriate opps, rather than the specific
> fields Brian suggests, just to make it flexible for the user's needs.
>
> —
> Reply to this email directly or view it on GitHub
> <https://github.com/SalesforceFoundation/Cumulus/issues/1805#issuecomment-155517835>
> .
>

[njjc]

Dave is right that the current implementation won't make this easy, but I also think we should keep a record of the "ideal specification" separate from the implementation details.

[brianpickett]

Can we discuss both routes on a call? We have a client interested in seeing this happen and will contribute developer hours that we will match.

[njjc]

Hi Brian, happy to discuss on a call. I'm currently speccing out a bunch of changes to rollups, most importantly payment rollups, so this fits well into that work. Shoot me an email and we'll discuss: ncampbell@salesforce.com

On Thu, Nov 12, 2015 at 8:04 AM, Brian Pickett notifications@github.com wrote:

Can we discuss both routes on a call? We have a client interested in seeing this happen and will contribute developer hours.

— Reply to this email directly or view it on GitHub https://github.com/SalesforceFoundation/Cumulus/issues/1805#issuecomment-156149286 .

[judisohn]

Another request: https://powerofus.force.com/0D58000002Q2w0v

[mpusto]

Another request: https://powerofus.force.com/0D58000002ki401

[tjwarfield]

Hi all, I have first and last gift amounts and largest gift date fields in the Save The Bay instance already coded - they've been live for years. Would it be any help for me to throw that code in here as a starting point? I don't know how streamlined it is, but it works, even in the16GB database.

[kbromer]

Would be curious to see that code @tjwarfield if you're able to share. The challenge has always been that I believe first/last gift amounts require some additional overhead (likely at least one additional SOQL) since we're retrieving aggregates values here - so we can get the largest/smallest gift overall, or even in a particular FY, but unless we expand the querying, we cannot retrieve a specific value (the one corresponding to the Oppty that is first/last based on the close date)

Generally speaking, that's the challenge with any specific oppty values that correspond to a particular oppty based on the returned aggregate - we would likely require a re-query based on the oppty value (which is also dangerous as the query may be non-deterministic, think two opptys on the same date that is the min close date), and would be significantly less efficient than our current rollup set, so would require some pretty expansive testing at scale.

All that said, this would need to be evaluated with the existing work @njjc's already started on evaluating rollup improvements and incorporations of payment-based rollups too - definitely a great thing to look at!

[tjwarfield]

@kbromer - what is the best way to get that apex into this post? copy and paste, or is there a way to export and attach?

sorry, new to github

[mpusto]

In the write box there is a little icon <>. Click on that and you can insert code.

[tjwarfield]

Interesting - Looks like when we moved to NPSP, we were able to get these fields with Rollup Helper

Largest Hard Credit Date = MAX Close Date SELECT CloseDate , AccountId FROM Opportunity WHERE isDeleted = false AND opp_is_accounts_largest__c = TRUE AND amount != 0 ORDER BY CloseDate ASC NULLS LAST

opp_is_accounts_largestc is a formula field on the Opp Amount = Account.npo02LargestAmount__c

Did the same for First Hard Credit Amount

[tjwarfield]

looks like the only apex that was required for donation summaries was for soft credits (custom object) - if any of this gets used, Vinnie Bacon should get his shout out. I can get any signatures needed from him.

`global class DirectedGivingUpdate {

/* 6/21/2015 - Vinnie Bacon

Revising this code for the new instance of Salesforce.

There are only three fields that can't be calculated by rollups or Rollup Helper. These are all on the Account object:

Directed First Amount - First_Directed_Transaction_Amount__c
Directed Last Amount - Last_Directed_Transaction_Amount__c
Directed Largest Date - Largest_Directed_Transaction_Date__c

*/

// START OF MAIN FUNCTION @future public static void DirectedGivingUpdate() {

String thisClass = 'DirectedGivingUpdate.cls'; String errorEmailAddress = Label.Admin_Email_Address; Boolean debug = TRUE;

String queryClearString = '';

List existingDirectedGivingAccounts = new List(); Integer numExistingDirectedGivingAccounts = 0; Integer numModifiedDirectedGivingAccounts = 0; List theDirectedGivingAccountsDel = new List();

List theDirectedGivingSoftCredits = new List();

List theAccountFirstLastLargestData = new List();

List accountsToModify = new List(); List accountsToModify_deduped = new List(); Map<ID, Account> accountsToModify_dedupedMAP = new Map<ID, Account>(); Integer numAccounts = 0;

ID anAccountID; Boolean foundRow;

AccountFirstLastLargestData aflld; AccountFirstLastLargestData new_aflld;

//GetExistingDirectedGivingData();

queryClearString = 'SELECT Id FROM Account'; queryClearString += ' WHERE (First_Directed_Transaction_Amountc != NULL'; queryClearString += ' OR Last_Directed_Transaction_Amount__c != NULL'; queryClearString += ' OR Largest_Directed_Transaction_Datec != NULL)';

existingDirectedGivingAccounts = database.query(queryClearString); numExistingDirectedGivingAccounts = existingDirectedGivingAccounts.size();

//ClearExistingDirectedGivingData();

if (existingDirectedGivingAccounts.size() > 0) { for (Account anAccount2 : existingDirectedGivingAccounts) { Account toBeDelAccount = new Account(); toBeDelAccount.ID = anAccount2.ID; toBeDelAccount.First_Directed_Transaction_Amountc = NULL; toBeDelAccount.Last_Directed_Transaction_Amount__c = NULL; toBeDelAccount.Largest_Directed_Transaction_Datec = NULL; theDirectedGivingAccountsDel.add(toBeDelAccount); }

if (theDirectedGivingAccountsDel.size() > 0) {
  try {
    update theDirectedGivingAccountsDel;
  } catch (Dmlexception e) {
    System.debug('ERROR in ClearExistingDirectedGivingData(): ' + e);
    STB_Utilities.sendExceptionEmail(e, 1, thisClass, errorEmailAddress);        
  }
}  

}

//getAllDirectedGivingSoftCredits();

String querySoftCreditString;

querySoftCreditString = 'SELECT Accountc, Donationc,'; querySoftCreditString += ' Amountc, Donation_Close_Datec'; querySoftCreditString += ' FROM Giving_Historyc'; querySoftCreditString += ' WHERE Donationr.Amount != 0'; querySoftCreditString += ' AND isDeleted != TRUE'; querySoftCreditString += ' AND received__c = TRUE';
querySoftCreditString += ' AND Directed_Giving__c = TRUE';

theDirectedGivingSoftCredits = database.query(querySoftCreditString);

//processSoftCredits();

if (theDirectedGivingSoftCredits != NULL) {
for (Giving_History__c sc1 : theDirectedGivingSoftCredits) {
  foundRow = FALSE; 
  if(theAccountFirstLastLargestData != NULL) {
    for(Integer i = 0; i < theAccountFirstLastLargestData.size(); i++) {
      aflld = theAccountFirstLastLargestData.get(i);
      anAccountID = aflld.accountID;
      if(debug) { system.debug('DEBUG - i = ' + i + '. sc1.Account__c = ' + sc1.Account__c + '.  anAccountID = ' + anAccountID + '.'); }
      if(debug) { system.debug('DEBUG - sc1.Donation_Close_Date__c = ' + sc1.Donation_Close_Date__c + '.  aflld.firstOppDate = ' + aflld.firstOppDate + '.  aflld.lastOppDate = ' + aflld.lastOppDate + '.'); }
      if (sc1.Account__c == anAccountID) {
        if(debug) { system.debug('It is a match!'); }
        if (sc1.Donation_Close_Date__c < aflld.firstOppDate) {
          aflld.firstOppID = sc1.Donation__c;
          aflld.firstOppAmount = sc1.Amount__c;
          aflld.firstOppDate = sc1.Donation_Close_Date__c;
        }
        if (sc1.Donation_Close_Date__c > aflld.lastOppDate) {
          aflld.lastOppID = sc1.Donation__c;
          aflld.lastOppAmount = sc1.Amount__c;
          aflld.lastOppDate = sc1.Donation_Close_Date__c;
        }
        if (sc1.Amount__c > aflld.largestOppAmount) {
          aflld.largestOppID = sc1.Donation__c;
          aflld.largestOppAmount = sc1.Amount__c;
          aflld.largestOppDate = sc1.Donation_Close_Date__c;
        }
        foundRow = TRUE;
        break;
      }  
    } 
  }
  if (foundRow == FALSE) {
    // We never found an AccountFirstLastLargestData with this account ID.
    // Thus, we create a new record here.
    if(debug) { system.debug('CREATING NEW AFLLD + sc1.Account__c = ' + sc1.Account__c + '.'); }
    new_aflld = new AccountFirstLastLargestData();
    new_aflld.accountID = sc1.Account__c;
    new_aflld.firstOppID = sc1.Donation__c;
    new_aflld.firstOppAmount = sc1.Amount__c;
    new_aflld.firstOppDate = sc1.Donation_Close_Date__c;
    new_aflld.lastOppID = sc1.Donation__c;
    new_aflld.lastOppAmount = sc1.Amount__c;
    new_aflld.lastOppDate = sc1.Donation_Close_Date__c;
    new_aflld.largestOppID = sc1.Donation__c;
    new_aflld.largestOppAmount = sc1.Amount__c;
    new_aflld.largestOppDate = sc1.Donation_Close_Date__c;
    theAccountFirstLastLargestData.add(new_aflld);
  }
} // for  

} else { STB_Utilities.sendErrorMessageEmail('theDirectedGivingSoftCredits is NULL', 2, thisClass, errorEmailAddress); }

//updateAccounts();

for(AccountFirstLastLargestData aflld2 : theAccountFirstLastLargestData) { if(debug) { system.debug('aflld2.accountID = ' + aflld2.accountID + '.'); } Account anAcc = new Account(); anAcc.ID = aflld2.accountID; anAcc.First_Directed_Transaction_Amountc = aflld2.firstOppAmount; anAcc.Last_Directed_Transaction_Amount__c = aflld2.lastOppAmount; anAcc.Largest_Directed_Transaction_Datec = aflld2.largestOppDate; AccountsToModify.add(anAcc); }

numModifiedDirectedGivingAccounts = AccountsToModify.size(); if(debug) { system.debug('numModifiedDirectedGivingAccounts = ' + numModifiedDirectedGivingAccounts + '.'); }

// These few lines are needed to dedupe the updates and inserts. for (Account a2 : AccountsToModify) { accountsToModify_dedupedMAP.put(a2.ID, a2); if(debug) { system.debug('DEBUG - a2.Name = ' + a2.Name + '. a2.ID = ' + a2.ID + '.'); } } accountsToModify_deduped = accountsToModify_dedupedMAP.values();

if (AccountsToModify_deduped.size() > 0) { try { update accountsToModify_deduped; } catch (Dmlexception e) { System.debug('ERROR in updateAccounts: ' + e); STB_Utilities.sendExceptionEmail(e, 3, thisClass, errorEmailAddress);
} }

if(debug) { system.debug('accountsToModify.size() = ' + accountsToModify.size() + '.'); } if(debug) { system.debug('accountsToModify_deduped.size() = ' + accountsToModify_deduped.size() + '.'); }

//sendEmail();

String[] toAddresses = new String[] {errorEmailAddress};

Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage(); mail.setToAddresses(toAddresses);

DateTime isNow = Datetime.Now(); String dateTimeString = String.valueOf(isNow);

mail.setSubject('Ran Directed Giving Update on ' + dateTimeString);

mail.setPlainTextBody('Ran Directed Giving Update on ' + dateTimeString + '.\n\n'

• ' Number of Existing Directed Giving accounts = ' + numExistingDirectedGivingAccounts + '.\n\n'
• ' Number of Modified Directed Giving accounts = ' + numModifiedDirectedGivingAccounts + '.\n\n' ); mail.setHtmlBody('

  Ran Directed Giving Update on ' + dateTimeString + '.'

• '

  Number of Existing Directed Giving accounts = ' + numExistingDirectedGivingAccounts + '.'

• '

  Number of Modified Directed Giving accounts = ' + numModifiedDirectedGivingAccounts + '.'

• '

  accountsToModify.size() = ' + accountsToModify.size() + '.'

• '

  accountsToModify_deduped.size() = ' + accountsToModify_deduped.size() + '.

  ' );

  Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

} /*** END OF MAIN FUNCTION **/ /******/

public void GetExistingDirectedGivingData() { // This query finds all contacts with any existing Directed Giving data. // (In the three fields addressed by this code anyway.)

}

public void ClearExistingDirectedGivingData() {

}

public void getAllDirectedGivingSoftCredits() {

}

public void processSoftCredits() {

}

public void updateAccounts() {

}

public class AccountFirstLastLargestData { public Id accountID { get; private set; } public Id firstOppID { get; private set; } public Decimal firstOppAmount { get; private set; } public Date firstOppDate { get; private set; } public Id lastOppID { get; private set; } public Decimal lastOppAmount { get; private set; } public Date lastOppDate { get; private set; } public Id largestOppID { get; private set; } public Decimal largestOppAmount { get; private set; } public Date largestOppDate { get; private set; } }

public void sendEmail() { }

}`

[cpolcyn]

Included in beta release 3.128 (Beta 7)

[mrbelvedere]

Included in production release 3.128