function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
AdamDawAdamDaw 

Access Grandparent Object in Trigger?

Hello Everyone,

 

I hope there's a straightforward solution to this, but I've looked over the documentation and searched the web for a while, and maybe it's the length of time I've been staring at this conundrum but I'm at a loss as to how to resolve it.

 

Here's the situation:

 

- ObjectA has a lookup relationship to ObjectB

- ObjectA has a master-detail relationship to ObjectC (A detail, C Master)

- ObjectB has a master-detail relationship to ObjectD

- ObjectD has a lookup relationship to ObjectC (D detail, C Master)

 

I'm trying to create a trigger on Object A insert/updates that will rollup the values for a field on all 'Object A's related to an Object D (through Possibly Multiple 'Object B's) and display that sum on the Object D. 

 

So right now, I'm doing the following:

 

 

trigger ARollup on ObjectA (after insert, after update) {
double sumTotalValue = 0.0;

ObjectD[] od = new ObjectD[]{};

if(Trigger.isInsert)
{
ObjectA[] aNew = trigger.new;

for(ObjectA a : aNew)
{
for (ObjectB[] ob : [select Id, ObjectBtoObjectDKey from ObjectB where Id = :a.ObjectAtoObjectBKey] )
{
for (ObjectD[] o : [select Id, SumField from ObjectD where Id in :ob.ObjectBtoObjectDKey])
{
for (ObjectA oa: [select Id, FieldtoSum from ObjectA where Id = :a.id])
{
sumTotalValue += oa.FieldtoSum;
}
o.SumField = sumTotalValue;

od.add(o);
}

}
}
update od;
}
}

 

Naturally, this doesn't work at this point, as I'm getting a "Save error: Initial term of field expression must be a concrete SObject: LIST:SOBJECT:ObjectB" error from the IDE, but I'm just not sure how to work around it. I thought about going through ObjectC, but I'm not sure if that's the place I should go.

 

I hope this makes sense - if not, I'll be happy to clarify.

 

Any help figuring out how to go about this properly would be greatly appreciated.

 

Update: I've bypassed the error by selecting where id = ob.get(0).ObjectBtoObjectDKey , but I still feel like this is wrong, so any other suggestions would be helpful.

 

 

Best Regards

 

ZG 

 

Message Edited by ZenGuin on 01-11-2010 06:48 PM
Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

Ah okay.  Thanks for the detail.

  

There's nothing wrong with using the zeroth element of the array as your update says but given that you'll only get one result for ObjectB and ObjectD arrays, you could remove the for loops.

 

However, you will run into problems if any bulk changes are made, as the way that you are carrying out SOQL queries for each element of trigger.new will quickly blow the governor limit of 20 SOQL queries. 

 

I think something like the following should be what you are looking for, but it will require a little effort on the coding side:

 

 

Put the ObjectAToObjectBKey of all elements of trigger.new into an array.

 

Select all ObjectB records whose ObjectAToObjectBKey is present in the array

 

Put the ObjectBtoObjectDKey of all Objectb records into an array.

 

Select all ObjectD records whose ObjectBtoObjectDKey is in the array.

 

Put all ObjectD ids into an array.

 

Select all ObjectB records whose ObjectBtoObjectDKey matches an id in the array.

 

Put all ObjectB ids into an array.

 

Select all ObjectA records whose ObjectAtoObjectBKey matches an id in the array.

 

 

You will now have gone up the chain based on the affected records in the trigger, then down again capturing all affected records.  You can now process these records as you see fit.  You'll proibably need to put some of the results of the queries in Maps keyed by id so that you can retrieve them quickly.

 

I know this sounds tricky, but I've done something similar in one of my projects and its not too tough as long as you have lots of comments and have planned out what you want to do in advance.

 

Of course, if you have more than 1,000 ObjectA records to include in your summary across all objectD records, you will need to look at doing this a different way, as that will blow another SOQL limit.

All Answers

bob_buzzardbob_buzzard

Would I be correct in thinking that your trigger is trying to do the following:

 

 

Iterate the A objects from the trigger

  Iterate all B objects that are related to the A object

     Iterate all D objects that are related to the B object

        add the sum field from the A object to the D object

        update the D object

 

 

 

AdamDawAdamDaw

Hello Bob,

 

Thank you for the reply!

 

My code wants to iterate for all ObjectA records attached to a specific ObjectD. So, right now, after update, insert, or delete of an ObjectA, it looks for the ObjectB that ObjectA is attached to, then looks for the ObjectD that that ObjectB is attached to, then finds all ObjectBs attached to that ObjectD, and all ObjectAs attached to those ObjectBs, then creates a sum of the value of a field on all ObjectA records and saves that sum value to a field on the ObjectD record.There's never more than one ObjectD in this scenario.

 

Best

ZG

bob_buzzardbob_buzzard

Ah okay.  Thanks for the detail.

  

There's nothing wrong with using the zeroth element of the array as your update says but given that you'll only get one result for ObjectB and ObjectD arrays, you could remove the for loops.

 

However, you will run into problems if any bulk changes are made, as the way that you are carrying out SOQL queries for each element of trigger.new will quickly blow the governor limit of 20 SOQL queries. 

 

I think something like the following should be what you are looking for, but it will require a little effort on the coding side:

 

 

Put the ObjectAToObjectBKey of all elements of trigger.new into an array.

 

Select all ObjectB records whose ObjectAToObjectBKey is present in the array

 

Put the ObjectBtoObjectDKey of all Objectb records into an array.

 

Select all ObjectD records whose ObjectBtoObjectDKey is in the array.

 

Put all ObjectD ids into an array.

 

Select all ObjectB records whose ObjectBtoObjectDKey matches an id in the array.

 

Put all ObjectB ids into an array.

 

Select all ObjectA records whose ObjectAtoObjectBKey matches an id in the array.

 

 

You will now have gone up the chain based on the affected records in the trigger, then down again capturing all affected records.  You can now process these records as you see fit.  You'll proibably need to put some of the results of the queries in Maps keyed by id so that you can retrieve them quickly.

 

I know this sounds tricky, but I've done something similar in one of my projects and its not too tough as long as you have lots of comments and have planned out what you want to do in advance.

 

Of course, if you have more than 1,000 ObjectA records to include in your summary across all objectD records, you will need to look at doing this a different way, as that will blow another SOQL limit.

This was selected as the best answer
AdamDawAdamDaw

Thanks Bob!

 

What you describe makes perfect sense - I think I stayed away from looking at that approach due to low level of comfort with maps, but I should probably bite the bullet and get comfortable :) . It seems to be working with the array set I have right now, but I'm going to look to refactor using maps in order to account for bulk changes.

 

Thanks again for the advice.

 

Best,

 

ZG