Help, Inventory, Catalog, Sales Structure

[raymanj]

I need help designing a relational structure to link a catalog, inventory and sales system together. Basically I want to be able to enter any item number from either the inventory's table or the catalog's item table into sales item portal on a order form and for it to pull the information (price, description, etc) into the order.

Current Tables

• Inventory (holds all item information: Item number, price, desription, etc)
  
• Catalog table
  
• Catalog Item Table: Holds the items that are in the catalog. Item info is pulled from inventory when item number is entered.
  
• sales table
  
• sales line item table: holds the items for the order. Item info is pulled from inventory.
  

Goal

We have color printed catalogs that are made by a printing company. Sometimes the item number on the printed catalogs don't match the item numbers in our Inventory system. The catalog numbers are based on catalog and item's location in the catalog. This means the same item could have a catalog number or a item number from the inventory when a customer calls to place an order.

So we need to be able to enter our inventory number or the item's catalog number into a order's portal and lookup the correct item information for the order.

I don't know how to lookup or pull information from two tables at the same time.

I need a good relationship and data structure to start off. Can anyone give some pointers.

[comment]

I think you can do with one unified table of Products, where each product has a ProductID field (item number) AND a CatalogID field.

Looking up the product information can be done in a number of ways, see the attached for a simple example.

LookUpOr.fp7.zip

[raymanj]

I think you can do with one unified table of Products, where each product has a ProductID field (item number) AND a CatalogID field.

Looking up the product information can be done in a number of ways, see the attached for a simple example.

Thanks for the sample file, but I was needing only one field in the order's portal to enter the item's number or catalog number. You placed two fields in the line items table. I would like one field so the user doesn't have to worry about the item number being a inventory number or a catalog number. They just enter a number and the correct item's information is pulled.

Also, an item could have more than one catalog number if it is on more than one catalog.

[comment]

They just enter a number and the correct item's information is pulled.

OK, but you need to tell us how one can tell - just by looking at a number - if it is an item number or a catalog number, and if it is a catalog number - from which catalog.

EDIT:

Or, can you at least guarantee that the entered number is unique (by unique I mean it appears only once in the combined list of item numbers AND ALL catalog numbers)?

Edited January 24, 2006 by Guest

[raymanj]

I am sorry about that. Yes the combined inventory item numbers and catalog numbers will be unique. The current inventory item number are already checked. The catalog items are entered by inventory item numbers then the user will override the number with a catalog number. At that point we can test for uniqueness. The only part where a duplicate can occur is if the printers use duplicate numbers. We will just have to tell them to watch their numbering.

I hope that provides the info you need.

[comment]

OK then, try it this way.

LookUpOr2.fp7.zip

[raymanj]

OK then, try it this way.

Thank you very much Comment. This is exactly what I am looking for.

Now my users can type an inventory or catalog number into the same field on an order and pull up the same information from inventory. This is a huge time saver for them. They don't have to search for a hard copy of the catalog that has the inventory numbers written on it.