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.)
+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).
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.
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.
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.
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.