3 Replies Latest reply on Jul 29, 2015 2:54 PM by philmodjunk

    Non serial number primary key



      Non serial number primary key


      Version: Filemaker pro 12

      I would like to use auto fill in drop down value lists in my database, but I see that this is not possible if I use an auto enter serial number and then use "show second value". I have reviewed the work arounds provided by PhilModJunk (thanks a lot by the way), which work well. However, I wonder if there is any reason to not remove the auto enter serial number primary key and simply use the values associated with them. The values I want to use are state names and codes and country names and codes. For example:

      With serial number:

      #     Name        Code

      1     Virginia      VA

      2     California   CA

      Without serial number:

      Name         Code

      Virginia        VA

      California     CA

        • 1. Re: Non serial number primary key

          This is a "rule" to be broken with great caution. The ideal primary key is:

          a) Unique

          b) never ever changed once assigned

          c) implemented in as simple and "bullet proof" a fashion as possible.

          The names of states are certainly unique. And it's very unlikely that a state would change it's name (though I suppose not impossible.)

          BUT, there's another scenario that comes into play that results in having to change primary key values when you use a "name" value: Correcting data entry errors. If a name get's misspelled during data entry and you do not discover the error right away, then you are back in the unwelcome situation of having to update first the foreign key fields that link to your primary key and then to update the primary key without "breaking" the connection between parent and child records.

          So with a table listing 50 states by name, there is a small chance that you could mis-enter the name of a state in that table. But the chance of this seems small enough that I would not have a problem using names in such a table--though some might well disagree.

          So yes, this works, but look before you leap. ;-)

          • 2. Re: Non serial number primary key

            Thanks again for the help. It seems like a fairly safe move with states, but less with countries. I was hoping to reduce the amount of work I needed to do in setting up the relationships for these tables.

            Am I wrong to think that the inability to auto fill value lists from the second value is an important design flaw in Filemaker Pro?

            • 3. Re: Non serial number primary key

              I've long considered it such and it was one of many Feature Requests that I've posted to FileMaker Inc's Feature Request Form.