We are using Airtable to organise our plant nursery. We need it to track the stock of plants in the nursery and match to orders etc. Its a bit different to normal stock tracking where we might track "current stock" by adding to stock when we get in stock and subtracting as orders go out because we have to grow the plants from seed and there are often plants dying, not being good quality etc. So we often have to go out and count the actual plants in good condition on the tables to get an accurate idea of stock count. But we also want to be able to subtract from the stock count automatically as stock is sent out. So we have a conflict between what we want to enter as our current stock levels - if we have a calculation in the current stock field - auto subtracting deliveries - we can't simply type in a number when we do a manual stock count. Any suggestions on how to resolve this ?
Stock tracking
+2Best answer by TheTimeSavingCo
This is doable but gets pretty weird so bear with me!
As you've mentioned, you count your plants to get a stock count, and so maybe you start off with 100, and then the next day you do another count and find 15 dead, so now you have 85 plants left
After that, you send out 3 of said plant, and you want the number "82" to be calculated for you from your base
The idea is to have a "Movement" table where you log stock count updates as well as sending stuff out, and you'll just select the type of update as you create the record like so:
Resulting in this:
To get to this point, we use a rollup field with the formula `MAX(values)` to find the most recent "Count Update" record linked to Plant 1. Once we've done that, we can then pull the correct stock count number over to our main base via a conditional lookup
After that, we use another rollup field to get a sum of all of the "Out" records linked to Plant 1, and deduct it from the correct stock count number
Like I said, it gets...pretty weird. Here's a link to the base so that you can see exactly how everything's set up, and you can duplicate it to view the formulas and such
+3I would have 3 sheets, Plants (the various species of plants you offer), Inventory (the individual pots you have planted), Sales (each sale record you make)
Inventory: I would have a record for each pot, which you can tag with a QR code. I would suggest adding a Single Select field which has the various stages of a plant. So let's say Seedling, Mature, Dead, and Empty. and a link to both Plants & Sales. Then I would have a view that filters pots that have a Stage of mature and Sales is empty. You can also group your inventory into Plant, then Stage to show how much you have of each.
Sales: this would have a list of your individual transactions including the price, client, payment, etc… It would link to the filtered Inventory view you made above.
Plants: this would be a simple list of the plants you offer.
Makes sense?
+2I would have 3 sheets, Plants (the various species of plants you offer), Inventory (the individual pots you have planted), Sales (each sale record you make)
Inventory: I would have a record for each pot, which you can tag with a QR code. I would suggest adding a Single Select field which has the various stages of a plant. So let's say Seedling, Mature, Dead, and Empty. and a link to both Plants & Sales. Then I would have a view that filters pots that have a Stage of mature and Sales is empty. You can also group your inventory into Plant, then Stage to show how much you have of each.
Sales: this would have a list of your individual transactions including the price, client, payment, etc… It would link to the filtered Inventory view you made above.
Plants: this would be a simple list of the plants you offer.
Makes sense?
Thanbks Matthew but I don't see how this would work. We grow 200,000 individual plants/pots per year so having a record for each plant would be overkill. Some species we grow only 100 of but some we grow 5000. We have about 300 different species and each batch/species is located on a bench in one area, with a barcode to identify that batch.
WE do keep track as you mention of how many are seeded, small, poor quality, dead and good quality. Its the good quality number that is available for sale that we mainly need to track and match against our orders. Each order (in a separate table) lists how many of each species is ordered, so we can ensure we have sufficient stock to fill al the orders. The species in each order rolls up to match against the species stocktake (good quality).
+31This is doable but gets pretty weird so bear with me!
As you've mentioned, you count your plants to get a stock count, and so maybe you start off with 100, and then the next day you do another count and find 15 dead, so now you have 85 plants left
After that, you send out 3 of said plant, and you want the number "82" to be calculated for you from your base
The idea is to have a "Movement" table where you log stock count updates as well as sending stuff out, and you'll just select the type of update as you create the record like so:
Resulting in this:
To get to this point, we use a rollup field with the formula `MAX(values)` to find the most recent "Count Update" record linked to Plant 1. Once we've done that, we can then pull the correct stock count number over to our main base via a conditional lookup
After that, we use another rollup field to get a sum of all of the "Out" records linked to Plant 1, and deduct it from the correct stock count number
Like I said, it gets...pretty weird. Here's a link to the base so that you can see exactly how everything's set up, and you can duplicate it to view the formulas and such
+3Thanbks Matthew but I don't see how this would work. We grow 200,000 individual plants/pots per year so having a record for each plant would be overkill. Some species we grow only 100 of but some we grow 5000. We have about 300 different species and each batch/species is located on a bench in one area, with a barcode to identify that batch.
WE do keep track as you mention of how many are seeded, small, poor quality, dead and good quality. Its the good quality number that is available for sale that we mainly need to track and match against our orders. Each order (in a separate table) lists how many of each species is ordered, so we can ensure we have sufficient stock to fill al the orders. The species in each order rolls up to match against the species stocktake (good quality).
In that case, I would convert the Inventory from pot to batch number, which includes a number of columns.
- Qty Mature
- Qty Seeds
- Qty Dead
- Qty Poor
- Qty Sold (Roll Up of Qty in the Sales sheet)
- Qty Out (Formula, Sum of all above)
- Qty Planted (number)
- Qty Available (Formula Qty Planted - Qty Out)
Again in the Sales sheet I would have a link to Inventory (filtered where Qty Available > 0).
I also like TheTimeSavingCo's suggestion of counts.
+2The manual field could be your “baseline” stock count. Whenever you do a stock check, you update this field. Then, a second calculated field can take that baseline and subtract orders or other changes automatically.
For orders, you’d link them to your stock table so that as you log sales, it automatically subtracts from the calculated field. You could also use tags for each batch (like barcodes) to match stock with specific orders, ensuring everything aligns.
If you’re looking to streamline this further, platforms like https://blackeaglefg.com/routing/ can offer tools to help manage workflows like these, especially when dealing with high volumes of data and needing precise tracking.
+1Last year, I had to automate inventory tracking after a stock audit in November showed a shortage of 312 units. We used to manage everything manually in Excel, but that didn’t prevent mistakes. In January 2025, we started using https://pythonrpa.org/ set up an integration between Google Sheets and our warehouse system: we only update the baseline values manually, and then the bot automatically subtracts shipments, checks by batch, and flags any discrepancies. In February alone, it caught a mismatch on 18 items before they were even sent to the client. So, as you can see, I managed to prevent items and money loss with that decision. I hope my boss appreciates that .
+1You could set up a dual system with an "Actual Count" field that you update manually when physically counting plants, and a separate "Calculated Stock" field that automatically deducts from the Actual Count as orders go out. When you do a physical count, just update the Actual Count field, and have a simple automation that resets the Calculated Stock to match that number. We actually faced this exact problem with our seed inventory where germination rates mess with theoretical counts. I'd recommend adding a "Last Count Date" field too, so everyone knows when the last physical verification happened. For extra visibility, maybe create a "Discrepancy" calculation that shows the difference between your last physical count (adjusted for subsequent sales) and what you'd expect. This approach gives you the best of both worlds - accurate manual counts when needed plus automatic deductions.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.