Inventory Tracking - total left in stock

[Tracy_Standish]

I want to track when a person takes stamps, and display the total stamps we have left. For example, We have 200 1st class stamps. Courtney takes 5 1st class stamps. The new stock is 195. Bridget takes 5 stamps. the new stock is 190 1st class stamps.
Also tracking global stamps, and ‘over 1oz’ stamps.
I need to track who takes what, when, and how much we have left.
Easy to do in excel, not so much here.

[Justin_Barrett]

Welcome to the community, Tracy! :grinning_face_with_big_eyes: I can definitely help you set up a base that tracks those pesky stamps. I’ll send you a PM so we can talk details.

[Julian_Kirkness]

Hi @Tracy_Standish

This is actually very simple to do in a database - first you have a Stamp Stock Table like this:

and a Stamps Used table:

The clever bits are the 2 calculated fields on the Stamp Stock table - the first Quantity Used is a Rollup field from the connected Stamps Used Table:

and the second, Remaining, is a simple formula:

To do stock control properly, you would normally have means of adding stock items as well - I’m not sure if you need this or whether you would just add to your Stock Quantity field - or another option is just to enter a negative entry into Stamps used with a name of Stock replenish or something.

To build a more complete solution you may want to add a New Stock table and have the base calculate Stock itself using a similar rollup field from that - but it may be overkill.

Hope this helps.

Julian

[Tracy_Standish]

Julian, thank you so much for trying to help. I cannot seem to recreate what you have here. Would you be so kind as to talk a look? May I contact you to share a link?

[Tracy_Standish]

https:// airtable .com/invite/l?inviteId=invbl7yxeE0Bcuz7O&inviteToken=e9e779f19e05b0cf14ce3b47a8f042f0065a9f9cfb70db0a3753099f0a7f2de6

[Julian_Kirkness]

Hi Tracy - the link didn’t seem to work - you can send me a link at julian@kirkness.com (or invite me).

[MONSHULS_for_ge]

Hi @Julian_Kirkness!

I’m new to Airtable.
I’d also like to create a doc to keep track of my stock.
I was trying to set it up as you suggested here, but I’m stuck :sweat:

My problems:

1. I can’t seem to do the rollup thing. It keeps telling me I “need a link field to create a rollup. Create the link field before configuring this rollup field.” I already have both tables, but still can’t seem to make it work.

2. I was thinking it would be cool to have within the same database, a table with all my product names and SKU # to use as a master table, and to be able to then, in the other tables, grab the info from there. Ex. I choose product 1, and immediately, its SKU and other info appears in its place. Is this possible? How?

3. In the SOLD PRODUCTS table, I’d like the main column to be the SALES CHANNEL (brick-n-mortar, e-commerce, trade shows, social media). How can I make it so that it allows me to choose the channel like I’ve set it up in the other tables in this doc?

4. How can I also create this doc so that I “have means of adding stock items as well”?

I would really appreciate all your help. I discovered Airtable just recently, and think it is a fantastic tool, but still find it a bit confusing and hard to use :grimacing: I would really love to learn how to use it so that it can become my day-to-day tool.

Looking forward to your reply!!!

Thanks in advance!

[KUDU_NonProfit]

Julian, I know this is an old thred, but I have a quick question. I completely understand the tables and fields you’ve shared, but what I don’t know is how to track the quantity of different items taken (stamps in this example). Do I need a Quantity field for each unique item in the “Stamps Used” table? If so, does that mean I also need a “Quantity Used” rollup field in the “Stamp Stock” table for each unique item.

For example, Cortney takes 12 first class stamps, 10 international stamps and 3 “over 1oz stamps” in one trip to the stock room. I would have a form Courteny would use where items taken is a multi-select field, then based on what she selected, the form would ask her for the quantity of each item. What is the easiest way to set this up with the fewes number of fields?

Thanks,
Dave from KUDU