Current stock calculation by store location

Arjun032
Participant III

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??

0 9 365
9 REPLIES 9

Lavanya
Participant I

Hai,

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

Current stock has already calculated
but i want stock of the product at different location

Lynn
Participant V

Hi @Arjun032
Have you tried with AND()

no
how??

Read the help doc and experiment.

@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.

Thanks @sangmin this syntax is okay and worked.

** Worked in my case

SUM(
    SELECT(
        [Related Receiveds][QTY],
        AND(
            [DIA]=[_THIS].[DIA],
            [DESCRIPTION]=[_THIS].[DESCRIPTION]
        )
    )
) 
- 
SUM(
    SELECT(
        [Related Releaseds][QTY],
        AND(
            [DIA]=[_THIS].[DIA],
            [DESCRIPTION]=[_THIS].[DESCRIPTION]
        )
    )
) 
+ 
SUM(
    SELECT(
        [Related Returneds][FINAL RETURNED QTY],
        AND(
            [DIA]=[_THIS].[DIA],
            [DESCRIPTION]=[_THIS].[DESCRIPTION]
        )
    )

) 

Top Labels in this Space