serial code for product

[moj6054]

Hi, every one.

I have a problem, for Product's code.

I want give code to product according product's category. I mean, I have :

category table(catID,CategoryName) in this table have some record (Board,CT,Cable,..)

Product Table(ProID,catID,ProductName) in this table some record

and link two table (category::catID - Product::catID)

I want give to this product a code like this: if in category table (Board's ID=1) then in product table, productID=11 and for second product in this table product'ID = 12 and go... and

if in category table (CT's ID =2) then in product table, productid=21 and second product in this table product's ID= 22

Please help me,

Thank you very much

[LaRetta]

Hi Moj6054,

Numbering schemes for keys is a risky affair because they can break in multi-user mode where two Users might enter new record at the same time. What is the purpose of this 'unique code' that you want to create? The purpose will dictate the process. :^)

[moj6054]

thank you,

Only one person can enter product in product table.

[LaRetta]

if in category table (CT's ID =2) then in product table, productid=21 and second product in this table product's ID= 22

Hi Moj6054,

But this will mean that your Products table will have the same product twice. The Product description would be same so you would create redundancy in your Products table. Or would the PRICE be different? Just the fact that you have catID in Products is all you need since you've already created a Category table.

If you want this for easier selection of the 'product' by User then a conditional value list would probably work better. If it is for display only then you can create a text calculation which concatenates both pieces as ProductID & CatID but even that won't work well if you run into double-digit numbers.

What are you trying to accomplish? We can help you get there. :^)

[moj6054]

Thank you,

But this is not my answer.

Let, I write again mu question,

I want a code design automatic for product according with group,mark, If i have a 20A MCB with ABB Brand, Then in product table, this product have code:1 for MCB,1 for first record in table and I see 11 20A ABB

and

for 20A MCB Schneider , I have 12 20A Schneider

then for 1A MCCB ABB , I have 21 1A ABB

If this problem is clear, Please help me

[LaRetta]

"I have a problem, for Product's code."

You explain what you want but still have not provided the true purpose of it. If it is to create a primary product key, I suggest you instead use auto-enter, FM-generated serial number. You are attempting to attach meaning to a code and it will surely break at some point because someone will assign improper code and then, when you correct the code (ProductID), any LineItems (invoices) holding that product will become instant orphans and they will no longer appear on invoices anywhere ... very bad stuff.

If I have again misunderstood, I apologize and hopefully someone else will understand your request and respond. :)

[moj6054]

I do not use it for product key, this is only a sign for this product.

..

let I write another question, in a category table, in a special record like "board" i want to know how much related record are in this table?

If a board have 8 related record, then when i add new record, then show 9 for that.

[LaRetta]

Then in your Category table, create a calculation (result is number) with:

Count ( Products::ProductID )

... and then when you are on a Category layout and viewing the Board category, this calculation placed on the layout will count the number of Products which has the Board category ID and it will update whenever you add a new product to the Products table and insert the Board's CategoryID into the Product record.

I still feel like I'm making suggestions a bit blind-folded so I hope this helps you. :^)