Correctly Update Parent data with Child Record/Rel...

Tristan1 · 09-22-2021 09:21 AM

Hi,

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?

Many thank

Tris

Bellave_Jayaram

Another option may be something like this:

ANY(SORT(SELECT(Appointments[Time], [Customer]=[_THISROW].[Customer]), TRUE))

Marc_Dillon

You mean this right?

Yes and no.

Since it sounds like you’re using real columns (not Virtual), and triggering the recalculation only when it is needed, I wouldn’t really worry about using lots of LOOKUP()s/SELECT()s, as it won’t hamper performance too much. Nevertheless, here is another option for you to consider.

Add an additional column to your Table, of Ref type, pointing to the child Table. From your Bot, update this column’s value with:

ANY( SELECT(
    [Related Appointments][#key-column-here#],
    [Date] = MAX([Related Appointments][Date])
)   )

Then you can use this column to do simple “dereferences” for all of the other values from that record.

[new-column].[Time]

[new-column].[Charge]

etc…

Correctly Update Parent data with Child Record/Related Data