How to batch update an opportunity field with a field from a related object?

Question

I have an Opportunity standard object with an Appointment (sObject) related list. An opportunity can have many appointments.

My requirement is to update all of our Opportunities' First_Appointment__c field with the Appointment's Date_of_Appointment field, but since an opp can have many appts, I only want to grab the opp's first appt.

An opp's first appt can be found by filtering for the the appt's Date_of_Appointment__c field.

I believe I can accomplish this with a schedule-triggered flow but have been having trouble doing so. Here are the objects and fields I'm working with:

Appointment

• Date_of_Appointment__c (date)
• Opportunity__c (lookup to opportunity)

Opportunity

• First_Appointment__c (date)

Here is the logic I'm trying to use:

Nothing special to start. I did not choose an object to attach.

Next, I have a Get Records element that grabs Appointments where Appointment.Opportunity__c equals the ApptId variable that I created (variable screenshot also below).

And here is a screenshot of the variable. It's just a Text variable to store the two Appointment field values above (Date_of_appointment__c & Opportunity__c).

Lastly, here is the Update Records element called Update Opp First Appt. It first looks for Opportunities that equal {!Get_Opp_First_Appt.Opportunity__r.Id} then is supposed to set Opportunity.First_Appointment__c to {!Get_Opp_First_Appt.Date_of_Appointment__c}

Unfortunately the flow didn't update a single record. I'm new to flows and cannot figure out why. I set the time to run in the future and can see the flow in my Scheduled Jobs. But when the time comes, nothing happens. I know it's a flaw in my logic, something is missing.

Answer 1

As mentioned by @cropredy you'll want to sort the Appointments by their date, in ascending order, to make sure you get the Appointment with the earliest Date_of_Appointment__c. This is done as part of the Get Records, like:

(Obviously you would be sorting Appointments, not Opportunities, but I don't have that object on my org.)

I'm not sure why you would do this with a scheduled flow. If I were doing this I would use record triggered flows that handle creation of Appointments, update of Appointments (where the Opportunity changes or the Date of Appointment changes) and deletion of Appointments, using the "Actions and Related Records" optimization. After all, you only need to calculate the Opportunity's earliest appointment date when appointments are added, updated or removed.

Since you will want to do the same processing for both the create/update and delete, you might consider a sub-flow to do the querying of the Appointment and the update of the Opportunity.