Stock tracking

[Jill_Holland]

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 ?

[MatthewAT]

I 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? 

[Jill_Holland]

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). 

[TheTimeSavingCo · Accepted solution]

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

[MatthewAT]

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. 

[Jill_Holland]

Thanks Mathew and Timesaving - some good ideas there for me to work on. 

[Wormanza]

The 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. This way, you can keep your physical counts accurate while still letting the system handle adjustments for outgoing orders.