Manufacturing Serial Number Decoder

[Brisben]

Hi there good people

I have been a casual and basic filemaker user for a number of years, starting with the simple form that was included with ClarisWorks all those years ago.

I recently decided to challenge myself to write a database to help easily decipher and record manufacturing codes for a hobby I am involved with - Vintage BMX Bikes.

To say the least I have stumped myself well and truly and am looking for some assistance.

Here is my dilemma:

I know for certain the meaning of the sequence of the numbers and letters in the stamped serial number but I can't get my head around transforming it to a database!

EG: Bike frame stamped 9143D

I know that this can be broken down to:

9th month (September), 1981, 43rd in sequence, Welded by Darrell Kendall

Frame stamped 501161D

Is 5th month (May), 1980, 1161st in sequence, welded by Darrell Kendall

There is physical evidence on the frame of the model change so that can be marked in the database.

What I really need help to work out is what is the best way to be able to type in the number and character stamped on the frame and have it decoded???

Hopefully someone can point me in the right direction.

Many thanks in advance.

Ben

[comment]

1. Are all bikes manufactured in the eighties?

2. How many welders are there? Is the welder always identified by a single letter?

Edited September 20, 2010 by Guest

[brian rich]

From the two frame numbers you have offered, you could use a few calculated fields to get the information out that you need viz

If frameNo is the serial number:

Month is left(frameNo;1)

Year is middle(frameNo,2,1) + 1980

The welder is filter(frameNo;"ABCDEFGHIJKLMNOPQRSTUVWXYZ")

The Serial number would be

left(middle(frameNo;3;99); length(middle(frameNo;3;99)-1)

[The above uses a length of 99 to force it to select everything to the end of the frameNo from the 3rd character onwards.]

What I'm not clear about is what they did in October, November or December, as there appears to be only a single digit for the month.

HTH

Brian

Edited September 20, 2010 by Guest

[Brisben]

1. Are all bikes manufactured in the eighties?

2. How many welders are there? Is the welder always identified by a single letter?

1. We think all bikes are manufactured in the eighties - you would not believe how hard it was to crack this code - the bikes made in the seventies have totally different serials - which still haven't been deciphered yet

2. From what we can tell there were 3 welders all identified by a single letter - their first initial

(there are also prefixes for special edition bikes but they are quite rare and can be dealt with on a case to case basis ie T for team rider bike)

[Brisben]

What I'm not clear about is what they did in October, November or December, as there appears to be only a single digit for the month.

HTH

Brian

That is what confused us for some time!

Following the sequence, purely for illustrations sake, the last December 1981 frame, build sequence 256 welded by Mark Bradshaw bike would be stamped

121256M

then a January 1982 welded by Darrell would be

12257D

When we only had a few frames as reference you could understand our difficulty in establishing their heritage

Cheers

Ben

[comment]

Brian's remark is right on the money: how can you tell that "121256M" is not January 1982, #1256 or that "12257D" is not December 1982, #57?

[Brisben]

Brian's remark is right on the money: how can you tell that "121256M" is not January 1982, #1256 or that "12257D" is not December 1982, #57?

Now you encountered the difficulty of my task

It is known that there are two models - the second commencing in September 1981 eg: 918D

Unfortunately we feel there might be an overlap from version one to version 2 of maybe a month or so

So in summary - the code for the stamping remained the same but was re-set with no additional acknowledgement

So we also know that the first edition frames were stamped in the same way but from (we think) September 1981 the build sequence was reset to "1"

Luckily visual appearance can tell these two apart.

Now you could possible have an idea why I have been tearing my hair out!!!

[comment]

I am afraid I don't follow this fully. If there are two "versions" of the frame number and each needs to be interpreted differently, then the version should be entered into another field (assuming it's not apparent from the frame number alone).

Now I would suggest you sit down with a piece of paper and write down the steps you would take to identify the month and the year without a computer (think of this as writing directions for a stupid but reliable person). Then the actual calculation will become easy to construct - assuming the task is possible in the first place.

[comment]

A thought: if the date is ambiguous, couldn't you use the sequence # to determine the correct choice? After all, an ambiguous date is either January or one of the last three months of the year.

If you have even a rough idea of the sequence's progress, then some interpretations can be eliminated: for example, if you have "62500", then "122900" MUST be December, and "12200" MUST be January.

[Lee Smith]

Hi Ben, and Welcome to the Forum,

I've been toying with your sample data in TextWrangler, and I think it might be possible to do the lion's share of this parsing out using grep patterns in TextWrangler or BBEdit.

The real challenge is the Oct - Jan productions, however, I was able to parse out the Dec and Jan example you gave, using a couple of greps. However, the real test would be with real data.

Are you trying to breakout this data on bikes you own, bikes you want to purchase, or are you doing this for all of the bikes manufactured by these two?

Do you have a complete list of those you want to parse out?

If so, why not post it as an attachment to a Reply, so that I can get a better feel for the complete picture?

HTH

Lee

Edited September 20, 2010 by Guest

[Brisben]

Hi Lee and thanks others for your replies

What we are trying to create is a couple of things in one for the enthusiasts of this particular Australian made brand.

Firstly a decoder of the number stamped on the frames

Secondly an historical archive of the frames themselves including some other aspects such as paint finishes and the like

Here's the breakdown as we established it, in a little greater detail

For the frame stamped 501161D as an example

5= May [some serials began with 10, 11, or 12 for October, November or December. eg 110xxxx X]

0= 1980 [they used the last digit of the year & will always be 9,0,1,2,or 3.]

1161= the serial number of the frame. This can be as short as one digit, ie, 911 for Sept, 1981, frame number 1.

D= Darrell Kendell, the man who actually built the frame in the factory.

Other code letters used as a suffix are

M= Mark Bradley who was a frame welder, a designer, test rider & Factory Team rider.

R= Ray Williams. He is currently the state Liberal member for Hawksbury.

X= Not currently known but thought to have been used on Factory Team race bikes.

Prefix codes were sometimes included and can also be used as qualifiers

TF= Team Frame. This was used on the 'Team' models, which was their production race model from December 1979 to August 1981

TH= Team Helium. This was the replacement race frame for the Team & was manufactured from September 1981. It was available until the end of manufacture in 1983. (these also had their own serial numbers starting from 1 - it is thought only 150 of these were made)

24. This was seen on the earliest of the 24" Cruiser models & was followed by a one or two digit serial but no date code.

C. This was used on later 24" Cruiser models. Very low production numbers but part of the general serial sequence we think

CR. This was used on 26" Cruiser models. Same as 24" models

M. This would indicate the Mountain Bike model that was largely based on the 26" Cruiser frame. ditto.

Other classification items that can be included are made by a visual inspection and could be simple radio buttons or check boxes - this is what would help discern the "September Crossover" frames which we have termed Series 1 and Series 2 - the area where the rear wheel connects was the change between these models so any 91XXX serial number will also have to be qualified by checking one of these boxes as well.

Ultimately we would like to have the following fields of input:

Prefix (as above with a blank field terming the frame as "Standard)

Number

Suffix

Location of stamping (one of two options)

Dropout type (one of two options)

Brake bridge type (one of two options)

Factory finish (Chrome or paint)

Current owner

The result of the input to the user would state something along the lines of (in the case of 501161D):

Standard Frame, Manufactured May 1980 by Darrell Kendall, Number 1161 in sequence

The rest of the information will serve as an archive of all the known bikes and as we know is searchable and sortable if required

Phew - that's about it, I'm starting to think I have bitten off a lot more than I can chew

Cheers

Ben

[comment]

You still haven't offered a way to distinguish between MYxxx and MMYxx. This is not a calculation problem. Unless some additional information is provided, the code cannot be parsed reliably.

[Brisben]

You still haven't offered a way to distinguish between MYxxx and MMYxx. This is not a calculation problem. Unless some additional information is provided, the code cannot be parsed reliably.

Other classification items that can be included are made by a visual inspection and could be simple radio buttons or check boxes - this is what would help discern the "September Crossover" frames which we have termed Series 1 and Series 2 - the area where the rear wheel connects was the change between these models so any 91XXX serial number will also have to be qualified by checking one of these boxes as well.

This is only for one month - September 1981

The difference between the Series 1 frame and the Series 2 frame is visually quite different - hence the need for a check box that would relate to the serials - it would be impossible to have a Series 2 frame from September 1981 with a serial higher than say 100, but quite possible for a Series 1 frame to have a serial higher than 1000 - the additional information will be one of two options

I should also add that the "September Crossover" frames are only a remote possibility

The serial number only was reset in September 1981 as the new version of the frame was released - the prefixe, where used and suffixes remained the same

The location of the stamping of the serial number on the Series 2 frame was also changed somewhere between 918D and 9143D, there are NO frames we know of before 9143D that are stamped in the same area as the Series 1 Bikes.

[comment]

I am sorry, but you are only confusing me even more. If I understand correctly, after removing the prefix and the welder's code you are left with a sequence of digits. I think you have now also narrowed the years to 1979 - 1983. And I believe the serial number cannot start with a zero.

Therefore, if the sequence begins with one of these:

109

101

102

103

119

111

112

113

129

121

122

123

we need something more to tell us what these mean. Everything else should be very easy (unless I am missing something again).

[Brisben]

I am sorry, but you are only confusing me even more. If I understand correctly, after removing the prefix and the welder's code you are left with a sequence of digits. I think you have now also narrowed the years to 1979 - 1983. And I believe the serial number cannot start with a zero.

Therefore, if the sequence begins with one of these:

109 - could only be January 1980 - 9th build - this is not possible

101 - couldn't be January 1980 first build - but possibly October 1981 depending on style of dropout (see my reply below)

102

103

119 - Could not be January 81 9th build - would be November 1979 followed by a serial and identified by the dropout style

111

112

113

129 - December 1979 - there is no likelyhood from our data that serials re-started in January 1982

121 - December 1981 - no possibility the first frame being manufactured on 1st february 1971 or 1981

122 - December 1982

123

we need something more to tell us what these mean. Everything else should be very easy (unless I am missing something again).

Maybe my terminology is what is confusing but I'll try again

109 would be October 1979 with the identifying input being the style of frame we'll call it "Mongoose dropout" - It cannot be January 1980 9th frame made as if that was the case there would only be a suffix after the 9.

it is the style of dropout - either "mongoose" or "thruster" that will help identify - and we know for certain that the "thruster" style dropout only started in September 1981

So - the person who inputs the data has only two choices "mongoose" or "thruster" in the dropout field - it will help identify whether or not it is pre or post September 1981

I have added to the quoted post above

Gee I wish we could do this over a cup of coffee - I'm much better at explaining things face to face and with a drawing or two !!

PS - dropout is the section where the rear wheel connects

[comment]

Well that is some progress - but I am afraid you may have missed the "begins with" part.

BTW, are you familiar with this tool? I think it could be very helpful here.

http://en.wikipedia.org/wiki/Flowchart

Edited September 21, 2010 by Guest

[Brisben]

You want me to start up and old 486 machine and Visio chart it for you?

[comment]

I don't care if you write it out as text - as long as it becomes an algorithm (i.e. something that a stupid machine like a computer can follow blindly, without any judgment calls of its own).

---

P.S. You can skip the prefix and welder extraction part - these are pretty clear, I think. Start with the following input as being known:

FrameNumber - a variable number of digits;

Prefix - as per the list of possible prefixes you have provided (incl. "24")

Location - a Boolean value, I think?

Edited September 21, 2010 by Guest

[Brisben]

I don't care if you write it out as text - as long as it becomes an algorithm (i.e. something that a stupid machine like a computer can follow blindly, without any judgment calls of its own).

---

P.S. You can skip the prefix and welder extraction part - these are pretty clear, I think. Start with the following input as being known:

FrameNumber - a variable number of digits;

Prefix - as per the list of possible prefixes you have provided (incl. "24")

Location - a Boolean value, I think?

OK how is this then - I have made a flow chart of sorts to help

(all the symbol boxes made it spread too far over the page so I coded the text in colour instead - the legend for the colours is at the bottom of the page)

Thank you ever so much for your patience and effort so far!

Quicksilver_Flowchart.pdf