AnsweredAssumed Answered

Serial "numbers", leading zeros and best practices?

Question asked by Cécile on Aug 14, 2017
Latest reply on Aug 15, 2017 by philmodjunk

We have an old database that I am cleaning up and two of the fields I want to clean up are the FiscalYear (text) and FileNumber (number). In correspondence, these two fields are combined FullFileReferenceNumber(text) and it looks like: GC16/17-0009.

Since 2004, the leading zeros in the number field have been achieved by setting the field at the beginning of each fiscal year like this:  Auto-enter, Serial, next value: 0001 increment by 1. Strict numeric value only is NOT selected.

Records created for the preceding years back to 1990 do not have the leading zeros and the FullFileReferenceNumber looks accordingly ugly.


Because I have found extremely difficult to do ad hoc searches excluding a certain amount of fiscal years without having to type the entire text entry for each (omit 02/03 and 03/04 and so on) and because when exporting or naming files automatically it does not sort properly (years 97/98 comes after 00/01 instead of before) I want to convert the FiscalYear field to a numeric with 4 decimal. So 97/98 will become 19.9798 and 00/01 will be 20.0001.

Question 1: please chime in if this strategy may yield unforeseeable (for me anyway!) problems in the future.


Let’s address now the FileNumber.

I have read several threads about leading zeros and there seems to be several ways to go about it but it seems to be important to determine whether the field must be a number or a text.


The schema will use the figure stored in the field either for its numeral (the ordinal propriety of the number e.g. alphanumerical) or numeric value.

In the case of FileNumber I suppose that it is more the rank the number represent than its mathematical value. The way file directories and computing systems read file numbers gives importance to leading zeros, another indication that it is the alphanumerics that matter in sorting files names.



If leading zeros are important, it is not "number" in database term. You should use text field instead of number field.



If your original number field should have leading zeros, perhaps you should make it a text field and have an Auto-Enter to change its format like:

                Right("000000" & GetAsNumber(Self);6)

Question 2: Ok according to that FileNumber should be changed to a text field. But setting the field type to text makes the options button disabled along with the auto-enter serial with it. How do I create my serial then?  Just realized that the button is greyed when you first change the field type but it is active again when the change is saved... Doh...



a) There are ways to assign unique serial numbers "on demand" rather than when the record is created.

b) Calculation fields can display the contents of a different number field with leading zeroes added using this calculation:

                Right ( "0000" & NumberFieldHere ; 5 )

So there are two alternatives you might consider.

I’m thinking “when it’s not broken, don’t fix it”, right now. Yet I want to learn and use best practices so what should be done here?


Question 3: With respect to the data that is already in the field, how to I fix the old file numbers that do not have leading zeros? Ok I made a loop and Set field to Right ( "0000" & Files::FileNumber ; 4 ). All pwetty now.