Track Inventory Locations per Item movement

[ted_ellis]

Hi, 
I'm using Airtable as my Main inventory.

PROBLEM:
Currently, I assign item location on a per unique SKU or product basis -  the problem is, I cannot track how many item is assigned to the location.

For example TshirtA has 3 quantities, all I can see right now is it is assigned to Box 1 and Box 2 but I don't know how many of this item is inside Box 1 or 2.

GOAL: 
I want to be able to have a field in MAIN INVENTORY table where I can see a sum of how may quantity is stored per location. 

Example 
TSHIRTA, BOX 1 = 3pcs, BOX 2 = 1pcs

I have 3 relevant tables for this problem.  Main Inventory, Item Locations and Inventory Movements.



Item Location is self explanatory, Inventory Movements is where I record the + or - of the stock quantity whether it's a stock allocation or a shipping. 

I thought the solution to my problem is assigning the location in the Inventory Movements table, this way, I can track the location on every new item that comes and goes.... then link this to MAIN INVENTORY table > ROLL UP based on location (SUM)... somehow I am stuck at the part how to show the quantity linked in the MAIN INVENTORY table to the corresponding ITEM LOCATION. 

My head is almost exploding... I hope you can help me 

[Stephen_Orr1]

Hi @ted_ellis,

I'm assuming the following structure: Main Inventory is a unique list of SKUs, Item Location is a unique list of locations, Item Movements is a combination of SKU and Location (ie, a junction a.k.a cross-reference table) where every record is a quantity increase or decrease.

If so, in Main Inventory create 1 roll up field per location quantity as such: roll up the Quantity field in Item Movements with sum(values) as the aggregation function and the conditional logic where location = the name of the current roll up field (so Box 1 Quantity has the condition where location = Box 1). Do this for all locations. 

Then create a formula field to combine these roll-ups into one field:

REGEX_REPLACE(
    IF({Box 1 Quantity}, 'Box 1: {Box 1 Quantity}, ', '') &
    IF({Box 2 Quantity}, 'Box 2: {Box 2 Quantity}, ', '') &
    IF({Box 3 Quantity}, 'Box 3: {Box 3 Quantity}, ', '') &
    IF({Box 4 Quantity}, 'Box 4: {Box 4 Quantity}, ', '') &
    IF({Box 5 Quantity}, 'Box 5: {Box 5 Quantity}, ', '') &
    IF({Box 6 Quantity}, 'Box 6: {Box 6 Quantity}, ', '') &
    IF({Box 7 Quantity}, 'Box 7: {Box 7 Quantity}, ', '') &
    IF({Box 8 Quantity}, 'Box 8: {Box 8 Quantity}, ', '') &
    IF({Box 9 Quantity}, 'Box 9: {Box 9 Quantity}, ', '')
    ,
    '[, ]*$',
    ''
)

 Hide the roll ups and the formula field should be what you're looking for.

[ted_ellis]

I have no knowledge about how REGEX work so just to confirm, I will need to create a new roll up field similar to the photo below? I have 150 item locations.. seems like a lot of work so I want to make sure before I spend some time to test this.

[Stephen_Orr1]

Your screenshot is correct. Oh wow that is a lot of locations 😅 I would test everything with a few first before doing them all. Regex_replace is not entirely necessary, I'm just using it here to remove the trailing comma that the formula generates.

There might be a better way to do this.... I'm still thinking.

[Stephen_Orr1]

If you group by Inventory (the linked record from Main Inventory) and also group by Item Location (the linked record from Item Location) in the Item Movements table, and use the Sum summary function in the groups, you should be able to see the quantity of items per location... the tricky part is getting it in the Main Inventory table without a script.