Changing SKUs from Alphanumeric to Numeric, How to!

Question

Our company has thousands of SKUs that currently have 1-6 digits followed by 10 letters that represent various color options, ie. 1550-B= Product 1550 is Black. B for Black. 165334-W = Product is White etc.

Our new system will only allow numeric codes (and this will be easier also for our staff to 10-key everything).

We need advice how to numerically enter an inventory number plus a numeric code that equates to the color. We're trying to figure out how to differentiate from product and color. SKUs currently end in digits 1-9 + 0 so we're at a loss on this. Not sure if we could use a period . in between numbers as that might have worked, ie. 1665.2 with 2 representing black. Can periods or any other characters be used in a numeric system? I'd tend to think not - so if you have any ideas, you would be a lifesaver! Thanks!!

Answer 1

If your system allows a floating point value, using a decimal place should be fine.

If not, require that the last one (or two, or three, etc.) digits in the number signify the color. That way all you need to do is look at the last n-digits to determine the color.

Answer 2

If it's anything like the system for fruit or vegetables at a grocery store (that's the closest thing I can think of off the top of my head) then periods and special characters won't work--and it increases the probability of operator error if they have to start remember decimal coding for things. A large part of this depends on what products you are building the system for.

Would it be possible to merge the product number with color numbers and just create one long numeric SKU? ie:

1554643 is x product -- white

1554644 is x product -- black

Answer 3

You need to map out all the options that your letter suffixes represent and see how many combinations are used. (Yes, it will be a big task.) Then you know how much 'room' you need between subsequent new SKUs and use that to convert the numeric part of the old one by multiplying it by a multiple of 10 to fit that gap.

Example: product 165534 might come in four colours, Black, White, Green and Brown. B, W, GR and N, for instance. But 1550 comes in six colours: Black, White, Grey, Blue with Red stripes, Blue with Yellow stripes and Blue with White stripes. B, W, G, BR, BY and BW. Okay, that makes eight variations. So you can convert them as follows:

 - 165534-B  => 1655340
 - 165534-W  => 1655341
 - 165534-GR => 1655343
 - 165534-N  => 1655344
 - 1550-B    =>   15500
 - 1550-W    =>   15501
 - 1550-G    =>   15502
 - 1550-BR   =>   15505
 - 1550-BY   =>   15506
 - 1550-BW   =>   15507

Notice that I've multiplied the numeric part by 10 and then converted the alphabetic suffix to a unique numeric prefix. The first advantage of this is that SKU xxxx1 is always going to be White. The second advantage is that product 165534 is still going to be 165534.

Answer 4

You can use decimals if your sku system uses "decimal" as the sku type, but I doubt it does.

What seems like the sanest way is to

1) take all the letters in the sku, upper case them 2) convert to ascii 3) sum them all up 4) use that as the last 4 digits of the sku, with a leading zero

So 1555-WB => w=87, b=66, = 153

1555-WB = 1555(0153)
1555-WC = 1555(0154)
1555-ZZZZZZZZZZ = 1555(0900) (0900 i the max)

Since you have 6 digits in front, you will always have the last 4 digits as the "sub product" code, until you gov over 6^10 products!

Unless you are pretty unlucky and find places where they collide, then you could always match to the old sku by doing the same math again, without needing to manually maintain a mapping somwhere.

Answer 5

Ouch, what a problem! If you have relatively few colors, and approach like staticsan's should work fine. If you have a rich set of colors you could map each onto its 12-bit or 24-bit RGB color code. These are the same color codes you would use in web page design, eg, in CSS style sheets.

So for instance, to make something red on a web page you turn up the red color to max and leave green and blue at zero: #FF0000 (24-bits) or #F00 (12-bits). White is every color turned up bright or #FFFFFF (#FFF). Black has the red, green, and blue lights turned off: #000000 (or #000)

So you could map your color names onto RGB values like this, then convert them to decimal and append them to the existing numeric code.

You can map these color values into a single decimal number, or you can map them into individual R, G, and B decimal numbers and concatenate them (more readable, but chews up more digits). Guess I'd recommend a four digit value which is the decimal equivalent of the 12-bit RGB values.

If you use Firefox, you can download the ColorZilla add-on, then use it to pick out colors on web pages to see what they map too. Also see this CSS color chart.