Jump to content

Match Order to Available Inventory


Madapple
 Share

This topic is 2929 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

 

 

 

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2929 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.