Complex aggregation in multi-warehouse app

There’s a problem I am facing that makes me ask if appsheet can solve it.
I have a multi-warehouse situation.
An item can be moved from one warehouse to another.
I have a registry table that keeps track of all items moving from one warehouse to another.
Therefore if I move 3 pieces of item A from warehouse w1 to warehouse w2 I will have a row like this:
id item from to quantity
1 A w1 w2 3

now… the rough problem… is there a way to show or calculate for each item the quantity of items present in each warehouse?

It’s easy algebra (for each item and each warehouse sum the quantity in and subtract the quantity out) and programmatically is very easy to achieve this. Here I am stuck.

1 Like