How to update opportunity field with date from a related sObject?

Question

I'm trying to update a field with a date on some opportunity records. This date should come from the opportunity's first appointment date.

The appointment sObject has a lookup field to opportunity. An opportunity can have many appointments.

I have a parent-child query that gets all opportunities that have related appointment records. I then sort those related appointment records by date in ascending order.

I need to grab the first appointment's date after the sort and set the opportunity's first_appointment__c field to the date of its first appointment (Appointment__c.date_of_appointment__c).

I don't have any compile errors but when I run the below code in the dev console, it executes and I get a debug log but none of my records have changed.

Here is the code:

public class UpdateOppFirstAppt {
    
    public static void updateOppFirstAppt() {
        // get 100 opps (and their related appts) where first appointment field is null and store in list
        List<Opportunity> oppList = [SELECT Id, First_Appointment__c, (SELECT Id, Date_of_Appointment__c 
                                                                       FROM Appointments__r 
                                                                       WHERE Date_of_Appointment__c != Null 
                                                                       ORDER BY Date_of_Appointment__c ASC)
                                     FROM Opportunity
                                     WHERE First_Appointment__c = Null
                                     LIMIT 100];
        
        // for each opp in the list, set the first appt field with its related first appt date
        if (!oppList.isEmpty()) {
            for (Opportunity opp : oppList) {
                for (Appointment__c appt : opp.Appointments__r) {
                    opp.First_Appointment__c = appt.Date_of_Appointment__c;
                }
            }
        }
        
        // update the opps in the list
        if (!oppList.isEmpty()) {
            update oppList;
        }
    }
}

I suspect I need an index of some sort to grab the first appointment maybe? Something like this within the for loop:

opp.First_Appointment__c = appt.Date_of_Appointment__c[0];

But I get a 'Expression must be a list type: Datetime' error when trying to do that.

Some opportunity records have more than 1 appointment in their appointment related list so I need to figure out how to just grab the first one and update the opportunity field with that first appointment's date.

Answer 1

Since you want to assign Date_of_Appointment__c from the first record after sorting, you should break out for loop on Appointments__r after first assignment as shown below:

// for each opp in the list, set the first appt field with its related first appt date
if (!oppList.isEmpty()) {
    for (Opportunity opp : oppList) {
        for (Appointment__c appt : opp.Appointments__r) {
            opp.First_Appointment__c = appt.Date_of_Appointment__c;
            // break out the for loop after the first assignment
            break;
        }
    }
}

Answer 2

You will want to use an aggregate query to avoid loading unnecessary detail. Perhaps something like:

SELECT Opportunity__c opp, MIN(Date_of_Appointment__c) earliestDate FROM Appointment__c WHERE Opportunity__c IN :opportunities GROUP BY Opportunity__c

I have assumed you would have some array of the Opportunities or Opportunity IDs already queried.

As I mentioned in the flow version of this question, you probably want to do this from the context of inserting, updating (date or opportunity) and deleting Appointment__c records; now you are looking at Apex, use the Appointment__c trigger for this.

The reason I suggest this is that the first appointment date could change as appointments are added, removed or updated.

I would collect the Opportunity IDs for which the aggregate query is to be performed from the set of Appointment__c records passed to the after insert/update/delete trigger execution (including the original opportunity and the new opportunity in the case of an update to the Opportunity lookup field, if such an update is permitted).