# Current stock calculation by store location

I have 3 table

1. stock IN
which contains
ID (primary key)
PRODUCT
DATE
QUANTITY
REMARKS
STORE POINT

2.Stock OUT
STOCK_OUT_ID
CUSTOMER
PRODUCT_NAME
DATE
QUANTITY
REMARKS
STORE POINT

1. Products
Product Name
Product Description
Price Image
Total Inventory
Total Requested
Total Available
store location(V C)

now current stock will be calculated by

SUM(
** SELECT(**
** Stock IN[QUANTITY],**
** [PRODUCT] = [_THISROW].[Product Name])) -**

sum( SELECT(
** Stock out[QUANTITY],**
** [PRODUCT_NAME]= [_THISROW].[Product Name]))**

This is only display the number of stock

But i have to display current stock of an item by store location and current stock of a item in that location .

how this possible??

Hai,

``     You have to subtract Stock IN Quantity and Stock Out Quantity to get the current stock``
1 Like

but i want stock of the product at different location

Hi @Arjun032
Have you tried with AND()

3 Likes

no
how??

Read the help doc and experiment.

4 Likes

## @Steve Here my tables am created current stock by calculating this syntax SUM( SELECT( Stock IN[QUANTITY], [PRODUCT] = [_THISROW].[Product Name]))

sum( SELECT(
Stock out[QUANTITY],
[PRODUCT_NAME]= [_THISROW].[Product Name]))

this syntax will return stock of an item correctly as whole amount in all store location.
but i have 4 store point.

main issue that when stockout a product from specific store point then display the current stock of the product in that store location.
and also when an stockin were done at a store point then stock of the product only display in that storepoint.

how is thiss possible??

here my tables
products

stock in

stockout

Hi~
Store location = Store point ??
When calculating inventories using AND functions, Location must also be added.

SUM(
SELECT(
Stock IN[QUANTITY],
AND(
[PRODUCT] = [_THISROW].[Product Name],
[STORE POINT] = [_THISROW].[store location]
)
))

-SUM( SELECT(
Stock out[QUANTITY],
AND(
[PRODUCT_NAME]= [_THISROW].[Product Name],
[STORE POINT] = [_THISROW].[store location]
)
))

Iām not an expert, but I hope it helped.

3 Likes

Thanks @sangmin this syntax is okay and worked.

1 Like