Stock management in multiple locations

[allanfocker]

does anyone knows how to make this?

whenever I add a new Product Variant, the bot / automation should create an inventory item copying some info of the product variant, then create a Inventory Level for all existing Locations (even if the location has 0 stock for this  Product Variant on those Locations) and keep the inventory levels related to the inventory items and Locations.

 

Product Variant: All product variants have a 1:1 relationship with their associated inventory item. Each product variant has one inventory item, and that inventory item belongs only to that product variant.

InventoryItem: Contains information about the physical product, such as its SKU, as the back-end information used for managing inventory. Inventory items are associated with one or many inventory levels. An inventory item will have an inventory level for each location where the item is stocked

InventoryLevel: Represents the actual quantity of an item that is available. Inventory levels connect one inventory item to one location. Each inventory level holds the available quantity for its inventory item at the associated location.

Location: Represents a geographical location of the point of sale.

To show the current stock of the inventory level I use a Virtual column that calculates the current stock:

SUM(SELECT(Order[Quantity], 

AND( [Location ID]= [_THISROW].[Location ID], [Inventory Item ID]= [_THISROW].[Inventory Item ID] )))

Thank you very much to all the google appsheet comunity!!

[TeeSee1]

I am not sure how much of this you have already created but to do

---

@allanfocker wrote:

Meaning that records on inventory level and inventory items should be deleted when product variant is deleted as well.

---

you need to set is a part of flag (reference doc) on edited: inventoryItem productItem -> productVariant and inventoryLevel -> inventoryItem productItem

[allanfocker]

 

 

Thank you for your response, im stuck at the part of triggering this when a new product variant is added by user:

• Step 1 Copy the id variant, cost and tracked? columns from the product variant to a new row in the inventory item table
• Step 2  add into the inventory level table a row for every location referenced to inventory item

Currently works like this: 

ajuste means adjustment 

 

but lacks of automation, if i add a new product variant it wont add by it self the inventory  levels for each existing location

[allanfocker]

 

[TeeSee1 · Accepted solution]

To copy a product variant to an inventoryItem is straight forward.

To create inventoryLevels from the newly created inventoryItem requires a modified set of steps described here. Here your enumlist will be a list of all the locations.