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
nateienateie 

What's the best way to "crawl" or traverse through objects in a trigger?

Hi Apex Community,

 

I'm having some trouble with a trigger and hope someone can help me out.

 

What I want to do...

For new and updated "Customer Feedback" records (Custom Obj), I want to update the corresponding "Issue" records (Custom obj) to aggregate various fields from "Customer Feedback".

 

The challenge?

The "Issue" object is three objects away.

Linkage architecture:  [CUSTOMER FEEDBACK]   <---Master Detail---   [CASE]   <---Junction--->   [ISSUE LINK]   <---Junction--->   [ISSUE]

 

My approach...

Write a trigger to the effect of the following:

 

trigger aggregateCustomerFeedbackOnIssues on Customer_Feedback__c (before insert, before update

{


  /** STEP 1: WORK MY WAY UP FROM Customer Feedback to Issue **/


  // Get list of all new/updated Customer Feedback records

  List<Customer_Feedback__c> feedbackRecords = [SELECT Id, Case__c FROM Customer_Feedback__c WHERE Id IN :Trigger.newMap.keySet()];


  // Build a set of all relevant Case records

  

  // Builds a set of the relevant IssueLink records 

 

  // Build a set of all relevant Issue records

 

 

  /** STEP 2: WORK MY WAY DOWN FROM Issue to Customer Feedback **/

    ...

}

 

The problem?

My attempts thus far have resulted in a number of errors (e.g. System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Customer_Feedback__c.Case__r:) and I'm convinced I'm approaching this incorrectly. If there's anyone out there who can point me in the right direction, I'd be mighty thankful!

 

Thanks in advance all!


AmitSahuAmitSahu

Can you query the field 'Case__r' in your SOQL query ?

 

Regards,

 

J

nateienateie

Hi J,

 

I think you're onto something.

No, I can't query "Case__r"...only "Case__c" works.

 

SELECT Id, Case__c FROM Customer_Feedback__c WHERE ...etc...


But when I attempt to access the Case__c field from the 'Customer Feedback' object, I have to use Case__r because it's a Master-Detail relationship with Case as the master. How do I properly query the case field and access it from Customer Feedback?

 

Sorry for the potentially rookie question!

dmchengdmcheng

So you may have multiple Issue records for each Case?  And you want to aggregate your feedback data into each issue record for the case?

nateienateie

Thanks for responding @dmcheng. That's right, with objects in between.

 

To reiterate, here's what I want to do:

  • Trigger: Upon new/updated Customer Feedback record
  • Action: Aggregate various data about the Customer Feedback records (e.g. Avg Satisfaction score) tied to an Issue

And the architecture looks like this:  [CUSTOMER FEEDBACK]   <---Master Detail---   [CASE]   <---Junction--->   [ISSUE LINK]   <---Junction--->   [ISSUE]

 

So there's a Master-Detail relationship between Case and Customer Feedback (Case is Master).

And there's a many-to-many relationship between Issue and Case, with Issue Link being the junction object in between.

 

My overall approach is..

STEP 1: Work my way up from Customer Feedback to Issue, identifying which Issue records I need to recalculate data for

STEP 2: Work my way down from Issue to Customer Feedback, aggregating all the Customer Feedback data

 

Just getting stuck on Step 1 in finding all the relevant Issues I need to calculate data for.

 

dmchengdmcheng

Wow.  So an issue can be linked to multiple cases.  And you want to aggregate all feedback records for all cases into a single issue record?  So if Cases 1, 2 and 3 are linked to Issue A, and there is a total of 9 feedback records linked to the three cases, then you want to aggregate Feedback.Field1__c into Agg1__c in Issue A?

dmchengdmcheng

I presume you have a good reason for keeping a separate Issue object instead of using Case to track issues, but it does make things a lot more complicated.

 

Here is a brute-force solution:

 

* Create aggregation fields in the Case object.

* write a trigger on Customer Feedback to aggregate your data and update the parent Cases.

* Write a trigger on Case to aggregate into the Issue records.

 

In your Case trigger, you'll need to the following:

1.  For each case ID, get the Issue ID from Junction.

2.  For each issue ID, use a SOQL relationship query to get all related Case records through the Junction object.

3.  Loop through the case records to aggregate into the Issue record.

4.  Update the Issue records.

 

nateienateie

Hi Community,

 

I'm through "Step 1". And now it's onto "Step 2" (working my way back down).

 

My takeaway (and this might be a rookie one): You can't pull Object B from Object A via SOQL queries.

 

I figured if Objects were linked via Master-Detail or Junction objects, I could access those referenced objects. I learned the hard way that stuff like this (trying to jump objects) doesn't work...

 

List<Issue__c> issueRecords = [SELECT Issue__c FROM IssueLink__c WHERE Case__c IN :caseIds];

 

 

Instead, I handled it this way...

 

 

...

// Get the Ids first

List<Id> issueIds = new List<Id>();

for(IssueLink__c il : issuelinkRecords)

{

  if(il.Issue__c != null)

    issueIds.add(il.Issue__r.Id);

}

 

// Then grab set of the relevant Issue records with the Ids

List<Issue__c> issueRecords = [SELECT Id, Issue_ID__c FROM Issue__c WHERE Id IN :issueIds];

 

Thanks for the insights.

nateienateie

Oops. Just saw your reply. I'm going to be doing some version of your suggestion, just all from one trigger.

Ugh. This is definitely a learning experience. Thank you so much for your advice!

dmchengdmcheng

You can traverse relationships using SOQL relationship query syntax.  See this link:

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

 

However, limits to the depth of traversal, and I think what you are trying to do may be too complicated for this and for a single trigger, so that's why I suggested the simpler brute force method.

nateienateie

Ah, I see. I very may well end up resorting to your suggested method.

 

And thanks for the clarification. You *can* traverse objects. You *can't* pull an entire object from another.