3 Replies Latest reply on Aug 19, 2014 8:09 AM by philmodjunk

    Can you link to a primary table using a field rather than the primary key?

    terrypink

      Title

      Can you link to a primary table using a field rather than the primary key?

      Post

           I have a primary table with a primary key and then a more descriptive field name.  Can I link foreign tables' foreign keys to this primary table using its more descriptive field rather than the standard primary key?

        • 1. Re: Can you link to a primary table using a field rather than the primary key?
          philmodjunk

               That would seem to defeat the purpose of using a primary key. But why is the field name significant rather than the data stored in it? You can always rename your primary key to be more descriptive--either in Manage | Database | Fields or by editing the layout text on your layout that labels the field.

          • 2. Re: Can you link to a primary table using a field rather than the primary key?
            terrypink

                 Because I thought the primary key wasn't supposed to be descriptive?  And also, this field value that is descriptive only occurs for some of the records in this table, not all of them?  It is okay to do this (meaning, connect the foreign tables to this descriptive field)?

            • 3. Re: Can you link to a primary table using a field rather than the primary key?
              philmodjunk

                   But in your original post, you described using a "More descriptive field NAME" not a more descriptive field VALUE.

                   Your very description of the data illustrates why you should use a primary key and not a name field.

                   The ideal primary key is

                     
              1.           Unique
              2.      
              3.           Never ever changed after it is assigned to a new record
              4.      
              5.           devoid of any and all "encoded/added" meaning beyond that unique identification
              6.      
              7.           Implemented in as simple and "bullet proof" manner as possible.


                   A field of names or descriptions doesn't meet that criteria and thus problems are typically encountered that threaten the integrity of your data if you use them in place of a primary key.

                   But some times you can use a relationship that links by name for the purpose of finding records--typically to link them by primary key. This is done with a an added occurrence of the table where the primary key is defined. The key question to answer when setting up such an addition to your design is "What will happen if a user has to change the text in this name/description field?

                   Here is an example of such a method where a name is used in a new record to look up the primary key:

                   FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7