Novice - Inventory relational data?

[illtowers]

I've been working on a database for my father's small apparel business and have run into an issue I am having trouble solving so I'm wondering if possibly my approach is wrong and what is the best way to fix it. 

 

The database is meant for inventory tracking and invoicing but more importantly is where the inventory resides. The business imports different styles of clothing from overseas and receives a packing list which lists every item that is in every box. With my database, I input that list into a layout called Container through a portal on a table called Container Data. Each line item corresponds to an individual box with a specific style color and size and the amount that is in there. Each line item gets an individual auto-entered box ID and each product has a calculated product ID based on style color and size ( ie: shortsleeveblueLG ). Once the items in the boxes are inspected and re-counted they are moved to a location in the warehouse and a location ID is added to the container Data line item. I then have a separate table with a preset number of locations in the warehouse and each location has it's location ID. These tables are linked by location ID and when a button on a line item in the Container layout is pressed it takes me to a warehouse layout that highlights where the box is. I can also navigate through each rack in the warehouse layout and see a list of the boxes and contents of each box that are in each slot via portal. It works beautifully and great - thank you filemaker!  

 

The issue starts when there are more than one style/color/size items in a box and when the items in the box have to be moved to separate locations. There is limited warehouse space so often products that are the same get moved into the same box although they come from separate containers. The main reason it's important to track items by container is that each container comes from a specific "batch" when it was made and not all batches are the same so for instance Navy Blue from batch number 1 might be slightly darker than Navy Blue from Batch number 2. When we are filling up orders in bulk which is 99 percent of our clientele, then the full order must match. They must all come from the same batch or what I call Shade family. Each line item in Container Data contains a Batch ID and a Shade Family ID. When an invoice is processed we must designate from where we grabbed the product in order to a) Track how much of that particular Item we have in stock how much of that particular Batch we have in stock and c) what Shade family did the client get so that the next time he orders he will get exactly the same color shading he ordered previously.

 

The logical issue I'm having is that at the moment each line item in my container corresponds to a box but I am also using it as my master stock table. From here I can easily determine how much I have left of every single item and I can sort it by batch Id, Container ID or Shade family. I don't want to have multiple entries of items within the same line item because I feel like it will get too confusing and mess up all the math. I have gotten around the problem when entering multiple items in a box into my container layout by adding extra line items because these items will eventually be moved somewhere else anyway but now my container layout does not exactly match my packing list which will always be used as a reference (not that important to me but seems highly important to my father). However, when moving items from one Container Data Line item to separate locations I will end up with multiple location IDs within the same line Item

 

I feel like there must be a box table and a box data table so that I can add to a box but I'm unsure on how to move the data around and I also feel like the Warehouse table should draw from the container data table like an invoice would but I can't seem to wrap my head around how all these table relationships should interact.

 

Any help would be greatly appreciated.

 

 

 

 

[Matthew F]

OK. Maybe that's a little too much information.  

 

One way to handle this is to make a table that represents locations (e.g. specified by room, rack#, and slot ID).  Keep this conceptually distinct from tables which represent movable containers or items within containers.    Location tables are static - once you create a table a record for each possible locations (room, rack, and slot ID), then the user should not need to change these records unless they reconfigure the warehouse.

 

Containers, and the items within them, on the other hand, may be created or destroyed.  Either you will want to create and delete these records to match your inventory, or else you will want to mark them as "inactive" and filter them out, if you prefer to keep the records but mask them from your current inventory. 

 

When you physically move an item (or a container that holds the item) it gets reassigned a different location ID.  Then it will show up on your location layout in the proper place.  

[illtowers]

Sorry thought that more information will help better illustrate the issue I'm having but guess not. The location table is not the issue - I already have a separate table with a preset number of locations (static). The problem is I have to take my data from how it is entered into the database (How it is shipped) and split it into how it is stored. So it is not as simple as assigning a location ID to a line item on my shipment because it will go to more than one place. So when I enter I received 30 shirts in a line item on my container shipment I may put 15 in location A and 10 in Location B and 5 in Location C - would end up with 3 values in one field. 

 

I think I've solved the issue by creating a separate box table with a box contents table that will be my master inventory data table from which all my invoices would link to. I create a box and give it a location - when a shipment comes in it goes to the Container data table and the information in that table is duplicated (depending on how many places it will go) via a script trigger using an import function into the box data table and at that point I assign the quantity values that go to each location. It's a little clunky still and all the data duplication worries me in the long term - if anyone has any better solutions I'm all ears.