Get value from (indirectly) related table
I still encounter a problem that I previously tried to describe in a previous post.
I seem to have made the description way to complicated so I'd like to put it in a new post in a very simple way:
Given three tables (Main, table 1 and table 2) with the following fields and relations:
Main_Table: UID_main, main_info, copied_info (calculated textfield)
Table_1: UID_table1, relation_mainTable, relation_Table2 (ALL three fields are required to be unique!)
Table_2: UID_table2, further_info
The relations are as follows:
Main_Table is linked to Table_1 via (UID_main = relation_mainTable) and
Table_2 is linked to Table_1 via (UID_table2 = relation_relationTable2)
=> In other words: Main_Table and Table_2 are not directly linked but indirectly via Table_1
Note: via definition of fields in table_2 there will always be only one single unique match between Main_Table and Table_2.
How do I copy the value from Table_2::further_info to Main_Table::copied_info via some calculation (not via script but via field-internal calculation)?
I would like "Main_Table::copied_info" to be a text field with a calculated vaule. But that doesn't seem to work, whatever I do. I tried the following calculations:
GetField (Table_2::further_info; "")
Lookup ( Table_2::further_info )
Any suggestions what I might be doing wrong and how I can copy the value?