Customizable Rollups: Lingering References to Previous Fields After Modification

[jesslopez-sf]

When User Defined Rollups are converted to Customizable Rollups, we add their Opportunity field to multiple fields on the Rollup record. If an admin modifies this rollup to use another field via the Customizable Rollups interface (for example, a non-formula field), that field is updated on the Custom Metadata Type record, but may still be referenced on other fields on the Custom Metadata Type. As a result, these fields are being pulled into SOQL queries and slowing down calculations.

The Work Item will contain a link to my full investigation. For one org, I manually edited the affected CMTs and there was a noticeable performance increase in the scheduled jobs because unnecessary formula fields were no longer being queried. Rollups were also being grouped by Filter Groups much more efficiently (logs attached to Work Item).

STEPS TO REPEAT

Setup:

1. Create new fields on Opportunity:
   1. Name: Gift Amount if Three Years Ago
      1. Data Type: Formula
      2. Formula Return Type: Currency (2 decimal places)
      3. Formula: IF ( YEAR (CloseDate) = ( YEAR (TODAY()) - 3 ), Amount, NULL)
   2. Name: Number of Gifts If Three Years Ago
      1. Data Type: Formula
      2. Formula Return Type: Number (0 decimal places)
      3. Formula: IF ( YEAR (CloseDate) = ( YEAR (TODAY()) - 3 ), 1, NULL)
2. Create new fields on Contact:
   1. Name: Gift Amount Three Years Ago
      1. Data Type: Currency
      2. Length: 16
      3. Decimal Places: 2
   2. Name: Number of Gifts Three Years Ago
      1. Data Type: Number
      2. Length: 18
      3. Decimal Places: 9
3. Create User Defined Rollups using these formula fields (do not enable Customizable Rollups yet!).
   1. Go to NPSP Settings → Donations → User Defined Rollups
   2. Click New User Defined Rollup
      1. Opportunity Field: Gift Amount if Three Years Ago
      2. Rollup Operation: SUM
      3. Target Object: Contact
      4. Target Field: Gift Amount Three Years Ago
      5. Create Rollup
   3. Click New User Defined Rollup
      1. Opportunity Field: Number of Gifts If Three Years Ago
      2. Rollup Operation: SUM
      3. Target Object: Contact
      4. Target Field: Number of Gifts Three Years Ago
      5. Create Rollup

Enable Customizable Rollups and View the converted User Defined Rollups.

1. Enable Customizable Rollups in NPSP Settings → Donations → Customizable Rollups
2. Click the Configure Customizable Rollups button.

Click the Contact: Gift Amount Three Years Ago Rollup. It has been converted with the following values:
1. Target Object: Contact
2. Target Field: Gift Amount Three Years Ago
3. Operation: Sum
4. Time Frame: All Time
5. Rollup Type: Opportunity → Contact (Hard Credit)
6. Filter Group: Opps: Won (HC)
7. Amount Field: Gift Amount If Three Years Ago

The Custom MetaData Type for that record looks like this (in Setup → Custom Metadata Types → Manage Records (next to Rollups). Note that the Field to Roll Up and Amount Fields both point to the Gift Amount If Three Years Ago field.

Click the Contact: Number of Gifts Three Years Ago Rollup. It has been converted with the following values:
1. Target Object: Contact
2. Target Field: Number of Gifts Three Years Ago
3. Operation: Sum
4. Time Frame: All Time
5. Rollup Type: Opportunity → Contact (Hard Credit)
6. Filter Group: Opps: Won (HC)
7. Amount Field: Number of Gifts If Three Years Ago

The Custom MetaData Type for that record looks like this (in Setup → Custom Metadata Types → Manage Records (next to Rollups). Note that the Field to Roll Up and Amount Fields all point to the Number of Gifts If Three Years Ago field.

Both of these rollups can be edited to use native Customizable Rollups functionality. Make the following edits and see what happens to the Custom Metadata Type records.

1. Go to NPSP Settings → Donations → Customizable Rollups. Click the Configure Customizable Rollups button.

Edit the Contact: Gift Amount Three Years Ago Rollup and change it to the following values:
1. Target Object: Contact
2. Target Field: Gift Amount Three Years Ago
3. Operation: Sum
4. Time Frame: Years Ago
5. Years Ago: 3 Years Ago
6. Rollup Type: Opportunity → Contact (Hard Credit)
7. Filter Group: Opps: Won (HC)
8. Date Field: Close Date
9. Amount Field: Opportunity: Amount
10. Save (now there are no references to the formula field on the Opportunity).

Changes were reflected on the Custom Metadata Type record, but the reference to the formula field remained in the Field to Roll Up field. (The Amount field was successfully changed).

Edit the Contact: Number of Gifts Three Years Ago Rollup and change it to the following values:
1. Target Object: Contact
2. Target Field: Number of Gifts Three Years Ago
3. Operation: Count
4. Time Frame: Years Ago
5. Years Ago: 3 Years Ago
6. Rollup Type: Opportunity → Contact (Hard Credit)
7. Filter Group: Opps: Won (HC)
8. Date Field: Opportunity: Close Date
9. Save (now there are no references to the formula field on the Opportunity).

Changes were reflected on the Custom Metadata Type record, but the reference to the formula field remained in both the Field to Roll Up and Amount fields:

[jesslopez-sf]

**lurch: add

[LurchTheButler]

Tracking W-030505

[mrbelvedere]

Included in beta release 3.151 (Beta 14)

[mrbelvedere]

Included in production release 3.151