I’m building an inventory system for a large tool trailer for a non profit. In many of the examples I’ve seen on this forum, they total all transactions (reductions and additions) in the transactions table and subtract that from the inventory table to get an on hand quantity.
I want to have a transactions table where people check out tools, and then check them back in after the job is complete. I need to update the on hand value in the inventory table each time a record is added, reducing the “on hand” in the inventory table by the total quantity of that tool that was checked out (they may take more than one of a specific tool for a job). I want to do the reverse when the tool(s) are checked back in. I’m having trouble with the actions formulas to make that work. Thanks for any help on this!
Hi @NCBOM ! This is not your typical “consumed” Inventory where you get items in from Purchase Order and then out based on sales.
Instead, it seems more like a Rental Inventory where the items are somewhat static. You may have old items that are discontinued from use and a need to add new items, but otherwise the inventory stays the same. You probably want to track these things differently than in a typical sales app.
I would recommend tracking EACH item with its very own record. Then you can mark THAT record as “Available” or “Checked Out”. You can then use barcodes for easy app entries. Each item gets its own barcode on it and then users of the app only need to scan the barcode and choose “Checked In” or “Checked Out”.
Additionally, this will allow you track individual details such as serial and/or model numbers as well as taking items out of service for things like “Damaged”, “Needs Repair”, “Needs Replaced”, etc.
I would even apply this to things such as hammers, screwdrivers and crowbars!!
With this setup, you can still show things like number of Hammers available but then can show which specific hammers.