The best thing to do would be to change (at least) your target field's type to Text.
You could also change the Set Field step to Set Field [ YourField ; GetAsText ($yourVariable) ] - but the truth is that "00001" is NOT a number.
Thanks for the response. I'll try implementing your solution and get back to you with additional questions.
I'm not certain whether I understand why 00001 is not a number; should I have started my numbering with 10000 rather than 00001 to keep my record IDs simple?
why 00001 is not a number
Because GetAsNumber ("00001" ) returns 1. Leading zeros are meaningless in a number, and discarded when text is converted to a number.
Ok, I follow the logic. One follow-up question: can a text field, such as the one you're suggesting, but auto-incremented by values of 1, such as in an auto-enter serial number?
Thanks for your insight.
Auto-increment works just as well within a text field. For example, setting a text field to increment "ABC0001" by 1 will generate:
Ahh, I initially was going to implement the increment that you suggested (ABC00001), but I read in several other places in this forum that I should avoid mixing alphanumerics in auto-enter serial values.
How much trouble would I be in if I switched all my current auto-enter serial numbers from 'number' format to 'text' format? I do not summarize by these fields (obviously), so I cannot see immediately how it might negatively influence my database. Any input would be greatly appreciated.
How much trouble would I be in if I switched all my current auto-enter serial numbers from 'number' format to 'text' format?
That's hard to say without knowing everything about your solution. For one thing, text fields sort differently. It is also not obvious to me why you would not summarize by these fields (or why that would make a difference).
My own personal preference is to use pure numbers as the unique ID's of a table (and as matchfields for relationships). If a formatted display is required, you can use a calculation field (with a Text result) =
SerialIncrement ( "YourPrefix" ; Serial ID )
But it is only a preference and there are exceptions: for instance, if data is coming in from various sources, a prefixed serial number can be useful.
I use the auto-enter serial numbers as unique IDs in each table (n=20) in my database. I cannot see why I would summarize by these IDs, other than a total number of records in a search. Can you tell me how else I might use them (without knowing everything about my database, as you said in your last reply)?
I'm curious, how would a text field with values such as 00001, etc. be differently sorted than a number field with values 00001, etc.? I know Windows Explorer doesn't how to sort files whose names include numbers, but does FM have similar problems?
If your primary keys are of type Number, then so should be your foreign keys (and vice versa). I often use a foreign key as the breakfield in a summary - but again, I don't see why the type should make a difference here.
A text field with values "00001", "00002", etc., will be sorted in the same order as a number field with values 1, 2, etc. - up to "99999". After that there will be a difference, since text is sorted alphabetically ( "12" < "9" ).
I use the my "ID" fields as my primary keys, and at this point, all are in number format. I'll probably leave them in this format because the sorting problem associated with numbers when identified as text will not work for my database solution.
I wanted to return to my initial issue now that we've largely coverted the 'why' part of the format problem. My issue was that I cannot consistently have my values (e.g., 00001, etc.) returned in the same 'format' between different tables. That is, on one table, the value will display as 00001, and when copied from one table into another (e.g., Table1::'Set Field' -> Table 2::'Set Variable'), the format changes (00001 -> 1). Should I include the functions you initially suggested in an earlier comment? If you have any insights into why this might be happening, I would love to hear it!
Thanks for all your help,
I am afraid you may still be missing the main point here:
Storing a TEXT value such as "ABC123" or "00001" in a Number field is asking for trouble. Filemaker is pretty tolerant when it comes to data types, so it will not object or modify the stored value. However, it WILL treat the entry as a number - usually, when you expect it the least.
A few examples:
• The entry "ABC123" will match "XY123" and "00123" in a relationship;
• Max ( Related::Field ) will return 11, when the entries are "A011" and "Z009";
• SetField [ Target ; Source ] will set Target (Number) to 123, when Source (also a Number) contains "ABC123".