Calculate Multiple Select Values

Welcome to the community, @Will_Bailey1! :grinning_face_with_big_eyes: Airtable formulas can’t easily extract multiple values. Technically it could be done with a series of formulas, but that would just be messy. One option is to write some code in the Scripting block, which can do the work fairly efficiently and output the final value to another field.

Could you share more about the setup of your base? How many possible values are there? What do these values represent? There may be another way to tackle it, but knowing more about the setup and your end goals will help me know whether or not that’s a viable solution.

Hi Justin, thank you for your quick response!

I’m running a base that manages distribution for a company. This base consists of the customers orders with the details of the boxes that are being sent out which corresponds to how much the overall delivery will cost.

We’re trying to keep stock of the boxes but at the moment it’s not working. For example, we have 10 Box 1’s. We then get orders and have to send 8 of those Box 1’s out meaning that the Stock Remaining column for Box 1 shows as ‘2’.

We then get more stock come in to the warehouse, another 10 Box 1’s let’s say, however when the admin team go to update the Stock column, they have to add together the previous stock and the new stock so that they get an accurate reading in the stock remaining column because that also links to the total number of Box 1’s ordered (this is fine to start with but it will continue to increase and increase and may become confusing).

So what I’m looking to do is have some form of running total that will make the admin team’s life as easy as possible. When stock is replenished, simply check how many Box 1’s have come in as stock, enter that number somewhere, each time that then gets added to a stock running total which deducts the total number of orders and gives out the Stock Remaining so that it is clear when stocks are low.

I hope that mostly makes sense, please let me know if there is anything to clarify in case it doesn’t!

That definitely clarifies the big picture of your base. However, your original question was about adding values in a multiple-select field, and you didn’t mention which field in your setup is the multiple select. However, my gut tells me that you might be using a multiple select field in a way that’s not optimal for your use case. If you’ll indulge me, this is how I would consider setting up your base. To keep track of inventory, I would use the following tables:

1. [Products] - This would be where you track the basic info about each product/box (perhaps call it [Boxes] if everything truly is just a box)
2. [Line Items] - Each record here would link to a single box and a single order, and record the quantity purchased for that box type.
3. [Orders] - Where all orders are recorded. Each line item from the previous table would link to one order, so that each order would then tie to one or more line items. A rollup field could be used to give you a total for the entire order, with other fields storing other order-centric data.
4. [Receiving] - Where all incoming inventory is recorded. Each shipment likely contains multiple box types, so each type would be captured on the next table and linked to this one. This table is effectively the incoming equivalent to the [Orders] table.
5. [Receiving Items] - I really don’t know what to name this table, as I don’t work in your industry, but this is my best attempt for now. Conceptually, it’s the incoming equivalent to the [Line Items] table.

When inventory arrives, a record is added to [Receiving] recording general info for that entire shipment. Say you get 100 Box 1’s. On the [Receiving Items] table, you add a record linking to that new [Receiving] record, and also linking to “Box 1” on the [Products] table, recording 100 units received. Continue adding more records to [Receiving Items] for other box types in that same shipment. Over in [Products] you can roll up the numbers of all units received to see your current inventory for all types.

An order comes in for 8 Box 1’s, so you make a new order record in [Orders], then a new record in [Line Items] linking to that order and also to “Box 1” in [Products], indicating 8 units ordered. In that “Box 1” record in [Products], you can now roll up all units ordered from all of the linked [Line Items] records. Add a formula field to subtract that from the units received, and you have your actual inventory. In this case, you’re left with 92 units.

The next time you get more inventory, you repeat the process above to record those incoming units. Let’s say it’s 50 this time. Thanks to the rollup field aggregating all incoming and outgoing inventory for each box type, the “Box 1” record in [Products] shows that a total of 150 units have arrived, and 8 went out, so you’re left with 142.

With this arrangement, all you need to record are the actual numbers coming in and going out. The rollups and formula on the [Products] table do the heavy lifting of processing those values to show your actual inventory at any given time.

Does that help?