Aug 10, 2022 03:48 PM
I am pretty new to AirTable, and I was wondering if anyone has advice on designing an inventory base/ tables for a small business. Currently, I have three tables for inventory - employee equipment (IT Equipment), supply closet inventory, and our meeting room inventory. Each of these tables features the item’s name, brand, description, quantity, unit type, price per unit, link to purchase, location, and the date of last purchase. I also have a table for a shopping list where employees can submit a shopping request.
What is the best way to design a base like this where I can easily track, uniquely identify, and organize each item? I am new to office inventory keeping and wanted to hear about your best practices.
Additionally, I am trying to figure out a way to add a checkout option for employees looking to use certain equipment and a way to track how many times something has been purchased.
Any help is greatly appreciated.
Aug 10, 2022 07:54 PM
Hi Chloe, I think if I were you I would combine the
Employee equipment (IT Equipment),
Supply Closet Inventory and
Meeting Room Inventory into one table, and use a single select field to set their item types
That way you don’t have identical fields in different tables tracking the same types of data, and we can create views to only look at
Supply Closet items, for example
For checking out and purchasing, I would suggest a single
Movement table that tracks all equipment purchases, check ins, and check outs
Quantity for Rollup field changes the
Quantity value for
Check Outs into a negative value so that, when we add the values up later, it makes sense)
We can then create a rollup field in the
Inventory table called “Quantity” to track the quantity in stock automatically after accounting for check ins and check outs
Something else I would suggest is creating a view that will only display items that are in stock, and having a check out form that limits record selection to that view.
That way you won’t have users checking out things that aren’t in stock at all. (This doesn’t stop people from checking out 5 things when there’s only one thing available though)
And finally, I would suggest changing the primary field of the
Inventory table to a formula that displays the item name followed by the quantity available
This way, when users are checking things out, they can see how much stock is available and fill out the form accordingly
Here’s a link to the base, and here’s a link to the checkout form
To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button
Happy to answer any questions you have!
Sigh, after doing all of this I realized I was reinventing the wheel. You should check out the Airtable Universe where people put up templates for this kind of thing. Here’s a link to it where I searched for “Inventory”
Aug 10, 2022 09:45 PM
This was incredibly helpful, Adam! I truly appreciate the time and effort you put into your answer. I will definitely redesign my existing base with your tips and techniques. Thank you so so much! :blush: :pray:
Aug 11, 2022 11:03 AM
regarding quantity, how can I edit this? I have tried inputting a number, but I am not allowed to change the quantity on the inventory table.
Aug 11, 2022 12:00 PM
I have tried inputting a few new records. Since quantity is a rollup field, does this mean the only way to manually update the quantity is by submitting a form that “checks out” or “purchases” the item?
Additionally, I have added a shopping requests table to my base. How can I make sure the record ends up in the inventory table when the item is clicked as “purchased”? I have done an automation to autofill the inventory table before, but I am unsure since the inventory table has rollup as quantity.