Help

Small Office Inventory

Topic Labels: Base design
2150 4
cancel
Showing results for 
Search instead for 
Did you mean: 
ChloChlo
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello,

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.

4 Replies 4

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

Screenshot 2022-08-11 at 10.33.34 AM


For checking out and purchasing, I would suggest a single Movement table that tracks all equipment purchases, check ins, and check outs

(The 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)

Screenshot 2022-08-11 at 10.43.27 AM

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

Screenshot 2022-08-11 at 10.47.06 AM


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)

Screenshot 2022-08-11 at 10.47.39 AM
Screenshot 2022-08-11 at 10.39.38 AM


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
Screenshot 2022-08-11 at 10.52.05 AM

This way, when users are checking things out, they can see how much stock is available and fill out the form accordingly

Screenshot 2022-08-11 at 10.52.15 AM


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”

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:

ChloChlo
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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.