You need to setup a relationship between table1 and table 2.
table1::lookupcode ---> table2::lookupcode
Once you have the relationship setup you can create a dropdown/popup on the table1::lookupcode field that will pull from the table2::lookupcode but only display values from table2::value
sorry.. I missed the automatic value..
I would setup this field to do a lookup value from the table2::lookupcode. This way when you choose a code the value will populate automatically.
I want to store the values in table 1 to be sure they will not change due to any calculation.
The "problem" is that the date entered in table 1 can be any date (for example the current date) and the validity date in table 2 is for example 31 december 2013. This means that the value of table 2 is valid until 31 december 2013.
When I have a date in table 1 of 1 january 2014 the lookup has to fail.
I can try to create a lookup value in table 1: LookupCode & Date
In the lookup formula it must be less or equal to table2::LookupValue
But that will only work if the date is converted into a number, before (!) concatenation.
Can you explain how you need this process to work in more detail? It's not clear to me what data should appear in place of <formula> in table 1 when you enter a look up code and date in those fields of table 1.
a relationship can be defined to match more than one pair of fields so you can use both date and lookup code fields as match fields in the same relationship. And you don't have to use the = operator inequality operators such as < or < may also be used to control what values are looked up from Table 2. You can also use a sort order specified for the related table as part of the relationship to control which out of a set of matching related records is the "first" related record. The first related record is the record from which auto-enter field options can copy a value.