Adding/Reducing Inventory


We use a product by the gallons. I get it in 50-gallon drums.
CUSTOMER SHEET> When I enter a customer, I want to say “This customer needs XX Gallons”

INVENTORY SHEET > Have a running total of “GALLONS NEEDED” that is a SUM of “This customer needs XX Gallons”

When I get more 50 -Gallon Drums in, it will add to the inventory. When I use XX Gallons it will subtract from inventory. IF inventory is < 100 Gallons, order, IF Gallons needed is > IN INVENTORY order that amount + the extra 100 gallons to keep in inventory.


What process do you envision using for updating the customer table? For example, say Customer A comes to you and needs 20 gallons, so you add a record for Customer A (assuming one doesn’t exist already), with 20 entered in a field like {Gallons Needed}. Once that customer’s 20 gallons are delivered from your inventory, how do you plan on updating that record? Will you change 20 to 0 in that customer’s {Gallons Needed} field, and reuse that same record the next time Customer A needs more? Do you want to check a box (or enter a delivery date) to mark those 20 gallons as being delivered, with a plan to make a brand new record the next time Customer A needs more? The method you plan to use will affect how the calculations on the Inventory table are driven.


I made an example that gets you most of the way there:

Clients order a number of gallons in the [Orders] table, you record every drum of “paint” you receive in the [Inventory] table, and the [Items] table includes some rollup/formula fields to get you a running total of stock versus ordered quantity.

1 Like

We install Spray Foam Insulation. When we schedule a job, I need to input XX Gallons needed for this job. All of the scheduled jobs would need to have a SUM TOTAL so I know how much to order for the next two weeks. My Material comes in a 50 Gallon drum.
I also want to track to each job ACTUAL GALLONS * XX for a Job cos of XX