Sum item for each locations

[Gash]

Hi All,

I am new to Appsheet and try to make it works for our inventory tracking app.
I want to sum items that are located in each location.

There are three main tables: Items, Locations, and Movement.
This is many to many relationship with Movement table as a bridge table.
One item can be in many locations, and one location can have many items.

Items table contains

• ItemID
• Name
• Related Movement

Locations table contains

• LocationID
• Name
• Related Movement

Movement table contains

• MovementID
• item_id
• location_id
• logged_time
• amount
• from_location_id (For tracking)
• to_location_id (For tracking the final location)

I would like to sum item for each location e.g. ItemA has 3 pieces in X, 5 pieces in Y, 9 pieces in Z.
I can only do the total stock of itemA in Movement table but not for each location.

This is an example of Movement table. This is where I logged all the changes.

MovementID	item_id	logged_time	amount	from_location_id	to_location_id
9745ht97h	item-567	12/11/2022, 13:28:01	20	SL-00901	SL-00901
34f34i7r5t	item-567	13/11/2022, 13:28:01	30	SL-00902	SL-00902
34ir7h34ri	item-567	14/11/2022, 13:28:01	40	SL-00903	SL-00903
3497534i9	item-567	15/11/2022, 13:28:01	-15	SL-00901	SL-00901
r34r72ihi7h	item-567	16/11/2022, 13:28:01	-20	SL-00902	SL-00902
4r3o458tjoo	item-567	17/11/2022, 13:28:01	-40	SL-00903	SL-00903

Here is my current formula in virtual column in Items table that is not working. 😢

SUM(
    SELECT(Movement[amount],
    AND(ItemID = [_THISROW].[item_id],
    **This is where I am strucking to get each item locations volume
    )
)

Should I need another column for each item location in Items table?

Any advice or suggestion will be gladly appreciated.
Many thanks in advance.

[Rafael_ANEIC-PY]

Hello there @Gash, your question is nicely explained and very simple to understand, and that makes me very happy.

However, you don't have a expression problem, if you have different locations for each item, and you want to calculate the amount of each item that is present at each location, then one column just simply isn't enough.

For that kind of reporting I suggest you look into document templates on google docs, that way you can generate documents that can list all of your items and their present amounts in each location.

If not, then you could create a virtual column for calculating the amount of each item in each location, and if you do this, I suggest you change your expression a bit:

SUM(
    SELECT(
     [Related Movement][amount],
     [to_location_id]= "location ID 1",
    )
)
-
SUM(
    SELECT(
     [Related Movement][amount],
     [from_location_id]= "location ID 1",
    )
)

I'm assuming you need to consider the items that arrive to a given locations as "incoming" items and the items that leave a location as "outgoing" items, so for getting the correct present amount in any location you're gonna have to do "Incoming" - "Outgoing".

[Gash]

Thank you @Rafael_ANEIC-PY  for help improving my expression. 🙏

I have around 30 locations in total in which any item can be moved freely to any position. With your suggestion, I would need to add around 30 virtual columns to handle every location (and probably more in future). I would like to add this virtual column in Items_Detail view so that our staff can check the current stock count of selected item and its current location.

Is it possible to show the sum of selected item in each location with one expression?

If I need to restructure the whole table or create new table, I am willing to change it to support future scaling.

Many thanks.

[Rafael_ANEIC-PY]

No problem @Gash

With 30 locations it's going to hurt your app a lot to have so many virtual columns, I would not suggest that at all.

---

@Gash wrote:

Is it possible to show the sum of selected item in each location with one expression?

---

Technically you could make an expression that lists each location and the amount of the item in each location in one single massive string, but that one virtual column would single handedly murder the performance of your app.

Do your users really need to know the location of all items in all locations at the same time at all times? and can't be bothered with  creating any kind of document like an inventory ledger? it would be easy enough to implement an user system and show the user the amount in their current location.

How many items are we talking about in your items list?

[Gash]

Unfortunately, my users need to know all items in all locations because they need to check and use and move them. Currently, we have 60 locations and around 700 items

We used to have Excel for tracking things but sometime there is an user error in input so I want to try Appsheet to have less error.

They may have to check it couple of times per day for some items or some locations like when it was missing from its original location. e.g. there are three knives in the shelf A, and later on one knife was move to room B, so we can track their current locations and stock.

Additionally, every user can go to every locations, so I try to create an expression to act like

1. Filter item to be the item they are looking for. (You helped improving on this.)
2. See the total stock of this item from all location (I got this working already.)
3. See the current stock of each item in every location. (This is where I am struggling and you are kindly helping me on it.)
   
   The current location (to_location_id) can be changed or added or deleted and has many places per item. However, when I pull the data, it show only the few latest input, in which not the correct nor updated number as it was not calculated in the detail view, hence I need to have them calculated per location as well.
   

   MovementID	item_id	logged_time	amount	from_location_id	to_location_id
   9745ht97h	item-567	12/11/2022, 13:28:01	20	SL-00901	SL-00901
   34f34i7r5t	item-567	13/11/2022, 13:28:01	30	SL-00902	SL-00902
   34ir7h34ri	item-567	14/11/2022, 13:28:01	40	SL-00903	SL-00903
   3497534i9	item-567	15/11/2022, 13:28:01	-15	SL-00901	SL-00901
   r34r72ihi7h	item-567	16/11/2022, 13:28:01	-20	SL-00902	SL-00902
   4r3o458tjoo	item-567	17/11/2022, 13:28:01	-40	SL-00903	SL-00903

Sorry for if my request is complicated as I am learning  from other Inventory Management design as well.

From the above table, what would be the expression to filtered one item (based on click from before table) and sum stock for each unique locations? This is where make my head hurts. 😥

[Rafael_ANEIC-PY]

---

@Gash wrote:

Unfortunately, my users need to know all items in all locations because they need to check and use and move them. Currently, we have 60 locations and around 700 items

---

Holy cow, it's 60 locations now! 

Well, I have an idea for you, but it's gonna take some work.

Here are the steps for making it:

1. Create a new table called "Product reference list", this is where all the products are going to be stored, but hold on to your seats, this is just getting started.
2. Create a "Product list", which will hold the products that are actually present in each location, this table will reference the "Product reference list", and the "location_id".
3. In your "Movements" table the "item_id" now must REF to the "Product list"

If you have followed these steps, eventually you will have one separate row of each item for each of your 60 locations, and once you have one single row for each item in each location it's fairly trivial to calculate the amount in inventory in each of them.

For example, now you have 60 rows in the "Product list" table all linked to the main item in the "Product reference list" now you can have ALL of the inventory amounts visible from the inline view of the "Product reference list" if you add ONE virtual column for calculating the amount within that inline view, pretty neat huh?

[Gash]

Thank you @Rafael_ANEIC-PY 
Please give me some time to complete your advice.

Please allow me to write down the new tables column to check my understanding.

"Product Reference List" table

1. PRL_ID (Primary Key for this table)
2. item_id (to ref back to Items table)
3. Virtual column (to calculate total amount in a view)

"Product List" table
This is like an reference extension from "Product Reference List" with "Locations" table with another backlink of item_id from "Movements" table.

1. PL_ID (Primary Key for this table)
2. prl_id (to ref back to Product Reference List table)
3. location_id (to ref back to Locations table)

To be honest, I am not fully understand the suggested solution concept yet, but I will read more and try your solution. I am grateful and really appreciated your help. 🙏🏻

[Rafael_ANEIC-PY]

The virtual column should be in the "Product list" table, besides that you got it

[Gash · Accepted solution]

Sorry for the quiet period.

I have tried your solution to the best of my knowledge but I could not make it work. Though I found my solution via using slice of data which I got the idea from this tutorial in Youtube.

Thank you for your guidance.