Update Table with Two References

Tristan1 · 09-22-2021 09:29 AM

Hi,

I have 3 Tables. Customers, Appointments, Photos.
Customers have related Appointments.
Both Customers and Appointments can have related Photos.

When i save a photo to a Customer record, that photo has its CustID column populated and the AppointmentID column remains blank. This is just fine.

But when I save a photo to an Appointment record I can’t find a way to auto-populate the Photo’s CustID too. There will always be a Parent->Child relationship between a Customer->Appointment so if a photo belongs to an Appointment it will indirectly belong to a Customer also.

Feel like i’m missing a simple setting to maintain this relationship between the tables but am happy if formulas are needed. I imagine if a formula was needed then i would have it to retrieve a Customer ID only if the photo was being saved for an Appointment and to skip the formula if it was being saved for a Customer.

Thank you

Tris

Marc_Dillon

[CustID] doesn’t need a expression when the Photo is created from a Customer record, because that’s how the system works when starting a Form for a new child record from a parent, it automatically auto- fills the parent’s key value. The same way it auto-fills the Appointment parent’s key value when you create a Photo from an Appointment.

You should add an Initial Value expression to the [CustID] column, using a “dereference” expression with the [appointmentID] (or whatever your Ref column to Appointments is).

Tristan1

Awesome! Thank you, so simple I apologise for not figuring that out.

For CustID (TYPE ref) in the Photos table the INITIAL VALUE = [AppointmentID].[CustID]

I am curious as to how to correctly handle the solution if another table was introduced in the same hierarchy as Appointments which would also always have a CustID.

Perhaps a simple If statement?

Taking the example from the IF() documentation page something like:

IF(ISNOTBLANK([AppointmentID].[CustID]), [AppointmentID].[CustID], [SomeotherID].[CustID])

Thanks again