4 Replies Latest reply on Sep 7, 2016 4:40 PM by philmodjunk

    Adding relationship


      I've run a Filemaker solution for years that has multiple tables related by one field (Asset Number). Thus, all of the data from the various related tables connected by this one field. The customer wishes to change to having Serial Numbers (a field we've gathered for years) as the key relationship. However customers will still submit their data based off Asset Number.


      So my question is can I safely have two fields with relationships, and do so without destroying/compromising the relationships already in place. In a nutshell, we need to allow data to be related by both Asset Number or Serial Number.

        • 1. Re: Adding relationship
          Johan Hedman

          You can always have to field in all your relationships, but I would convert to use Serial Numbers or UUID for each record as the relationship key

          • 2. Re: Adding relationship
            David Moyer


            I don't think that there's anything wrong with that for display purposes (utilizing old primary key and new primary key).  However, you should consider the Asset Number as data.  You should also pore over your scripts and update any usage of relationships based on Asset to your new relationships based on the serial number.

            1 of 1 people found this helpful
            • 3. Re: Adding relationship

              One issue you will have is getting the new primary key field correctly posted into any contexts where it is used as a foreign key. you can do this by adding it via the existing relationship using a looping script.

              • 4. Re: Adding relationship

                The customer wishes to change to having Serial Numbers (a field we've gathered for years) as the key relationship.


                With all due respect, that's like asking the school kids to drive the school bus instead of the driver. A relationship is the "under the hood" part of the database that the customer should never see anyway. You can always use a supplementary field such as the serial number field for searching and sorting out records--the visible function of the database without changing the structure of your database to match by a new field and value. You can always hide the asset number from view while continuing to use it to link records in relationships.


                The end result can be that your system appears to link data by serial number when the asset number continues to be the real means of linking data between your various tables.