NEED HELP WITH CUSTOM SERIAL NUMBER

[EIGHTWGT]

Hey everyone - I have a a whopper of a problem. I am trying to do something in a field way beond my skill level and need help.

Here is the problem

I need a serial number in a format as follows - MSF40000B606

This is defined as :

MSF 40000 B6 06

MSF is constand never changes

40000 is the 'serial number- changes by +1 each new record

B6 is date of manufacture - the 'B' is letter for February,March is a 'C', April is a 'D' and so on..

The number after it is the year built-6 is 2006,in 2007 it would be number 7-so summary if it said A5 then it would have been manufactured in January of 2005

The last 2 are the model year 07 means its an 2007 model year- this changes each July 1st to the next year just like automobiles....

Like I said its a whopper of a calculation (for me anyway)

HELP !

Edited October 14, 2006 by Guest

[ThatOneGuy]

Hi EIGHTWGT and welcome to the forums! :welcomesign:

Wow, you waited to post a doosey! Actually, it shouldn't be too difficult.

You have several (I count 4) constituents we need to work into the calculation for your "Master Serial ID" field. Before we can come up with a calc, we'll need some more information about these fields. If you could provide the exact names you're using for these constituents, their "field type," and such, we can get crackin' ...

• the "serial number" field, e.g. "40000" etc.

• the "date of manufacture" field (and is that a true "date" field or something different?... please clarify)

• the "year built" field (a four-digit number field, right?)

• the "model year" field (another four-digit number field, correct?), and is this a calculation field that changes automatically each July 1st?

Hope to hear back!

[EIGHTWGT]

First - remember the whole serial number is the sum of the parts as mentioned and described above.

• the "serial number" field, e.g. "40000" etc.

That field which represents the whole serial number is named "VIN NUMBER"

• the "date of manufacture" field (and is that a true "date" field or something different?... please clarify)

No field yet specified-can be any name we want

• the "year built" field (a four-digit number field, right?)

No field yet specified-can be any name we want

• the "model year" field (another four-digit number field, correct?), and is this a calculation field that changes automatically each July 1st?

No field yet specified-can be any name we want

I have not specified ANY of these field other than the final output....

[EIGHTWGT]

MSF40000B606 = Complete Serial Number-

This is what I need the finish calculation field to output and in this format, and do so automatically whenever a new record is added. The serial number is based on the serial number,date of manufacture,and year model. So it has to calculate what month it is to get the input for a couple of the parts of the serial number and it changes obviously with each month and if it is past July 1st !- the serial number is figured or defined by its parts as I tried to explain;

MSF is constand never changes

40000 is the "serial number"- changes by +1 each new record-

B6 is date of manufacture - the 'B' is letter for February,March is a 'C', April is a 'D' and so on..The number six is the year built-(6 is 2006),in 2007 it will be number 7- so summary; if it said A5 then it would have been manufactured in January of 2005.

The last 2 are the model year 07 means its an 2007 model year- this changes each July 1st to the next year just like automobiles....

[ThatOneGuy]

Hey EIGHTWGT!

Thank you for answering those questions; I think it gave me greater insight.

I've attached a sample file with a functioning calculation. Since you do not yet have any fields created, and since you indicated you want all this to happen "automatically," the calculation does not rely on user input for the fields I asked about. Rather, it directly produces a VIN based on the creation date for the record. (If I've missed the boat on this, let me know! We can create those other fields. However, the VIN could not be calculated until those constituents are populated ... a mild chicken-and-egg problem.)

You'll note there is a separate field "pk_ID_MSF" that generates a serial ID number, and the "VIN" field then takes over from there. Other developers may point out there are ways to achieve this all with just one field, but I take an overly cautious approach when working with serial IDs in a multi-user environment, also keeping in mind that "record import" schemes may be used in the future. It follows then, if you implement any relationships, I imagine you'll want to reference the "VIN" field instead of the "pk_ID_MSF" field.

Additionally, I used the Get ( CurrentHostTimeStamp ) function, as opposed to the Get ( CurrentDate ) function. Because it makes a call to the server, it is not subject to the vagaries of users' CPUs not being set to the correct date/time. If you're operating in "single user mode," it simply defaults to the CPU.

I created several test records, changing the Date/Time settings on my machine. I think it's gonna work just fine, but I recommend you put it through some steps, yourself.

Hope it works for you, and I'll wait to hear back. :cofeebrake:

CustomSerialNumber_01.fp7.zip

[comment]

It follows then, if you implement any relationships, I imagine you'll want to reference the "VIN" field instead of the "pk_ID_MSF" field.

I would say exactly the opposite. The scheme may be changed in the future.

[ThatOneGuy]

I would say exactly the opposite. The scheme may be changed in the future.

... okay

[EIGHTWGT]

Oneguy -

Thanks for the file- I will try it Monday when I get back to the office. I cant wait to try it. You were correct in making it automatic without user input based on the record creation date.

Thanks a million.

:waytogo: