I have 3 Tables. Customer, Appointments, Photos.
Customers have Appointments.
Both Customers and Appointments can have Photos.
I was struggling to update a Customer record with it’s latest Appointment data. Namely the Date, Time, Time Slot, and Charge for the Appointment.
I found topic 14257 and used this as a solution. The Customer table has a related Appointments Column. I can use trigger an ADD_and_UDPATES Action on Appointments_Form which triggers an Action on the Customer table for the Customer row to update its data from its most recent appointment…
The Date - MAX([Related Appointments][Date])
And the Time, Time Slot, Charge with the expression
LOOKUP( MAX( SELECT( [Related Appointments][_ROWNUMBER], ([Date] = MAX([Related Appointments][Date])) ) ), “Appointments”, “_ROWNUMBER”, “Time” )
Now this works, and i’m very happy with the result but it feels a little wrong doing 3 lookups on the Appointments table when all the data I need is already in the Related Appointments column in the Customer Table.
Is there a cleaner way of doing this?