App Sheet Inventory Management

[BillyPanaggio]

Hey all, I'm trying to create an inventory management system, and am running into a few road blocks. We would like to be able to accomplish the following within app sheets.

Bring in inventory to a specific location

Transfer inventory out of location to be sold

I am currently having trouble with getting app sheet to display the inventory in  the location, however the formulas to calculate total inventory, inventory in, and inventory out are working well. Currently I have the following sheets:

"Item Inventory Data" - Barcode, Image, Item Name, Item Category

"Warehouse Location Data" - Location ID, Location Name, Item Name, Item Quantity (this is where I'm pulling my "Initial Stock" from)

"Inventory In" - Timestamp, Barcode, Quantity, Location Name, Location ID

"Inventory Out" - Timestamp, Barcode, Quantity, Location Name, Location ID

I can add and remove inventory using a form in app sheet, and the current inventory will change, however when I look at the specific location I put it in the item name and quantity aren't displayed until I drill into the location. Currently being displayed under "Related inventory Ins"

Any help would be greatly appreciated! Thanks 

[TeeSee1]

You can try 

[Item Quantity]
+
SUM(
 SELECT(
  Inventory In[Quantity],
  AND(
   [Location ID] = [_THISROW].[Location ID],
   [Barcode] = [_THISROW].[Barcode]
  )
 )
)
-
SUM(
 SELECT(
  Inventory Out[Quantity],
  AND(
   [Location ID] = [_THISROW].[Location ID],
   [Barcode] = [_THISROW].[Barcode]
  )
 )
)

This is based on the assumption that you have [Barcode] in the Location Data table.

If [Barcode] is your Item "Key", then by specifying [Item Name] as the Label, you only need [Barcode] in your Location Data table because {Barcode] will be replaced by [Item Name] when displayed in your view (underlying spreadsheet keeps the key value)

[BillyPanaggio]

Thanks TeeSee1, If I'm reading the expression correctly that would be used to calculate total inventory? I am able to calculate that, where I am having issues is with the UX display. Please see screen shots below.

If I drill into PG7 I can see there is malt there, but it is not displaying in my Warehouse Location View.

[TeeSee1]

If I understand your current configuration correctly, you seem to modify your Warehouse Location table slightly.

It should be something like

WLD:

• ID: this is either a generic ID or a Natural Multi-column key that should be unique per  Location and Item combination
• Location ID: Ref to Location Table (I assume you do not have this. It makes more sense to create this separate from Warehouse Location Data table)
• Item ID/Barcode: Ref to Item Table (Not listed in your original post, but I assume you have this)
• Item Quantity: Initial Quantity per Location/Item. This does not change ever unless you introduce the concept of period where you update this with the period's initial value
• Current Inventory (VC): This is where you set the expression I posted earlier.
• Other columns like descriptions as you see fit.

Item Inventory Data holds the total inventory for the Item.

WLD holds inventory for the Item at a particular location.

I hope this clarifies what I am trying to say. (I could have completely misunderstood you,,,)

[BillyPanaggio]

Good Afternoon TeeSee1 I have made the adjustments you have suggested, but am still having the same problems with the display. When I bring inventory In to lets say PG7, which is currently empty the information doesn't populate in WLD. It is only when I drill into PG7 the sheet tells me that there is inventory in "related inventory ins".