Hello to you all,
I wonder if it is possible to link two tables using the value of a field for the current record.
Let me explain:
Table: ta (id, name1, phone1, name2, phone2)
Table: tb (id, name, phone)
In ta
name1(int) is a lookup field (parent is TableB)
phone1(int) is a lookup field (auto-fill with parent TableB linked to name1)
name2(int) is a lookup field (parent is TableB)
phone2(???) is ???
Problem with phone2
The use of auto-fill will display the value of phone1 since lookup feature is linked to the record and not to a particular field
I was wondering if the new calculated field in 5.81 could be used to get the value of tb.phone2 could be retrieved with a formula.
Inspired by Mr ronwill and Mr a.gneady; I came up with the following:
ta.phone2 : varcar(40) with calculated field:
SELECT `tb`.`phone`
FROM `ta`
LEFT JOIN `tb` ON `tb`.`id`=`ta`.`name2`
WHERE `tb`.`id`= ??? the value of name2 for the CURRENT RECORD ???
Usually we use '%ID%' to specifiy the id value of the current record. But now, I want to use the value of name2 (which is an integer from lookup) instead.
Is there a way to do this using a calculated field?
Regards,
RP