Best combo of features for ideal inventory base

Hello hive mind,

Do you hate it when someone knows just enough Airtable to want to do something complicated, but not enough Airtable to build it without asking for help? Sorry, I’m about to do this exact annoying thing. :grimacing:

I have a zillion bases for grouping/sorting at will, but calculations/rollups/etc are my weak spot, and I haven’t learned interfaces yet. I admit this base is out of my wheelhouse, but I’m determined to learn in order to help this charity. I am happy to do my own homework once I know what terms I need to look up in order to learn the relevant features.

Here’s my context:
I am building an inventory base for a pregnancy center non-profit. Everything has to be on the free plan because they have a very slim margin. Each entry will be a category, not individual items, so we can keep it under 1200 entries. I can do whatever is necessary in the base itself, but what the end user (the director) will see is only a form for inventory items going in and out that auto-updates the category totals, and an interface to view the category totals and conditional alarms for low inventories.

What airtable functions do I need to learn, in order to create the following user experience:

  1. Auto-update totals by entering new inventory in or out in a simple form, without having to count inventory and manually update the totals in the tables. For example, the director receives a donation of 3 carseats. She opens the form, selects Category:Carseats, types the number 3, and hits Enter.
    –I know I need to set this up in the tables before creating a form, but I don’t know what the formula is to auto-update a total.

  2. Then she opens the category interface to see if she has enough carseats for now (either a percentage of an ideal, a total, a conditional return that says GOOD, whatever, etc). She can see there are now 8 carseats total in inventory, which is enough to not have to go buy any more with their meager funding. But she can see immediately that they are critically low on formula, so they’d better spend the funds on that.
    –Can conditional returns show up in an interface? How would I do that?

–What formulas and features do I need to go learn in order to create this base for the center?

Please indicate what the formula or feature will relate to in my process or tables (eg: to show a conditional result in an interface, you need to search the term “x”, etc). If you already know the formulas, please link me to the discussion or help page if you don’t want to type it again.

As always, thanks for your help everybody!

Hi Mary, apologies, I feel like I’m missing something as I’m not quite sure what you’d like the interface to display

I’ve put something together here for you that does what you’re looking for otherwise, I believe?

Form itself:

Form submissions:
Screenshot 2022-08-01 at 4.59.02 PM

Categories table:
Screenshot 2022-08-01 at 5.00.55 PM

It works by asking the user to select the category and then rolling up on it as needed. The Status field’s a formula field with a SWITCH() in it that we’ll have to customize based on when the director wants to be alerted to something

Lemme know if you’ve got any questions!