Sequential Order No.

[Ricardo]

hi All,

i am currently creating an Order Tracking System.

i need to create a UNIQUE 6 digit order number for each new order.

so a new record will be created and an Order number entered.

The order number cannot be duplicated.

now i know there are several ways to do this (e.g. using the middle and position functions)but just wanted to see which is the best and different ways?

Thanks in Advance,

Ricardo

[EddyB]

The easiest way is to set the field as a serial number.

In define fields do the following:

Create the new field

Click on options

In Auto-Enter click on the AutoEnter checkbox

Enter the value which the first serial number is going to be

Enter how much this should be incremented by for each record.

Then click prohibit modification of value

This will do all that you asked.

Ed.

[danjacoby]

I can't remember whether it was LiveOak or someone else who first mentioned the following idea (or something very close to it):

CreationDate -- auto-enters the creation date, and is unmodifyable

CreationTime -- auto-enters the creation time, and is unmodifyable

SerialNumber = Status(CurrentRecordID) & " " & DateToText(CreationDate) & " " & TimeToText(CreationTime)

This way, even if you import records (and if you do, then make sure you check the "Perform auto-enter functions" box), you'll still get unique SerialNumber entries for each record.

[cjaeger]

Hi Dan,

that's putting it too short ...

FileMaker does import more than 1 record per second. LiveOak & the other guys include a status(currentrecordID) and/or a random() number to make sure...

next: the goal in those threads was to make a record ID unique and persistent even across imports - so do NOT check the "Perform auto-enter functions" box if you want to keep that ID.

[Ricardo]

but what i need is a Order Number which will be visible to the end user and it needs to be 6 characters.

i want the Order numbers to be along the lines of A00001, A00002 .....

i have a calculation but wondered if there are any others which are better.

thanks for your input so far.

[cjaeger]

Hi Ric,

in most cases a "A" & Right("00000" & serial#,5) will do. However, you should have a look at those threads just to make sure it applies to your situaion .... LiveOak & other developers always keep 2 serial numbers - one visible to the user and another, invisible, to guarantee consistant relations even if you change your order numbering scheme.

[Ugo DI LUCA]

Hi Ricardo,

As Christian said, your serial could look as "A" & Right("00000" & serial#,5), but this should be the visible part of an Unique_ID which first part could be a concanation of Status(CurrentDate).

Just have a look to this thread where both Brent (Live Oak) and Ray (Cobalt Sky) came up with an interresting ID structure.

Serial Number Thread

[Ricardo]

This is not for Unique Record ID. it's more in that it creates a sequential Order number e.g record 1 = A1, record 2 = A2, record 10 = A10 and so every time a new record is created the order number moves up sequentially.

Thanks again.

[Ricardo]

sorry forgot to mention that for example when the order number reaches A100, it then goes to b1, b2 etc.

[Ugo DI LUCA]

Hi Ricardo.

So you will be limited to 26 *100 = 2600 records.

Now, from what you said, what could be interresting would be to have an Alpha AA, AB, AC and a serial 001 to 100.

I'm not that fresh today but you will need a unique ID anyway for this.

Have a look at these threads.

The first is a clever calc to determine what would be the next alpha. In your case, you might adapt the calc to jum to next alpha everytime you pass a 100.

The second is another clever calc to autoenter the next record, therefore would be useful to auto-enter the next alpha.

Guess what. Both are Ray's ...

Next Alpha

Entry from Next record

[BobWeaver]

Ricardo, there are any number of ways of getting an alphanumeric serial number but, as Ugo points out, a system that is only defined for up to 2600 numbers won't be very useful. What should the next number after Z100 be? Or, do you mean Z99? I think you need to give us more details.

[Ricardo]

Ok here goes - i'll try explaining more clearly.

My orders need an Order number to be identified by. now this number needs to move up by one for each new order (each order represents 1 record)e.g if order one is 'A00001' the next order number need to be shown as 'A00002'.this will go right up to 'A99999' then i want it to go to 'B00001' and so on, this going right up to Z99999. at this point the order numbers can start again at A00001.

i have been asked that the order numbers are 6 charachters.

so what i need is some form of calculation/script to input this order number for each new record and for it to move up sequentially.

Thanks again,

ricardo.

[CobaltSky]

Hello Ricardo,

Based on what you've said you require, I suggest that you create an auto-entering serial number field (defined as a number field) called 'AutoSerial' and then create a calculation field that generates your order numbers using the following formula:

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mod(Int((AutoSerial - 1)/99999), 26) + 1, 1) & Right("00000" & (Mod(AutoSerial - 1, 99999) + 1), 5)

That will always give you one alpha and five numeric characters, will skip over IDs where the numerals are all zeros (ie it will go from A99999 to B00001) and wil start again at A00001 after Z99999.

[Ricardo]

Thats FANTASTIC. thanks very much.