Current stock calculation by store location

I have 3 table

  1. stock IN
    which contains
    ID (primary key)
    PRODUCT
    DATE
    QUANTITY
    REMARKS
    RECEIVED BY
    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

Current stock has already calculated
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

anyone who knows please help??? because am new to this and try to learn !!!

Hi~
I had the same problem.
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