hi i have been working in a apparel orders reception

so i have to order quantities depending in sizes and colors ... here i attache an example .. .. i want to know how to related a table with some fields...

i have to record different information from each id product i enter in to the order .. in the same fields...

i hope you understand

here is my file and pic

order.zip

Edited May 3, 200817 yr by Guest

This is a basic retail problem. It is also (even more) an inventory problem. I'm not a retail expert, but I've done some design for this. Basically, each size and color of a product is not only a physical object of its own, it is an inventory item of its own. A purchase order, to bring in more, say shirts, is going to have to specify not only which shirt "product", but which size and color.

So when you sell one, you also need this information. You could just put the Product, the size and the color, then sort it out later. But I think it makes more sense to sort it out first.

So I would have another table, ProductSizes, which would actually be accurately named ProductSizesColors. It would have a unique record for every unique product-size-color combo. This table would have its own unique serial ID (ItemID).

In the Invoice, in the line items portal, you would choose a Product, a Size and a Color. The unique combo would then Look Up the ItemID. This could later be used to show exactly how many of that combo were sold.

Using a combination of fields to Look Up a unique ID is kind of bending the rules, as it's backwards, but I think the logic holds. It is necessary to validate those fields in ProductSizes to make sure the combo is unique.

It is true that ProductSizes would require a lot of records, a lot of data entry. But how else can you do such things as: lavender is only available for size small, and purple is only available for size large?

You could (and would) create a Product-Type-Sizes-Colors table, where you'd first create this multiple values. Say for shirts. Say it has 6 colors and 3 sizes, that's 18 records. Then whenever you created a "shirt" product, you could bring in all 18 at once, then delete/edit for that particular shirt as needed. Yeah, still a lot of work.

But the ProductSizes is also used to filter the drop-downs for Size and Color in the Invoice lines. So the salesperson is only presented with logical choices, rather than a list of all possible choices.

Of course, in the modern world, you'd just swipe a barcode. That would be the ItemID, which positively identifies that particular garment. ProductID does not, it is too general. Something like that.

orders.fp7.zip