0

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.

1

2 Answers 2

0

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;
        }
    }
}
0

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).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .