Madapple Posted November 26, 2014 Posted November 26, 2014 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 Posted November 26, 2014 Posted November 26, 2014 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 Posted November 26, 2014 Author Posted November 26, 2014 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 Posted November 27, 2014 Posted November 27, 2014 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.
Recommended Posts
This topic is 3745 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 accountSign in
Already have an account? Sign in here.
Sign In Now