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.
Child Table ContactDetails : CustomerId ; PhoneNo
Parent Table Customers : CustomerId ; Customer Name
Child Table Tasks : CustomerId ; TaskName ; VC for PhoneNo
Ah. Thanks. Maybe this?
[customerID].[Related contact details][phoneno]
If that doesnโt work, try this:
SELECT(
ContactDetails[PhoneNo],
([CustomerId] = [_THISROW].[CustomerId])
)
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]
User | Count |
---|---|
44 | |
29 | |
22 | |
20 | |
14 |