Apex Code Development You need to sign in to do that
Don't have an account?
jhart BUG: Roll-up summary fields incorrect in certain cases
Roll-up summary fields are wrong in a certain case.
It relates to many:many relationships and cascading deletes.
Here's a stripped-down test case (not quite real world, but almost identical to the more complicated real thing):
We want a new object, "PhoneNumber", which normalizes all unique Phone numbers found in Contact records. It is required that the PhoneNumber object has a roll-up summary field, "Contact Count", which gives the current number of Contacts with that phone number (in their phone/mobile/business etc fields). This is a many:many relationship because multiple Contacts can have the same phone number, and a Contact has multiple phone numbers.
Expressing this relationship requires two custom objects in Salesforce.com:
PhoneNumber__c.Name // the text string of the phone number
PhoneNumber__c.ContactCt0__c // roll-up: COUNT Phone2Contact
Phone2Contact__c.PhoneNumber__c // Master-Detail(Phone Number)
Phone2Contact__c.Contact__c // Lookup(Contact)
Triggers are used to maintain the "PhoneNumber" and "Phone2Contact" tables.
The problem occurs when a user deletes an Account. All of the Contacts in the Account are deleted, but no triggers are fired for the Contact deletion. I could write a trigger on Account delete/undelete, but if the Account has too many Contacts the trigger will fail. (see this post).
Of course, the Phone2Contact records would be cascase-deleted IF Phone2Contact were defined in Master-Detail with the Contact, rather than the PhoneNumber, but the "Contact Count" roll-up summary requirement precludes this.
So those orphaned Phone2Contact__c records are not deleted. However, they are updated - their Contact lookup field is set to null (actually, null-ish .. see below). And, usefully, if the Account is undeleted - thus undeleting its Contacts - the Contact lookup field will be reset correctly.
So, if all my queries against Phone2Contact__c are careful to filter on Contact__c != null, that should give me what I need.
The problem lies in the "Contact Count" Roll-Up summary field. It is no longer accurate, because the orphaned Phone2Contact records still exist (with a NULL Contact__c field).
I cannot define the roll-up as COUNT(Phone2Contact__c where Contact__c NOT EQUAL TO NULL) because "NULL" is interpreted here as a literal string rather than the null value. There is no way to enter a blank string (or some signifier of null) into the "Value" field of the "not equal to" operator. The only filter options available for a field of type lookup are equals / not equal to / starts with.
OK, so we get creative. If we can't use a filtered "COUNT" roll-up, perhaps we can use a SUM roll-up that references a formula on Phone2Contact__c.
Let's add two fields to Phone2Contact__c:
Phone2Contact__c.ContactCt1__c // IF(ISNULL(Contact__c),0,1)
Phone2Contact__c.ContactCt2__c // IF(0 = len(Contact__c), 0, 1)
And let's roll them up into PhoneNumber:
PhoneNumber__c.ContactCt1__c // roll-up: SUM Phone2Contact.ContactCt1__c
PhoneNumber__c.ContactCt2__c // roll-up: SUM Phone2Contact.ContactCt2__c
Now let's see how all those fields behave in a test case:
Code:
public abstract class testPhoneNumber {
private static testMethod void testAccountDelete() {
// create a new Account (for easy delete/undelete)
Account a = new Account(Name = 'Test Account 1');
insert a;
// create a new Contact in that account
Contact c = new Contact(LastName = 'Test', AccountId = a.Id, Phone='(999)999-9999');
insert c;
// do what triggers would have done for us
PhoneNumber__c p = new PhoneNumber__c(Name = c.Phone);
insert p;
Phone2Contact__c p2c = new Phone2Contact__c(Contact__c = c.Id, PhoneNumber__c = p.Id);
insert p2c;
dump(p2c.Id, 'before account delete');
delete a;
dump(p2c.Id, 'after account delete');
undelete a;
dump(p2c.Id, 'after account undelete');
}
private static void dump(Id id, string label) {
Phone2Contact__c p2c = [select Contact__c, ContactCt1__c, ContactCt2__c
,PhoneNumber__r.ContactCt0__c, PhoneNumber__r.ContactCt1__c, PhoneNumber__r.ContactCt2__c
from Phone2Contact__c where Id = :id];
System.debug('\r\n\r\n' + label
+ '\r\n' + p2c.Contact__c + ' = p2c.Contact__c'
+ '\r\n' + p2c.ContactCt1__c + ' = count1 = IF(ISNULL(Contact__c),0,1)'
+ '\r\n' + p2c.ContactCt2__c + ' = count2 = IF(0 = len(Contact__c),0,1)'
+ '\r\n' + p2c.PhoneNumber__r.ContactCt0__c + ' = Roll-Up: COUNT(Phone2Contact)'
+ '\r\n' + p2c.PhoneNumber__r.ContactCt1__c + ' = Roll-Up: SUM(count1)'
+ '\r\n' + p2c.PhoneNumber__r.ContactCt2__c + ' = Roll-Up: SUM(count2)'
);
}
}before account delete
0037000000VsRxwAAF = p2c.Contact__c
1.0 = count1 = IF(ISNULL(Contact__c),0,1)
1.0 = count2 = IF(0 = len(Contact__c),0,1)
1.0 = Roll-Up: COUNT(Phone2Contact)
1.0 = Roll-Up: SUM(count1)
1.0 = Roll-Up: SUM(count2)
after account delete
null = p2c.Contact__c
1.0 = count1 = IF(ISNULL(Contact__c),0,1) // *1
0.0 = count2 = IF(0 = len(Contact__c),0,1) // *2
1.0 = Roll-Up: COUNT(Phone2Contact)
1.0 = Roll-Up: SUM(count1)
1.0 = Roll-Up: SUM(count2) // *3
after account undelete
0037000000VsRxwAAF = p2c.Contact__c
1.0 = count1 = IF(ISNULL(Contact__c),0,1)
1.0 = count2 = IF(0 = len(Contact__c),0,1)
1.0 = Roll-Up: COUNT(Phone2Contact)
1.0 = Roll-Up: SUM(count1)
1.0 = Roll-Up: SUM(count2)
*1 ok, good to know, I guess the field is empty but not null ... although I thought that wasn't possible! (in SFDC all strings are trimmed prior to storage, and empty strings are converted to null)
*2 that's what we want on the child object
*3 BUG. Why isn't this rolling up correctly?
Salesforce, any response or ideas about when a fix may be forthcoming? A data consistency issue like this seems like it should be high priority.
It's here.
Hi jhart,
Thank you for your detailed posts -- you have indeed run into a bug! We have recently discovered that Roll-Up Summary Fields that use lookup fields in filter criteria are not calculating correctly if the record that the lookup field references is deleted. This may not be a common business practice, but if it does happen, then the Roll-Up Summary Field will not recognize that change and will not recalculate. Consequently, the value of the Roll-Up Summary Field will be inaccurate from that point forward.
On Wednesday, March 19, 2008, a patch will be applied to all salesforce.com instances to block users from creating new Roll-Up Summary Fields that filter on lookup fields. You will still be able to create Roll-Up Summary Field that filter on other types of fields. We will just hide lookup fields from the filter criteria when you create or edit Roll-Up Summary Fields.
All customers who have Roll-Up Summary Fields that use lookup fields in filter criteria have been contacted, and there is a support solution with more detailed information if you are interested: please click Help and Training and search for the support solution titled, “Roll-Up Summary Fields referencing Lookup Fields.” (Solution # 00007681)
You also raise a number of other questions in your post. Please see my comments below for clarifications…
1. " All of the Contacts in the Account are deleted, but no triggers are fired for the Contact deletion."
Yes, apex trigger do not fire on the detail records on a "cascade delete" (when a Master record is deleted and the associated Detail records are deleted).
2. "I cannot define the roll-up as COUNT(Phone2Contact__c where Contact__c NOT EQUAL TO NULL) because "NULL" is interpreted here as a literal string rather than the null value. There is no way to enter a blank string (or some signifier of null) into the "Value" field of the "not equal to" operator. The only filter options available for a field of type lookup are equals / not equal to / starts with."
You cannot use the work NULL in the roll-up summary field filter, but you can say where FieldA not equal to "". To limit results to records that are blank or contain "null" values for a particular field, choose the field and the "equals" or "not equal to" operators, leaving the third field blank. For example, "Amount equals " returns records with blank amount fields. You can search for blank values or other specified values at the same time. For example, "Amount equals 1,,2" returns records where the Amount contains the value "1," "2," or is blank.
3. "*3 BUG. Why isn't this rolling up correctly? The explanation could be that roll-up summary fields are evaluated *before* the formula fields upon which they depend, but that seems backwards."
Roll-up summary fields are evaluated *after* the formula fields upon which they depend, as you expect. The reason it is not rolling up correctly, even for the formula field, is that the roll-up summary field is not aware that you have nulled the Contact__c field (because of the bug you ran into). If your field was not a lookup field, just a text field, for example, then it would behave as you expected: with a formula field called No_Mary__c: IF(0 = len(Mary__c),0,1)and a roll-up summary field summing that field, then when the user changes the value of Mary__c and hits save, then the formula field is re-evaluated and then the roll-up summary field is updated.
Thank you,
Mary Scotton
Force.com Platform Product Management
I see this error in our production org.
We have a Roll-up Summary field on Entitlement for Count of EntitlementContacts. Rollup field says 8, but there are only 4 records exist in the child object (EntitlementContact).
Thanks
Hari
Thanks