So I am trying to create a somewhat all encompassing purchasing, inventory, and installation/delivery base to track some material for a specific work scope on a construction site.
Essentially we issue purchase orders that have various line items for materials on them (say 10" pipe, 12" valves, 6"x1/2" bolts, 1/2" nuts, etc etc) and they go out to lots of different vendors. I want to log in all the stuff we’ve bought and what vendor it’s from, the purchase or number, etc etc.
Next, I want to log in what we have received into inventory. For example I may do a PO to a vendor and I request to purchase 100ea 6" bolts, 100ea 1/2" nuts, and 100ea 1/2" washers. This may not all come in the same shipment. I would like to log that I received 30 bolts, 50 nuts, and 50 washers on 7/31. Then I would log receiving another 30 bolts, 50 nuts, and 50 washers on 8/3, for example, then the balance of the 40 bolts remaining out of 100 on 8/15 (very basically example). The stuff coming in a delivery comes with a receiving tag, so we would upload that as an attachment.
This would so far hopefully be tracking what I set out to buy, what has arrived, and what is still waiting to be delivered.
Finally, this material gets installed on the construction site, so it would LEAVE inventory in the warehouse. I might get the 30 bolts on 7/31, and then on 8/3 I get 30 more so on 8/4 I have 60 in inventory. But then on 8/5 I might put 50 of those on a truck and send them to the jobsite, so now I have 50 out for install and only 10 left at the warehouse - basically trying to track the disposition of all the stuff from when we order to when it arrives to when it leaves to be installed (and then ideally when stuff is actually installed so I can know if we sent 50 to the site but only 40 were installed and someone lost 10 of them).
I think what the question here is somewhat revolves around what linked fields to use to not make a bunch of duplicate entries and how dates plate a role in this (I would like to see the draw up and draw down in inventory over time).
Right now I’ve got a table for purchase orders (PO_###_### - Vendor Name) with the numbers being unique). Then I’ve got a PO details table where I link to the purchase order table and I might have 10 lines linked to that PO, so a roll-up back on the PO table shows me the total of that PO.
I’ve got an “Inventory” table that I’m not quite sure what to do with yet, and then a shipment table and a receiving detail table. The shipment table would have a unique shipment per line, with a date and the attachment of the receipt that comes with the shipment. The receiving detail line then links to both the shipment table to give a few lines of detail to the shipment AND to the PO detail table to select what items were received that day and then enter the quantity.
This seems ok so far, but what I need to figure how to setup is a table of unique inventory that pulls from all of these. For example I might buy 100 6" bolts in PO_001_000, but then I might need more and order 300 more bolts in PO_005_000. In PO table, those are two different POs. In the PO detail table, those are two different details (cause one is for PO 001 and one is for PO 005). In shipments they will come in separate (likely) and the receiving details also separate. On inventory - I just want them to be the same product cause all I care about there is how many I have.
Does the inventory have to be the origination point of a unique piece of material we are buying, then link that back to the PO details table and assign a PO number to it, or is there a more elegant way of creating this workflow such that the “inventory” table collects all unique values from the PO-Details table in the product/material column (but not the lead column, as that would be 6" Bolt - PO_001 and 6" Bolt - PO_005 by formula based on other fields). I think starting at inventory works but I plan to hand this base off to other people with significantly less process attention span than myself.