so i have 2 custom objects, rep and role history. rep is the master and role history is the detail. rep has a field "rep type" that needs to be systematically updated to reflect whatever is in the "type" field on the role history object.

  • Ok, I see now that my suggested approach in your previous question was off the mark. Can you tell us how you're enforcing (or planning to enforce) the condition about not having multiple, active role history records? (do you have a validation rule? another trigger or apex class to set the end date when a new role history is added?) – Derek F Sep 18 '19 at 23:57
  • Also, does having an end date always mean that the role history record is now inactive? What if the end date is sometime in the future? – Derek F Sep 18 '19 at 23:59
  • 1
    salesforce.stackexchange.com/questions/278399/… i was going to do a validation rule but it didn't function properly and i didn't want to reask that question on this specific topic so i was gonna just approach it after figuring this part out first. for your second comment, yes, i have a separate validation rule in place that doesn't allow to set the end date in the future – testing Sep 19 '19 at 0:46

Ok then, this calls for a change to the plan of attack.

The important detail here is that there should only be one role history that we pull data from (as opposed to aggregating the data from all role histories).

The good news is that this ends up making the code a little simpler. Subqueries (i.e. parent-child queries, or semi/anti-joins) can also have WHERE clauses to filter records, and we can take advantage of this to do the heavy lifting.

// The "WHERE End_Date__c = null ORDER BY CreatedDate DESC LIMIT 1" is the new bit
// End_Date__c = null isn't an ideal criteria, but it should do.
// Ordering by created date means that if there are (somehow) multiple child records
//   with End_Date__c equal to null, the result of the query will return the one created
//   most recently first.
//  Limit 1 is pretty self-explanatory 
parentReps = new Map<Id, Rep__c>([SELECT Id, Rep_Type__c, (SELECT Id, Type__c FROM Role_Histories__r WHERE End_Date__c = null ORDER BY CreatedDate DESC LIMIT 1) FROM Rep__c WHERE Id IN :listIds]);

    for(Rep__c reps :parentReps.values()) {
        // No need for the Set<String> anymore
        // Technically, with the Role_Histories__r subquery returning 0 or 1 rows
        //  , you don't need this loop (you could just access reps.Role_Histories__r[0]
        //  ... after checking that the list isn't empty), but
        //  using a loop is safer.
        for(Role_History__c roles :reps.Role_Histories__r) {
            // You also don't need to use String.join any more.
            // Since the Rep__c record is taking its roles from only one
            //   Role_History__c, you can set Rep_Type__c directly
            reps.Rep_Type__c = roles.Type__c;
    update parentReps.values();

Another (possibly useful) aspect of the behavior of this code is that if you don't find any suitable Role_History__c records related to a Rep__c, the trigger won't overwrite the current value of Rep_Type__c.

My bit about how using the nested loop is safer comes down to how SOQL queries work. If there are enough records, Salesforce will internally call a queryMore() method. Salesforce can handle calls to queryMore() for a subquery just fine if the results of the subquery are accessed via a nested loop like in my example. If you try to access reps.Role_Histories__r[<some positive integer>] or reps.Role_Histories__r.size(), you could get an Aggregate query has too many rows for direct assignment, use FOR loop error.

| improve this answer | |
  • thanks so much! really appreciate you helping and educating me on how to get this working! – testing Sep 19 '19 at 13:45
  • one last quick question just to better my understanding... so i used the variable roles twice. once in the beginning of the trigger when making Trigger.New and later in the very last for loop. is this correct? because, i used 2 different variables for reps... parentReps and Reps what is the reason for this? should they both just be reps? what are the implications of having them be different/same in this context? thanks! – testing Sep 19 '19 at 14:20
  • @testing If you're not familiar with the variable scopes/scoping, you should learn it. You can re-use the variable name roles because the scopes of the two roles variables don't overlap (their scope begins and ends with their respective for loops). For loops like for(Rep__c reps :parentReps.values()) { are saying something like Process all of the things on the right of the colon one at a time, putting one of them into the variable on the left each time. reps is only available inside the loop, and the data feeding your loop needs to be available outside of the loop's scope. – Derek F Sep 19 '19 at 14:42

Based on your example, it should show role1; role2, but is it showing role1; role1; role2 ?

The reason for that is when you are querying the parentReps you are fetching all the child objects and iterating over the entire list, while what you wanted is to filter out the Active ones (the one that doesn't have end date stamped on it). So, add the filter condition when you are adding roles.Type__c to valuesSet

Replace the line

parentReps = new Map<Id, Rep__c>([SELECT Id, Rep_Type__c, (SELECT Id, Type__c FROM Role_Histories__r) FROM Rep__c WHERE Id IN :listIds]);


parentReps = new Map<Id, Rep__c>([SELECT Id, Rep_Type__c, (SELECT Id, End_Date__c, Type__c FROM Role_Histories__r) FROM Rep__c WHERE Id IN :listIds]);

Replace the line



if(roles.End_Date__c == null) valuesSet.add(roles.Type__c); // assuming End_date__c is your field name

Please comment if you still have difficulty or if you need any further clarification

| improve this answer | |
  • Sorry, my bad. I updated the answer. You need to query End_Date__c field as well when you are getting the parent records. That exception is because the query was not fetching the End_Date__c field on Role history object – Prudhvi Konda Sep 19 '19 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.