Value of a column from a referenced row by a dereferenced value...or something like that

I have (in a simplified version)
-a customers table
-a contact details table , containing one row per customer , referenced to customers table key
-a tasks table , referenced to customers table key

I need a VC in tasks table that shows a column value from contact details table (the phone number)

For now I have a VC in tasks that show the entire row from contact details , using
[customerID].[contact details] , but I need only one VC column as phone column.

I have tried parentheses and different ANYs , but of course it will not work , like

([customerID].[contact details])[phoneno] :))

One way would be to create a VC in customers first , to get phone number, but I am interested in a chained dereference-reference formula , if there is such a tip and trick, without selects/lookups

Thank you

Have you tried this?

[customerID].[contact details].[phoneno]

I think I did and this will work for dereferencind a child to a grandparent value. But a I have two children tables and a parent , not a grandparent-parent-child

So I think I need to reference a dereference ?!

I don’t understand. :confused:

Child Table ContactDetails : CustomerId ; PhoneNo

Parent Table Customers : CustomerId ; Customer Name

Child Table Tasks : CustomerId ; TaskName ; VC for PhoneNo

1 Like

Ah. Thanks. Maybe this?

[customerID].[Related contact details][phoneno]

If that doesn’t work, try this:

SELECT(
  ContactDetails[PhoneNo],
  ([CustomerId] = [_THISROW].[CustomerId])
)
2 Likes

I already tried the first one, unfortunately it doesn’t work , even it makes sense

The second option is an expensive select formula?

Yep.

Then I better do this

Then use [customerID][VC Phone No]

2 Likes