Update Amount of item in each location

AppSheet AppSheet Q&A
1

I am building an inventory management app. I need a function for a check in and check out transaction. For each Item, I want the sum quantity amount of all the items and where they are located.

The items should have a column that update constantly with items are being checked in and out based on Item ID.

Any help is appreciated!

Under “Total Stock Available” as a virtual column in “Items” I have a formula for updating total amount of items by subtracting Check in items by check out. It is currently not subtracting the amount and stays at zero:

SUM(

SELECT( Inventory[Amount], [Item ID] = [_THISROW].[Item ID],[Status]= “Check In”) )

  • SUM(

SELECT( Inventory[Amount], ([Item ID] = [_THISROW].[Item ID]),[Status]= “Check Out”) )

My code I currently have updating amount for location:

SUM(
SELECT(Inventory[Amount],
AND([Location ID]= 30004,[Status] = “Check In”, [Item ID] = [_THISROW].[Item ID])))

David

2

Can you share us how you have your tables set up?

You apparently have Inventory.

What about a table to record movements?

1 Like
3

Here are my tables:

Inventory:

Inventory ID Item ID DateTime Status Amount Location ID

Category:

ID Category

Items:

Item ID Name Description Image Category Vendor Purchase Cost Link

Location ID

Virtual Columns for Items:

Inventory Log Change, Total Stock Available, Barcode, and Related Inventorys

Vendors:

ID Name Logo URL Phone Email Address

Locations:

Location ID Lab Building City
4

So for the expression for your [Total Stock Available]

try..

SUM(
 SELECT(Inventory[Amount],
  AND([Item ID] = [_THISROW].[Item ID],
	  [Location ID] = [_THISROW].[Location ID],
	  [Status] = "Check In"
  )
 )
)
-
SUM(
 SELECT(Inventory[Amount],
  AND([Item ID] = [_THISROW].[Item ID],
	  [Location ID] = [_THISROW].[Location ID],
	  [Status] = "Check Out"
  )
 )
)
1 Like
5

I got no errors on this expression but the Total Stock Available is still at zero not adding.

6

Just a question. Is [Status] always one of the two values “Check In” or “Check Out”, or can it be blank (NULL) or some other value?

7

It is always Check in or check out not null.

8

When you test this expression, what do you get?

What if you test the individual SUM expressions?

Need more clues.

9

Thanks for the feedback I think we are on to something now. I removed the subtracting sum before and still got zero, but tried again and now it’s totaling everything. The problem is it not recognizing “Check In” and “Check Out” as different, so I subtract it will result in zero.