I'm working on a reliable inventory system, but I am stuck with this because of the specificity of my business (Ceramic tile wholesaling).

You may have experienced it as an individual, if you had some repairs in your house. It is quite impossible to find exactly the same ceramic tile that the one you bought 30 days ago, even with the article code on your invoice.

As a mixture of clays and low-iron content kaolins, with temperatue of + 1200

It sounds to me like you already know the answer. Each specific variety of tile should be its own product. For example you do not sell "Piazze di Roma", but you sell "Piazze di Roma A3/3" and "Piazze di Roma B5/7" as seperate products.

I would have liked it to be so simple !

This is simply impossible. I already have 28,475 records registered in my Product file listed with their specific vendors references. Listing each product different from the calibre/shade would expend this list to almost +400,000.

Let me make it clearer with another example from another industry

Imagine the GAP Sportswear Product List

T-Shirt Red XL - 456fglm

Sweat Blue Small - 476fclm

Short Pink L - 472fxlm

T-Shirt Black L - 479fxlp

...

where 479fxlp is only the unique product id in the catalog list

In my case, I almost actually have

Piazze di Roma 30x30 Tile Beige 55XLM

Piazze di Roma 45x45 Tile White 45XMP

Piazze di Roma 30x60 Tile Yellow 85DL

Piazze di Roma 30x60 Decoration Yellow 84MP

...

where 55XLM is the unique product id from the vendor catalogue.

If I didn't have any Piazze di Roma in stock, or if in the example from the previous post, I placed a new order, both customer order and purchase order would have in my line item :

00245DFK(my unique Id) - Piazze di Roma 30x30 Tile Beige 55XLM

The Calibre/Shade would be only added when the product is received. I would therefore have

00245DFK- Piazze di Roma 30x30 Tile Beige 55XLM + Calibre/Shade code

The Calibre/Shade code is a feature that doesn't change the product id itself, neither the price.

Its a "sub category of stock" - I wouldn't even don't know how to call it.

See where I am ? In a big mess.

Working on this for about 5 months...

Well, I am hearing that those codes do matter, but you just do not want to have that many products. I would be stumped as to how to deal with this another way. I suppose that you could have a product list and a seperate inventory list. However you'll then trade a big product list, for a big inventory list, as you'll need to record every specific product (including the color code) as it come in. Searches will have to encompase both DB as neither will contain the complete product list.

Oh Captain

Do not believe that all the products in my product file are in stock...

A separate inventory file would certainly consist in about 300 to 400 products. It would be acceptable, but I was wondering if I could keep the Inventory in the Product file, looking to the line-items to "filter" the shades/calibre on hold.

Of course, I want my solution to handle average stock costs also...

Reading all the posts on the Forum about Inventory didn't help me out to choose a method !

May be an idea came out. I have to make a test file but here it is.

I have a Product file with Product_ID (textfield) and all the relevant information for the product

My Line Item File with the Product_ID as a lookup from Product

The Shade/Calibre is a text field also

So why not create a c_ShCal field = Product_ID&Shade/Calibre code in Line Items and a new File called Shade/Calibre Stock.

Each Product would be registered through a script that sets a t_Product_ID&Shade/Calibre code.

Lookups could bring separately to this new file the Product_Id and the Shade/Calibre code.

The problem is I have separate line items for purchase/Transport and quotes/customer orders/shippings/invoices, but I was looking for a way to put them together.

All future entries with same Shade/Calibre code would be registered into this file, and I would still have the stock shown in the Product File with the relationship ProductFile:Product_ID::Shade/Calibre Stock:Product_ID. to show me through a portal the total on hold for each Shade/Calibre for the same Product.

Do I miss something...?

I'll wait for other comments as I learned from time that Patience is better than everything.

I finally got it stay into the Product File. I think it is OK. Just have a look at the attachment and tell if you can see "major" concern and errors.