6 Replies Latest reply on Feb 17, 2016 3:49 PM by pfroelicher

    private keys


      In our in house MRP system we have "id" for every record. Customers have keys like c_idXXXX and sales orders have keys like pv002002.


      For us it is very good that these numbers are human readable, (no UUID) because we use them to find records


      These private key fields are defined as "number"  (although they contain text sometimes, like pv002002 "pedido de venda 002002")


      When I am in the sales order table PV I define a foreign key field, where the customers id gets written in. These fields I historically defined as text fields.

      Now... this setup gives me trouble when doing SQL queries. There the two fields should be of the same type.

      However to change everything (both sides, the private and the foreign key fields) to text or to number seems risky.


      What will happen if I change this fields now from text to number? Can I do that without risking data integrity?

      What is best practice to define ID fields?

      What is best practice to make this change (change field formats in private keys and foreign keys fields in many tables without loosing info).




        • 1. Re: private keys

          the original sin was to stick text into number fields, so fix that by changing the type to text.


          If you are scared, do it on yesterday's backup and check the functionality.

          ( It will work.)

          • 2. Re: private keys

            +1 to siplus.


            I inherited a dbase suite having the same "sin".  Switched the number key fields to text with no issue (tried on a few backups first, of course).

            • 3. Re: private keys

              Best Practice - Id keys field that are used in relationship should use uuid, which is to verify that the id is unique and the user should not have access to these fields so it does not matter if they are readable or not.   You would create additional fields for user access / searches, but you would not use these fields in relationship. 

              • 4. Re: private keys

                I have made that change before from number to text without any problems.  I don't know if I have gone from text to number, but I can't think of any reason why it would cause problems, hopefully someone else can verify or dispute that statement.


                Depending on how many records you have and the indexing it might take a little time to complete.


                Creating an Id field of a data type of numeric or text is a matter of preference really.  A numeric Id is a little easier to find by specifying the number.  The result will be only the record with that number as an id.  A text field will return results that contain that number unless specifically constrained.  So a search on a text field for 00200 might return 002000 - 002009, on a numeric field the result will be only the record with an id value of 200.


                If it were me, I would have a good backup and then try it out and see what happens.  If it doesn't work, I would restore the backup.  I think once I made the change it would be pretty obvious right away to see if there was an issue with the integrity once I started doing some searches, because I believe in this scenario everything will get screwed up or nothing will.


                Good luck.

                • 5. Re: private keys

                  if you want to sh* brix, create a new database, define a field named key, numeric.


                  Enter browse mode, create a new record, type "A1" in that field.


                  Enter find mode, enter "A" in the key field, perform find. Boo. Brix.


                  Enter define fields, change key to being text, OK, enter find mode, type A, perform find, bingo.

                  • 6. Re: private keys

                    Hi Siplus

                    Yes, I changed all the fields to text now.

                    Had to work a bit because historically all foreign key fields that where number had only number in them.. so a pv00200 had only 200 in it..which worked, but changed to text would have linked to anything containing "200" as you pointed out.

                    In the future I will probably change the system to using UUID, which where not available easily when I programmed this MRP solution in FM8 some years ago.

                    So.. for others.. my finding: IF you do not use UUID which would be the best but adds some overhead in fields and programming, DO stick with the same field type for the private and foreign keys, otherwise you get into problems. If you stick to text on both sides a calc to put in the fk will pull pv00200 into the field..which give a solid match.. If you mix you might end up with only 200 in the fk field which gives you some trouble if you  private key contains somethings like pv200, pv10200 or pv02001.

                    Thanks to all.