Serial Number

[Andrew Q]

Hi all. I have one question. In selecting serial numbers, are there any difference in having leading zeros such as INV00003 or just INV3? I would prefer the latter as it looks less messy but I would like to get the opinion of FM gurus on this.

[Lee Smith]

Yes there is a difference in how the numbers (text) will sort..

This is the sort order for your numbers. I added one so you can see what I mean.

INV0003

INV12

INV3

Also, you have mixed the capital O with the zero 0, which can really mess you up.

HTH

Lee

[Andrew Q]

Thanks for the reply. What I'm asking is that which route should I take for serials. Example would be:

Type 1:

INV00001

INV00002

INV00003

...so on

OR

Type 2:

INV1

INV2

INV3

INV4

...so on

I would rather choose type 2 but before I design my database, i'm asking whether if I would run into any trouble later down in time using this option for example in sorting or whatsoever.

[Lee Smith]

If I'm understanding your question now, then the answer comes down to what you want to see in print and/or on screen.

INV_1

INV_100

INV_1000

INV_10000

I like this better.

INV_00001

INV_00101

INV_01000

INV_10000

HTH

Lee

[T-Square]

I vote for:

1

2

3

4

5

Add the INV in a calculated field.

Edited October 29, 2006 by Guest

[Lee Smith]

I didn't know we were voting?

Shoot, if I'd known that, then I would have voted to not to include INV in it at all. I would use the lead zeros though, because I like the way they look better.

HTH

Lee

[Andrew Q]

Lol. It is a free country and anyone is allowed to choose what they like. I merely posted this to ask what season developers opted to use.

I agree with Lee that it looks better with the leading zeros but when a user searces for a particular invoice, then they must type in the exact leading zeros if not there would be no results. This of course was with the example of having INV in front as well.

[Lee Smith]

Whoa, type in the numbers?

then they must type in the exact leading zeros if not there would be no results

Why would you need to type in a serial number? The user should not be creating the serial numbers by entering them, Serial numbers should be done using the Auto Enter feature, that is an Option for the fields. Search the Online Help when FileMaker is open for [color:blue] Defining automatic data entry

HTH

Lee

[Fenton]

I guess there is no perfect method. But there are ways to make it work. You can create a "mirror" ID field, if the real one is Text, use GetAsNumber (InvoiceID). Put it below the other on the layout, and make it the only one enterable in Find mode. That way they can just type the number. Neither field should be enterable in Browse mode, except on a "developers" layout.

You don't usually need to sort a serial ID field, in its own table. Unless something is very wrong it will be in creation order, the same as unsorted.

I don't usually use prefixes in ID fields ("INV", etc.).* I think some people have the idea that the database won't know which ID it is unless it has a prefix; which is crazy. It's in the InvoiceID field, no other ID will ever end up there. It's also labeled on the layout.

*There is an exception if you have table used by many others, such as a Notes or Addresses table, and you use a method putting IDs from different parent tables into the same foreign key field. But I usually just create separate ID fields for each parent.

There is also an exception if you're using a prefix to separate different "updates" of the file, and you don't want to restore the "next serial number." I usually just restore it. Or, if it's going to be a big problem, such as in distributed files, you can use a calculation key which is going to be unique, a combo of the partial record id, the date & time, etc., instead of an auto-enter serial ID.

[Andrew Q]

I believe u've misunderstood me again Lee.I suppose it is my fault for not making things clear. My apologies.

What I meant when users could enter in was when they are in find mode to call up a particular INV, first they gotta remember the INV no and thenj how many leading zeros before they are able to get the INV to show up.So it is quite a hasle when u just forget 1 zero.

[Lee Smith]

I almost added a comment about using the find mode, but after reading your question a couple of times, it sounded more like your were entering the numbers, and I didn't see the word "Find" in any of your posts.

Change your field type to "Number," and you should be okay.

Lee

[Andrew Q]

thank you to all the gurus for giving your usefull feedback. Much appreciated.