Inventory Management app using Google Sheets not calculating Current Stock

[c_b]

Hello,

I am using the Inventory Management app found at here

The app has 3 tables, Product, Sales, and Purchases.

My problem is that the following expression is failing to add to or deduct from the Initial Stock based on the Quantity column in the Related Purchases and Related Sales tables:
COUNT([Related Purchases]) - COUNT([Related Sales]) + [Initial Stock]

For example, on the app I select ‘Add to Stock’ and scan the barcode 789 for Lotion and select Quantity 10. Since the Initial Stock for this item is 20, the Current Stock should reflect 30 but instead it says 21. The Current Stock is not reflecting the Quantity entered, just the fact that the barcode was scanned. The same thing happens when I select ‘Sell’ and scan the barcode. It does not deduct from the Initial Stock based on the Quantity I entered but only on the fact that it was scanned once.

Since this all totally new to me, I suspect that I need the expression to call upon the Quantity column in some way. I have tried to find out how to do that, but I have failed. My business really needs a simple way to track inventory and this would be the perfect solution if I can get it to work.

Any help is greatly appreciated!
Carin

[L_John · Accepted solution]

In data under columns click the product tab

in related sales use this formula
SUM(SELECT(Sales[Quantity],([Product Barcode] = [_THISROW].[Product Barcode])))

in related purchases use this formula
SUM(SELECT(purchases[Quantity],([Product Barcode] = [_THISROW].[Product Barcode])))

in current stock use this formula
[related purchases]- [related sales] + [Initial Stock]

[c_b]

Thank you! That did the trick!