Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mahmoud Coudsi 1Mahmoud Coudsi 1 

SOQL - query related person's account- update field - most recent date - Inner join

Hello,

I'm new to Apex and SOQl. I have two objects; Accounts (Master object) and Appointments (Child object) and I'm trying to write a trigger (With SOQL query) that returns the most recent appointment date (Max date) per account on a custom field (Most_Recent_Appointment_Date__c) that is built on the account (Persons account) object:
 
Object #1: (Appointments)
 
Appointment ID  Account ID,   Name,  Apt Date,   Weight....etc.
---------------------------------------------------------------------------------
Apts_001112       001                John      01/01/2017     160
Apts_001114       002                Nicole   11/05/2016      180
Apts_001113       001                John      04/05/2017     175
Apts_001115       003                Mark     05/05/2017      190   
Apts_001116       002                Nicole   12/15/2016      200
 
Object #2: (Accounts)
 
 ID,      Name....etc.
--------------------------
 001     John    
 002     Nicole   
 001     John     
 003     Mark      
 002     Nicole
-----------------------------------------------------------------------------------------------------------------------------------------------------
 
Trigger MostRecentAppointmentDate on Accounts (After update, after insert) {

List <Accounts> AccountsList = new List<Accounts>();

Set<id> AcntsID = new Set<id>();
    for(Account a:trigger.new) {
 AcntsID.add(a.ID);
}

For (Appointments Apts : Trigger.new) {
Appointments MostRecentApts = [SELECT Scheduled_date__c, Weight__c, Patient__c
                                            FROM Appointments__c
                                            WHERE Patients__c IN:AcntsID
                                            GROUP BY Patients__c
                                            ORDER BY Scheduled_date__c DESC
                                            LIMIT 1];

Set Most_Recent_Appointment_Date__c = MostRecentApts

}
Best Answer chosen by Mahmoud Coudsi 1
pconpcon
Have you looked at doing this as a roll up summary instead?  Can you choose the max Scheduled_date__c and roll that up to the account?  That would mean you would have to do the code.  If that doesn't work, I think you'll want to instead do a trigger on your appointments object and update the account with that instead.  Doesn't make sense to do this when the account is updated.  Instead do it when a appointment is created or updated.
  
trigger MostRecentAppointmentDate on Appointments__c(after insert, after update) {
    Set<Id> accountIds = new Set<Id>();

    for (Appointments__c appt : trigger.new) {
        accountIds.add(appt.Patients__c);
    }

    accountIds.remove(null);

    if (!accountIds.isEmpty()) {
        List<Account> accountsToUpdate = new List<Account>();
        for (Account account : [
            select Id,
                (
                    select Scheduled_date__c
                    from Patients__r
                    order by Scheduled_date__c desc
                    limit 1
                )
            from Account
            where Id in :accountIds
        ]) {
            // This may need to be datetime depending on your field type
            Date dt = null;

            if (!account.Patients__r.isEmpty()) {
                dt = account.Patients__r.get(0).Scheduled_date__c;
            }

            accountsToUpdate.add(new Account(
                Id = account.Id,
                Most_Recent_Appointment_Date__c = dt
            )); 
        }   

        update accounts;
    }
}
NOTE: This code has not been tested and may contain typographical or logical errors
NOTE: When adding code please use the "Add a code sample" button (icon <>) to increase readability and make it easier to reference.
 

All Answers

pconpcon
Have you looked at doing this as a roll up summary instead?  Can you choose the max Scheduled_date__c and roll that up to the account?  That would mean you would have to do the code.  If that doesn't work, I think you'll want to instead do a trigger on your appointments object and update the account with that instead.  Doesn't make sense to do this when the account is updated.  Instead do it when a appointment is created or updated.
  
trigger MostRecentAppointmentDate on Appointments__c(after insert, after update) {
    Set<Id> accountIds = new Set<Id>();

    for (Appointments__c appt : trigger.new) {
        accountIds.add(appt.Patients__c);
    }

    accountIds.remove(null);

    if (!accountIds.isEmpty()) {
        List<Account> accountsToUpdate = new List<Account>();
        for (Account account : [
            select Id,
                (
                    select Scheduled_date__c
                    from Patients__r
                    order by Scheduled_date__c desc
                    limit 1
                )
            from Account
            where Id in :accountIds
        ]) {
            // This may need to be datetime depending on your field type
            Date dt = null;

            if (!account.Patients__r.isEmpty()) {
                dt = account.Patients__r.get(0).Scheduled_date__c;
            }

            accountsToUpdate.add(new Account(
                Id = account.Id,
                Most_Recent_Appointment_Date__c = dt
            )); 
        }   

        update accounts;
    }
}
NOTE: This code has not been tested and may contain typographical or logical errors
NOTE: When adding code please use the "Add a code sample" button (icon <>) to increase readability and make it easier to reference.
 
This was selected as the best answer
Mahmoud Coudsi 1Mahmoud Coudsi 1
Hi Pcon,

Thanks a lot for helping me out. I just had a little problem with the last line, I keep getting this error message (Variable does not exist: account at line 36 column 16) and I tried sub it with all these different variables:

AccountName
Account__c
Account__r
Account__r.id
Patients_c
Patient_c
 
Any clue what could've went wrong?

Also, my next step is to return the most recent weight value in the Most_Recent_Weight__c custom field which is also built on the account object. Can you give me some guidance on how to do so?
  
Trigger MostRecentAppointmentDate on Appointments__c(after insert, after update) {
    Set<Id> accountIds = new Set<Id>();

    for (Appointments__c appt : trigger.new) {
        accountIds.add(appt.Patients__c);
    }

    accountIds.remove(null);

    if (!accountIds.isEmpty()) {
        List<Account> accountsToUpdate = new List<Account>();
        for (Account account : [
            select Id,
                (
                    select Scheduled_time__c
                    from Dr_Chrono_appointments__r
                    order by Scheduled_time__c desc
                    limit 1
                )
            from Account
            where Id in :accountIds
        ]) {
            // This may need to be datetime depending on your field type
            DateTime dt = null;

            if (!account.Patients__r.isEmpty()) {
                dt = account.Patients__r.get(0).Scheduled_date__c;
            }

            accountsToUpdate.add(new Account(
                Id = account.Id,
                MostRecentDateTrigger__c = dt
            )); 
        }   
        
        Update account;
    }
}


 


 
pconpcon
That is my fault.  It should read
  
update accountsToUpdate;

 
Mahmoud Coudsi 1Mahmoud Coudsi 1
Hi Pcon,

Thanks a lot, it works like charm. I used this code as a template to return the inital weight and latest weight value but I'm getting this error message: 

Compile Error: Method does not exist or incorrect signature: account.Dr_Chrono_appointments__r.get(Integer) at line 58 column 22​

Could you please take a look at it?
  
Trigger GetLatestAndInitalWeightValue on Appointments__c(after insert, after update) {
    Set<Id> accountIds = new Set<Id>();

    for (Appointments__c appt : trigger.new) {
        accountIds.add(appt.Patient__c);
    }

    //Elimnate the the accounts that don't have IDs for
    accountIds.remove(null);

    //SOQL query that returns that latest weight value 
    if (!accountIds.isEmpty()) {
        List<Account> accountsToUpdate = new List<Account>();
        for (Account account : [
            Select Id,
                (
                    Select Scheduled_time__c, weight__c
                    From Dr_Chrono_appointments__r
                    Order by Scheduled_time__c desc
                    Limit 1
                )
            From Account
            Where Id in :accountIds
        ]) {
           
            //Declare a decimal variable to store latest weight value 
            Decimal LW = NULL;
            
            // Get(0) to return the first element in the list value
            if (!account.Dr_Chrono_appointments__r.isEmpty()) {
                LW = account.Dr_Chrono_appointments__r.get(0).weight__c;
            }

            accountsToUpdate.add(new Account(
                Id = account.Id,
                Latest_weight__c = LW
            ));
            }
              //SOQL query that returns that inital weight value 
             
        for (Account account2 : [
            Select Id,
                (
                    Select Scheduled_time__c, weight__c
                    From Dr_Chrono_appointments__r
                    Order by Scheduled_time__c ASC
                    Limit 1
                )
            From Account
            Where Id in :accountIds
        ]) {
           
            //Declare a decimal variable to store inital weight value 
            Decimal IW = NULL;
            
            // Get(0) to return the first element in the list value
            if (!account.Dr_Chrono_appointments__r.isEmpty()) {
                IW = account.Dr_Chrono_appointments__r.get(0).weight__c;
            }

            accountsToUpdate.add(new Account(
                Id = account.Id,
                Inital_Weight__c = IW
            ));
                         
        }   
        
        update accountsToUpdate;
        
        }
      }

Thanks advance!
pconpcon
You may instead have to do 
  
for (Dr_Chrono_appointment__c appt : account.Dr_Chrono_appointments__r) {
    IW = appt.weight__c;
}
This will only run once since you have the limit