12 Replies Latest reply on Oct 22, 2016 1:21 AM by Markus Schneider

    What is the most often selected field used for the Primary Key?

    tbs

      When creating an online form in FMP which includes Company Addresses from many different related files. Which is the most common field chosen to relate the files to each other:

       

      Account Number?

      Phone Number?

      Company Name?

      A Custom field which combines data from many fields in the address?

       

      I am trying to design the most flexible yet consistent approach for expedient data entry that will be most effective for the data entry person without bulking up the related files with redundant data created by continuous failed attempts to find a related record.

       

      My initial thought is the Phone Number field as it remains somewhat unique and more easily identifiable by those familiar with a record. However, the consensus of a data entry staff is the Company Name field. Knowing the probability of errors when entering into a Company Name primary key field, I am hesitant to chose Company Name unless it can be configured to very tight specifications. IE Maximum number of characters, All Uppercase, etc.

       

      Looking for the best recommendation and a solid justification.

       

      Thanks for any feedback

        • 1. Re: What is the most often selected field used for the Primary Key?
          philmodjunk

          The best choice is none of the above.

          Make it simply one of the following two options:

           

          A number field that auto-enters a serial number

          A text field that auto-enters a calculation: Get ( UUID ) and that has "unique values" specified as a validation rule.

           

          Primary keys should ideally be:

          1. Unique and never, ever changed once assigned
          2. Devoid of any "encoded meaning" other than that it's unique
          3. Implemented in as simple and "bullet proof" a fashion as possible

           

          Trying to produce a primary key from any of the options that you suggest violate all three requirements

          • 2. Re: What is the most often selected field used for the Primary Key?
            beverly

            while the fields you listed may be used at one time or another to "match" something, they would not be the "primary key" as they could break the rule of being unique. In addition, user entry errors can make these kinds of matches "fail", so also not a good reason to be the "primary" field to link between tables of records.

             

            If you are trying to find duplicate entries, a phone number might be a good match. But keep in mind that entities can have more than one phone number, so even that may not be a match, or the data entry may be slightly formatted differently to make matches fail:

            (555) 123-4567 does NOT match 555-123-4567, even though the digits are the same, for example

            beverly

            • 3. Re: What is the most often selected field used for the Primary Key?
              philmodjunk

              A problem that I've seen in a contact manager that I set up for a client is that phone numbers aren't as unique as you'd expect.

               

              This is less the case today than in the past as we get to take our phone numbers with us more often, but when contact A, stops using a phone number, they don't tell you this. And then Contact B gets a new phone number and it's the one Contact A was using and that is still in your contacts table as the number for Contact A...

              • 4. Re: What is the most often selected field used for the Primary Key?
                tbs

                Thank you for addressing the Primary Key considerations. I understand your logic but I am having a hard time applying it to my challenge.

                 

                My Data Entry Team wants to be able to enter into a Primary Key field based upon some data they can view inside a client request form. Example: they will see a Company Name, Phone Number, Address, etc. This data may or may not be available inside a related database. The challenge is for the Data Entry Person to key in the least amount of data into a primary key field that will allow the remaining fields to populate without having to key in all of the information.

                 

                So they must be able to use the data in some field they are viewing to execute the relationship. If they use a Phone Number and it populates data which is contrary to the request they are viewing, then they will need to re enter all the data for that record. However, if the phone number of the related record matches the data on the request form. Then they completed the process by keying in a 10 digit number.

                 

                So I am forced to use known data to act as a source for the relationship. With all that said, do you have any guidance for me?

                 

                Thank You

                 

                Peter

                • 5. Re: What is the most often selected field used for the Primary Key?
                  tbs

                  Thanks Beverly,

                   

                  I do use a filter to insure that all 10 digits of an entered phone number are formatted the same. This usually eliminates this error. But I understand that phone numbers can easily be reassigned leaving a vulnerability to expecting the data related to the phone number to be consistent. This may violate the Primary Key structure, but it is the only data I can find that will allow the related match to have the greatest chance of success.

                   

                  I'm all ears if you have some advice.

                  Thanks

                   

                  Peter

                  • 6. Re: What is the most often selected field used for the Primary Key?
                    philmodjunk
                    My Data Entry Team wants to be able to enter into a Primary Key field based upon some data they can view inside a client request form.

                    No

                    Absolutely not!

                    Never, ever allow a user to manually edit a Primary Key. Trust me, this is really prone to causing developer headaches and corresponding user headaches.

                     

                    But don't get hung up on the primary key label here.

                    You need a primary key, but your data entry people don't need to even see it, let alone edit it.

                     

                    You can search on any field in your table so you can set up, say a phone number search to find all records with a given phone number. If there's only one match, copy over that record's primary key to link the new child record to that parent. If the script get's multiple matches, have it present a list with additional info to assist the user in picking the correct one.

                    • 7. Re: What is the most often selected field used for the Primary Key?
                      philmodjunk

                      What you are actually describing, by the way is the foreign key field that links to the primary key field of a parent record. This field also should never, ever be directly editable by a user as it leads to errors that mismatch records. As I described, they don't need to do so to get the job done.

                       

                      You can even set up concatenated fields for search purposes, just don't use them to directly link records in relationships. Use a real primary key for that.

                      • 8. Re: What is the most often selected field used for the Primary Key?
                        tbs

                        Thanks Phil,

                         

                        Was hoping to find an easy solution. Just trying to avoid repetitive data entry into files which already contain the data needed. The idea was to have the Data Entry Person key in the Phone Number and have the relationship bring forward the rest of the data needed to complete the Customer Name and Address.

                         

                        Any examples of this data entry strategy would be helpful

                         

                        Peter

                        • 9. Re: What is the most often selected field used for the Primary Key?
                          beverly

                          yes, that's why I say they may be used as a 'match' and show a "picklist" for the real primary ID to be selected and pushed into the record, thus pulling all the correct (consistent) data as needed.

                          I use that method for name, address, city, phone and give them a choice to 'near matches', but they must select. otherwise you are apt to get 'duplicates' with different data.

                          beverly

                          • 10. Re: What is the most often selected field used for the Primary Key?
                            philmodjunk

                            What you describe isn't all that different. You just need a script to handle the cases when the value entered either doesn't match to anything or matches to more than one record. Address info is notorious for being "messy". Any method used will not be perfect. You'll likely need secondary methods of searching for when your first method fails.

                             

                            Take a look at this demo file. The script that is part of the "Auto-Complete Value Lists II" example is one working example of how a script trigger driven script can process search information to find and copy over a Primary Key value while handling circumstances where there is more than one matching record or multiple matching records.

                             

                            Adventures in FileMaking #2-enhanced value selection

                            • 11. Re: What is the most often selected field used for the Primary Key?
                              coherentkris

                              I concur with the precautions mentioned herein.

                              If you give users the ability to edit primary key values and foreign key values they will break the system and blame you.

                              • 12. Re: What is the most often selected field used for the Primary Key?
                                Markus Schneider

                                leave that team...

                                 

                                sooner or later, You are running into troubles when letting somebody/something alter the ID - and the first ones to complain are those team-members...

                                 

                                There is absolutely no reason to alter an ID. If You need a 'handle' that looks like a member-ID, You could add that field just like another attribute..