4 Replies Latest reply on Nov 13, 2013 7:33 PM by philmodjunk

    Lookup for value and use the validity date



      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?

        • 1. Re: Lookup for value and use the validity date

               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



          • 2. Re: Lookup for value and use the validity date

                 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.

            • 3. Re: Lookup for value and use the validity date

                   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.

              • 4. Re: Lookup for value and use the validity date

                     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.