2 Replies Latest reply on Jan 22, 2016 12:51 PM by user26142

    Serial # field or not?


      I am just getting started with FMP.  I have two tables, transactions & lots .  When adding a new transaction table  record I have a lot field in the table set to auto-enter serial.  My design calls for automatically creating a corresponding record in the lot table when a new record is created in the transaction table.  I'm playing around with the best way to have FMP do the record creation, but would like advice on whether or not the lot field in the lot table should also be set to auto-enter serial.  Hope this makes sense!


      Thanks, Scott

        • 1. Re: Serial # field or not?

          I believe *every* table should have a PRIMARY KEY (number, serial, text, anything that is UNIQUE to that table). However when relating two tables (even one-to-one relationships) you don't use the PRIMARY KEY = PRIMARY KEY between the two tables. You have a PRIMARY KEY in the first table = FOREIGN KEY in the second table.


          If you need, create another field which is NOT the primary key and you may then use it to match tableOne::myField = tableTwo::myField.


          But the Auto-enter in tableOne can be the PRIMARY KEY and match the FOREIGN KEY in tableTwo (not auto-enter serial).


          HOW TO:

          • Make a trigger to create the record in the second table when the record in the first table is committed (pass the key in the script parameter)
          • or Set a field (based on the relationship) by script that will automatically set the Primary Key (auto enter) and the Foreign Key in tableTwo (if the relationship allows creation of related records).
          • 2. Re: Serial # field or not?



            An analogy for your transaction-log table relationship is company-contacts in a CRM application.. For example company Acme, Inc could have one or more contacts e.g Bill in Accounting, Sam in Vendor Relations and so on. The company IDs are distinct from the contact IDs. Both sets of IDs can (and should) be auto-generated. As Beverly points out, you need to link the two tables on a common field. In my CRM example, that would be Customer ID. You can do this in Relationships in 'Manage Database'  in FileMaker.