3 Replies Latest reply on Apr 30, 2013 4:33 PM by philmodjunk

    Multi Key Field - "lookup" value?

    UhruSan

      Title

      Multi Key Field - "lookup" value?

      Post

           I am trying to setup a relationship between two tables, where the field of table 1 holds a string composed of substrings ("aa ab ac ad"). Table 2 has keys for each of those substrings ("aa" = "123", "ab" = "456", "ac" = "789", "ad" = "098").

           As I am rather a newbie to FM, I am not sure how to accomplish this. I searched the forum and tried to define the field of table 1 as "lookup" field, but with no success.

           Any help is greatly appreciated!

           best regards,

           Uhru 

        • 1. Re: Multi Key Field - "lookup" value?
          philmodjunk

               Can you explain in more detail what you are trying to do?

               From here, it looks like you have multiple values in the same field of a single record that should be in separate records of a join table to facilitate a many to many relationships.

          • 2. Re: Multi Key Field - "lookup" value?
            UhruSan

                 Hi Phil!

                 Thanks for your answer!

                 Yes, I have multiple values in one field of table 1 (ex.: "aa ab ac ad") and I have a table 2 with key/value pairs  ("aa" = "123", "ab" = "456", "ac" = "789", "ad" = "098"), where keys and values are in separate columns.

                 What I want to do is create a portal view in one of my layouts, where the values contained in table 1 get the key/value pairs from table 2 and are shown as key value pairs:

                 aa   |    123

                 ad   |    098

                  

                  

            • 3. Re: Multi Key Field - "lookup" value?
              philmodjunk

                   Then it appears that you need to rethink your data model.

                   Table1 --<ValuesTable>-- Table2

                   Table1::__pkTable1ID = ValuesTable::_fkTable1ID
                   ValuesTable::Value = Table2::Value

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   In place of your field with many values, you create records in the Values table, one record for "aa", one record for "ad" and so forth. In table 2, you have one record for each pair of value aind the associated number.

                   You can then set up a portal to ValuesTable on your layout and include a field from Table2 to show the number that goes with each value.

                   This is essentially how you set up an invoicing layout where you use a table to a related lineitems table to list the items sold on the invoice and links to a third table look up the unit price for each item.