Grouped field with a sum of previous rows

[Petr_Ogurcak1]

Hi, I have in shortcut

• table Products
• table Deliveries with a link to a product and amount of the product we bought in that particular delivery
  and some other tables storing how many products were sold, used, etc.

and I would need to have a Stock table with rows grouped by product with information how many of product from that delivery is still in a warehouse. Simply first in first out.

To do that I would need to know how many pieces I have already bought in the time of a particular Delivery.

For example we have 3 deliveries of 5, 10 and 15 pieces and we sold 17 pieces so I would like to have 3 rows of Product, delivered amount, amount left.

In this it would be
XX - 5 - 0
XX - 10 - 0
XX - 15 - 13

There is 0 pieces from the first delivery, 0 pieces form the second delivery and 13 pieces from the last delivery.

Does it make sense? Any idea how to do that? The problem is how to calculate that after fisrt delivery we have bought altogether 5 pieces, after second delivery 15 pieces and after third delivery 30 pieces altogether.

[Petr_Ogurcak1]

There would be an easy solution if there are functions for array like array_search.

[Damon_Talbot]

This may help you:

New BETA Feature Grouping

[Petr_Ogurcak1]

Thanks, Damon, I know about it, actually I’m using it in that table but id doesn’t help me with the calculation of pieces left in the stock. Or does this grouping brings some new formula?

[Arlo_Haskell]

Petr this sounds similar to a problem I had. Our customers make an initial deposit to hold an item, then make one or more balance payments later to complete their transaction. Our problem was how to show a running total of balance due for each transaction. I solved this by creating a “Primary Transactions” table and a separate “Sub-transactions” table. So the first payment gets recorded in Transactions, establishing a Transaction ID. All subsequent payments get recorded in Sub-transactions, with a reference to the original Primary Transaction via a Transaction ID linked field. As long as you link all the Subs to the correct Primary, you will be able to keep a current count/sum of all related transactions. Something like this might work for you. Sorry I don’t have time to explain in more detail right now – it’s a complicated table with almost 50 fields, lots of formulas from one table being looked up by the other table to make all the numbers work out – but I hope this points you in the right direction.

[Petr_Ogurcak1]

Arlo, I understand what you mean. Actually, we have it as well. My problem is how to calculate SUM of all previous transactions linked to one product (customer at your db) for every transaction.

customer x
transaction linked to x
transaction y1 - 10 pieces
transaction y2 - 8 pieces
transaction y3 - 5 pieces

I need to have
transaction y1 - sum(10)
transaction y2 - sum(10,8)
transaction y3 - sum(10,8,5)

[Arlo_Haskell]

Petr, I put together a basic example base that I think does what you’re looking for. You can see it here:

This has 3 tables – Products, Deliveries, and Sub-Deliveries

The first time you receive a delivery of a product, you record it in Deliveries. For any subsequent deliveries of that product, you record it in Sub-deliveries, making sure to link it to the correct “Parent Delivery.” The quantities of all deliveries related to the primary are added together and displayed in both Delivery and Sub-delivery tables thanks to a combination of formula, rollup, and lookup fields. You can view the total quantity delivered from either table.

Note that I’ve created Delivery ID that concatenates the product name and delivery date. You need to attach all sub-deliveries to this ID to make the calculations work. You might want to add supplier name to make it easier to search for the parent delivery.

In this simplified version, you can only record one product type per delivery. It is possible to add more formulas to log multiple product types in each delivery and provide running sums of each.

I’m not sure if this is the best way to solve this issue, but it’s the one I’ve been able to figure out. I’d be curious to see other examples from anyone who’s found another approach.

[Petr_Ogurcak1]

Many thanks, Arlo, we’re getting closer. Perhaps I could rethink the concept. But It should be simple for my non-technical colleagues.

I created simple base to show you what I need to do https://airtable.com/shrGv4VthYG6JcIgI

We need to be able to see how many pieces of product we have in stock with a particular best before date.

So in table Stock I need a SUM of previous deliveries.

[Petr_Ogurcak1]

Simply I’m looking for a way how to track First In First Out stock.
So I have a product with 3 deliveries - first for 6 items, second for 8 items and third with 5 items.
It’s 19 IN and I sold 10 pieces. We need to know which 9 items are in stock. It’s 5 from the last delivery, 4 from the second and none from the first.

[Arlo_Haskell]

To get the Sum of associated deliveries, I think you’ll need the deliveries/sub-deliveries model I talked about earlier. That will let you establish a primary delivery record to which you can attach all secondary deliveries.

You also need to link your stock table and your sales table to your deliveries tables. Then you can use a lookup field to show delivery date for all items in stock, and see delivery date when making sales. You can also use a formula to concatenate the delivery date with stock quantity, and then a rollup to display that info, so you know how many items you have from each delivery.