I work for a Graphic Design Company and Distribution, and I’m looking for an Item Numbering System and Art File Numbering System that will do the following.

This is what we need.

1. Be able to Create a Unique Item Number that follows the following Rules.

a. Be able to select a Product Line Ex (Miami Ink=MI). IF you select Miami Ink i want the first 2 characters of the Item number to be “MI”. We will have about 100 Product Lines, and each one will have different codes such as “MI”.

b. Be able to select a Category after the product line to have the 3 character of the Item Number. Ex (Youg Men’s=M, Jrs=J, or Youth=Y). So if Young Mens is selected I would want the M to be put right next to MI. So now it would be “MIM”

c. After this I would want a 4 digit number. “0001” so my first Item number would be “MIM0001” and when another item number gets created for the same product line and Category I would want the Item number to be “MIM0002 and so on. So basically I need the system to know what the last item number was for any Product Line and Category.

2. After Creating an Item Number I need to be able to Assing Art File Numbers. This art File Number need to be Linked to the item Number.

a. An Art File number is what we assing to a piece of art. An Art File number has to be unique to a piece of art and has to follow the following things.

i. There’s a total of 8 fields ex(8INK0001). The number 8 is generated automatically by getting the last digit from the Current year. This art file # is created and assigned to an ItemNumber. In 2010 the number will be 10INK0001. “’INK” should be assigned automatically based on the ItemNumber. For example since the Item Number is MIM0001 there should be a cross reference Table telling the system that if the Item Number is using “MI” the Art file number should use “INK” for the next 3 characters after the year.

ii. The Number after that shold be a sequential number 0001. If I need to create another Art File Number for “MI” the next Art File Number Should be “8INK0002”.

iii. More to come

Please let me know if you're interested.

Is this number for end user visualization/utlization or do you intend to use this numbering system for internal relational keys?

im not sure i understand your question, but this Item numbering/art Numbering will be used as a starting point for art management, licensing and other departments, furthermore it will act as a communication tool between departments in the company. I hope this answers your question.

If you send me the list of product lines (preferably in an excel sheet) I will have a go at it for you.

If the product line initials are already set for each product line, please include those in the column next to the product code.

If you want the product line initials to be generated from the product line, let me know if it must be strictly 'first letter of each word' or some other convention. (Keep in mind that it must be unique).

Phil

Edited November 5, 200817 yr by Guest

is the Product Line.

Let me know what you can do. The category should also be something we're able to select.

Also i would want to go over about revisions to art file numbers.

Thanks

Nestor

Product_line.zip

Hi Nestor,

What you want is not that difficult - though a little tricky. Your excel file has the base information to cause the identifier formation. This whole spread sheet becomes one of your tables.

In this table you will need a couple of new fields that are number fields. Then when some one selects a particular line and creates a new record, the number field in that line is incremented by one. A similar field is used for the art work number incrementation.

I would question the padding of the numbers though. 0001 as a number implies that the largest number will be 9999.

The full identifier would then be formed as a new record is created.

Dave McQueen

Didn't we already discuss this?

http://www.fmforums.com/forum/showtopic.php?tid/198650/post/305632

Edited November 5, 200817 yr by Guest

We did, but i ran into a lot if issues that kinda took me back to square one. Im working on those issues as we speak so i can post them and get some ideas if possible.

Thanks

Moved to the private topic

Didn't we already discuss this?

http://www.fmforums.com/forum/showtopic.php?tid/198650/post/305632

Umm ... I had not read that thread. So I haven't.

With regards to the problem posted, I used a technique similar to create invoice record identifiers for a multi centered daycare. they wanted unique identifiers for each invoice, a notation on the invoice of which centre and a numeric order per center for each invoice. So, hopefully it helps ... maybe ... perhaps -)