Skip to main content

I am trying to create an inventory system for my company (we fabricate fire sprinkler systems). We generally have 10-15 purchases a week for material. The issue is that I don’t receive all of my material on individual shipments, some times it takes 2 or 3 shipments and on rare occasions as many as 5. How can I create a base that records the items that I have received while keeping those I haven’t opened? I only get invoiced for those I receive so being able to show only that amount would be beneficial as well.

Right now I have an inventory being created in one of the tables, and then on the purchase order, I can select multiple items from the inventory table to fill out a purchase order. I need to show the items that have been received as received when they come in, while still showing the other items pending.

Eventually I want to then ship out the items we have made in partial shipments as we don’t deliver 100% of the fabricated parts to a job site all in one day.

You could try inserting another table in between “Inventory” and “Purchase Orders” that joins the two - perhaps called it “Received”.

“Received” would hold a record for every shipment received, and detail the number of each product in that shipment. The “Purchase Orders” would compare the amount ordered to the amount received, and could contain multiple “Received” records linked to it to fulfill the Order.

Inventory would count the number Received, rather than the number ordered, so that it is always accurate.

You can set up the same system for outbound orders – a table in-between called “Shipped”, leading to a table called “Orders”. “Orders” would demand a certain number of a product, and would compare the demanded number to the shipped number, whereas Inventory would only pull it’s value from the “Shipped” number (not the “Ordered” number) to update its status.

@Jeremy_Oglesby thank you for helping me! I appreciate it.

I have begun creating the full base to have something to test with. The issue I still don’t understand is how do I create a single picking ticket/receiving ticket and receive multiple items under one table, using only 1 line item? In my mind, each picking ticket would come in as a table, but I don’t want hundreds of tables to sort through.

For example, the actual order I am working with has 11 line items that I need to enter into 1 Purchase Order. That purchase order has a PO number, which is the primary key. The “Purchase Orders” table should show 11 items under that primary key, but I have to also show the individual cost, then use formulas for the total cost of the PO.

Once I start receiving, I go to the “Received Inventory” and should be able to enter a receiving record for each receipt I receive until the total PO is accounted for.

Terms & ConditionsAccessibility statement