Re: Update Amount of item in each location

dbui · 02-23-2022 02:33 PM

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

TeeSee1

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

You apparently have Inventory.

What about a table to record movements?

dbui

Here are my tables:

Inventory:

Inventory ID

Item ID

DateTime

Status

Amount

Location ID

Category:

ID

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

TeeSee1

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"
  )
 )
)

dbui

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

TeeSee1

When you test this expression, what do you get?

What if you test the individual SUM expressions?

Need more clues.

dbui

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.

alphacp

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?

dbui

It is always Check in or check out not null.