Lookup for value and use the validity date
I'm new to this forum and to FM Pro and looking for a solution for the following:
I want to enter something in table 1:
Unique_ID1 | LookupCode | Date | Automatic value
00000001 | 01 | 20-10-2013 | <formula>
The formula should pickup a the right value from table 2 (the Unique_Id2 is a combination of LookupCode from table 2 and the ValidTo_date (If I don't concatenate these fields I have no unique field in this table).
Unique_ID2 | LookupCode | ValidTo_date | Value
0120102012 | 01 | 20-10-2012 | NotValidValue
0120102014 | 01 | 20-10-2014 | ValidValue
The formula in table 1 should get the ValidValue from table 2, but how do I define this formula? I am trying to solve this in the table-field definition I don't want to do this for every single layout.
Is it possible with a formula?
Do I have to concatenate the fields LookupCode and ValidTo_date in table 2? Or is that not necessary?