+ Start a Discussion
Todd B.Todd B. 

Not Pulling the Right Value

I wrote the following trigger to pull the Max date from a child record.  The problem is, it is not pulling the Max value it's pulling the value of the last child record entered.  I tried using the Select Max(Enrollment_Status_Date__c) but I receved an error, Malformed Query - Only root queries support aggregate expressions.

Any ideas how to get the max value?

<pre>
trigger Update_Latest_Enrollment_Date_v02 on SD_Mbr_Enroll_Cycles__c (after insert, after update) {

Map<id,SD_Member__C> SDM = New Map<id,SD_Member__C>();
List<id> sdId = New List<id>();

For (SD_Mbr_Enroll_Cycles__c sdec : Trigger.new) {
        sdid.add(sdec.SD_Member_Stakeholders__c);}

System.debug(sdid);

SDM =  New Map<id,SD_Member__C>([Select Id, Name, Enrollment_Date_Latest_OnTrak__c,
                                (select SD_Member_Stakeholders__c, Enrollment_Status__c, Enrollment_Status_Date__c
                                          From SD_Mbr_Enroll_Cycles__r
                                          Where Use_Me__c = True
                                          and Enrollment_Status__c = 'Enrolled'
                                          ORDER BY Enrollment_Status_Date__c DESC LIMIT 1)
                                From  SD_Member__c
                                Where ID IN : sdid ]);
                           
For(SD_Mbr_Enroll_Cycles__c EC: Trigger.new){
    SD_member__c sd1 = sdm.get(ec.SD_Member_Stakeholders__c);
    sd1.Enrollment_Date_Latest_OnTrak__c = ec.Enrollment_Status_Date__c;
   
    System.debug(ec.Enrollment_Status_Date__c);
}

Update sdm.values();

}
</pre>
Best Answer chosen by Todd B.
Phillip SouthernPhillip Southern
Since the trigger is on the child object....you could just do one straight query to pull its parent date value and then compare, update as needed.  Benefit is you arent doing updates to unneccesary data, only when a child has a new value you want to push up to the parent...so could work like this: (please excuse typos, typed freehand)

trigger Update_Latest_Enrollment_Date_v02 on SD_Mbr_Enroll_Cycles__c (after insert, after update) {
       set<id> recordstorepull = new set<id>();
       List<sd_member__c> toupdate = new List<sd_member__c>();

       for(SD_Mbr_Enroll_Cycles__c sd : trigger.new)
      {
             if(sd.enrollment_status_date__c !=null && sd.sd_member__c!=null
              && sd.use_me__c = true && sd.enrollment_status__c = 'Enrolled')
              {
                        recordstorepull.add(sd.Id);
              }
       }

      if(recordstorepull.size()==0) return;

      for(sd_mbr_enroll_cycles__c sd2 : [select id, enrollment_status_date__c, sd_member__c,   sd_member__c.enrollment_date_latest_ontrak__c
                                                                       from sd_mbr_enroll_cycles__c where id IN :recordstorepull])
      {
              if(sd2.sd_member__r.enrollment_date_latest_ontrak__c == null
              || sd2.sd_member__r.enrollment_date_latest_ontrak__c < sd2.enrollment_status_date__c)
              {
                     sd_member__c tempMember = new sd_member__c(id=sd2.sd_member__c);
                       tempMember.enrollment_date_latest_ontrak__c = sd2.enrollment_status_date__c);
                        toupdate.add(tempMember);

      }

      if(toupdate.size()>0) update toupdate;
}

All Answers

Phillip SouthernPhillip Southern
Since the trigger is on the child object....you could just do one straight query to pull its parent date value and then compare, update as needed.  Benefit is you arent doing updates to unneccesary data, only when a child has a new value you want to push up to the parent...so could work like this: (please excuse typos, typed freehand)

trigger Update_Latest_Enrollment_Date_v02 on SD_Mbr_Enroll_Cycles__c (after insert, after update) {
       set<id> recordstorepull = new set<id>();
       List<sd_member__c> toupdate = new List<sd_member__c>();

       for(SD_Mbr_Enroll_Cycles__c sd : trigger.new)
      {
             if(sd.enrollment_status_date__c !=null && sd.sd_member__c!=null
              && sd.use_me__c = true && sd.enrollment_status__c = 'Enrolled')
              {
                        recordstorepull.add(sd.Id);
              }
       }

      if(recordstorepull.size()==0) return;

      for(sd_mbr_enroll_cycles__c sd2 : [select id, enrollment_status_date__c, sd_member__c,   sd_member__c.enrollment_date_latest_ontrak__c
                                                                       from sd_mbr_enroll_cycles__c where id IN :recordstorepull])
      {
              if(sd2.sd_member__r.enrollment_date_latest_ontrak__c == null
              || sd2.sd_member__r.enrollment_date_latest_ontrak__c < sd2.enrollment_status_date__c)
              {
                     sd_member__c tempMember = new sd_member__c(id=sd2.sd_member__c);
                       tempMember.enrollment_date_latest_ontrak__c = sd2.enrollment_status_date__c);
                        toupdate.add(tempMember);

      }

      if(toupdate.size()>0) update toupdate;
}
This was selected as the best answer
Todd B.Todd B.
Thanks Philip, I had to do a little tweaking, but i worked.   I appreciate the help.

The final code is:

<pre>
trigger Update_Latest_Enrollment_Date_v03 on SD_Mbr_Enroll_Cycles__c (after insert, after update) {
/*
  Developer     : Todd Barry
  Created       : 03/03/2014
  Last Modified : 03/04/2014
  Test Class    : Test_Last_Enrollment_Logic
  Objective     : Update the corresponding Enrollment Date (Latest) - OnTrak Field on the SD Member record.
*/

set<id> recordstorepull = new set<id>();
List<sd_member__c> toupdate = new List<sd_member__c>();

       for(SD_Mbr_Enroll_Cycles__c sd : trigger.new)
      {
             if(sd.enrollment_status_date__c !=null && sd.use_me__c == true && sd.enrollment_status__c == 'Enrolled')
              {
                        recordstorepull.add(sd.SD_Member_Stakeholders__c);
              }
       }

      if(recordstorepull.size()==0) return;

      for(sd_mbr_enroll_cycles__c sd2 : [select id, enrollment_status_date__c,  SD_Member_Stakeholders__c,  
                                         SD_Member_Stakeholders__r.enrollment_date_latest_ontrak__c
                                         from sd_mbr_enroll_cycles__c where SD_Member_Stakeholders__c IN :recordstorepull
                                         ORDER BY Enrollment_Status_Date__c DESC LIMIT 1])
      {

      //        if(sd2.SD_Member_Stakeholders__r.enrollment_date_latest_ontrak__c == null
      //        || sd2.SD_Member_Stakeholders__r.enrollment_date_latest_ontrak__c < sd2.enrollment_status_date__c)
              {
                     sd_member__c tempMember = new sd_member__c(id=sd2.SD_Member_Stakeholders__c);
                     tempMember.enrollment_date_latest_ontrak__c = sd2.enrollment_status_date__c;
                     toupdate.add(tempMember);

      }

      if(toupdate.size()>0) update toupdate;
}}
</pre>
Phillip SouthernPhillip Southern
Awesome!  yea sorry, since it was free hand I'd be shocked if I didn't have any typos!  Thanks for understanding.