Match Order to Available Inventory

[Madapple]

I am working on a system that will take orders and find the warehouse that has the stock to fulfill the order.

 

We have 19 warehouses that hold various different items, Based on inventory some items are at all warehouse some are only at one, some are at none.

 

I have created a portal that allows me to search for a SKU and see all warehouses that have the item in stock.

I would like to be able to import the orders as csv - create a record for each order - then show the warehouse that has all items available.

 

Current structure:

INVENTORY:    Table with SKU, Warehouse Name, Warehouse Number, Quantity On Hand

WAREHOUSE: Table with Warehouse Name, Warehouse Number

ORDER:           Table with All items on order, Order No.

MASTERSKU:  Table with Master SKU list, ItemID

 

The Master SKU list layout has a portal that shows all warehouses with the SKU being assigned and the QoH.

 

Part of the issue is the order comes in with an ItemID - 4 to 6 digit code, not the SKU. Each order has 14 fields for itemID

 

Relationships

ORDER ItemIDs -> MASTERSKU ItemID

MASTERSKU  SKU -> INVENTORY SKU

INVENTORY WarehouseID -> WARHOUSE WarehouseID

 

 

THoughts??

 

Thanks

 

 

 

 

 

 

[comment]

Part of the issue is the order comes in with an ItemID - 4 to 6 digit code, not the SKU.

This part can be solved quite easily by letting each order lookup the SKU from the MASTERSKU table. However:

 

Each order has 14 fields for itemID

 

This part is a major obstacle. You must find a way to split the orders into individual line items.

 

 

INVENTORY: Table with SKU, Warehouse Name, Warehouse Number, Quantity On Hand

 

Can there be multiple entries for the same SKU in the same Warehouse?

[Madapple]

I was afraid of the Each Order as a record problem... I now have a table that has only the order number and the item ID

 

 

Each warehouse will only have one entry for each sku

[comment]

I was afraid of the Each Order as a record problem... I now have a table that has only the order number and the item ID

 

Ok then, the rest should be pretty easy: define a relationship between this table (ORDERITEMS) and the INVENTORY table as:

 

ORDERITEMS::SKU = INVENTORY::SKU

AND

ORDERITEMS::Quantity ≤ INVENTORY::Quantity On Hand

 

 

For the purposes of the above, the ORDERITEMS::SKU field can be an unstored calculation field =

MASTERSKU::SKU

based on a relationship:

 

ORDERITEMS::ItemID = MASTERSKU::ItemID

 

For other purposes, you could lookup the SKU value (using the same relationship) when creating/importing the ORDERITEMS records.

 

 

Note that you might have to use new occurrences of the INVENTORY and/or MASTERSKU tables to create these relationships.