2 Replies Latest reply on Mar 30, 2012 11:30 AM by Embee

    Choosing field types for key fields?



      Choosing field types for key fields?


      Hi fmp's,

      In trying to adopt some best practices while learning fm, I am wondering what field types should be used for PrimaryKeyFields and ForeignKeyFields.

      My PrimaryKeyFields are always going to be "auto-enter serial, can't modify auto", but since both Text and Number are available field types for both the pk's and the fk's; which type is the "better practice" in each key? Or what are the considerations?


      - Emmbee

        • 1. Re: Choosing field types for key fields?

          Developer's are known to argue over this one. The most important "best practice" is to keep the types the same--either both text or both number.

          I prefer number types for number serial numbers. It's often the case that you may want to sort your records by a key field and numbers in a text fields won't sort correctly. (1 , 10 , 100, 2, 20, 200 is correct ascending order for numbers in a text field).

          Usually, the reason a developer wants to use text types  for key fields is to provide some additional 'meaning' added formatting to the number such as letters or leading zeroes. I choose to add any such additional details with a separate calculation field and keep the keys as pure numbers. I can display such a field where needed on layouts and users can use them for performing finds and sorts, but my relationships remain simple number based relationships.

          Any time you add such 'extras' to the value of a key, you open the door at least a tiny bit to a case where you might need to change these details in the future. (Your serial numbers become larger than the specified number of leading zeroes permits, the powers that be decree that "we now want these numbers to start with "A" instead of "C" and so forth....) If that changes the actual value used to match records in a relationship, updating your fields can be tricky and take quite a bit of time to do. If this only changes the value returned by a separate calculation field, you just make the changes there and all your records stay properly linked in relationships.

          • 2. Re: Choosing field types for key fields?

            Thanks a lot, Phil. That was exactly the kind of answer I was looking for. And I haven't been able to find this kind of pro/con evaluation anywhere - so thanks again.

            - Emmbee