Multiple entry quantity that deduct to its inventory

[Ebel]

Hello Everyone,

Need help new with appsheet.

I am creating a Sales entry where I can enter multiple product with price and quantity in 1 form then each quantity sold for each product will deduct to its corresponding product stocks in my inventory table.

Trying to use:

[BODEGA QTY] -

ANY(SELECT(Sales[SOLD QTY 1], [_ROWNUMBER] = MAX(Sales[_ROWNUMBER]))),

[BODEGA QTY] -

ANY(SELECT(Sales[SOLD QTY 2], [_ROWNUMBER] = MAX(Sales[_ROWNUMBER])))

but the result is its deducting only for 1 product with the total of all quantity entries.

Much appreciated for the help.

[Joseph_Seddik]

Hello, your expression will always return one entry because:

• your condition [_ROWNUMBER] = MAX(Sales[_ROWNUMBER], will select only one row which is the one with the highest route number.
  
• even when multiple rows are selected, you force the expression to return one row by using ANY().  

[Ebel]

Hi @Joseph_Seddik ,

Thanks for the quick reply, I will use this hint to try to comeup with another solution without using those 2 functions. 

Appreciated it very much.

[Joseph_Seddik]

I'm not able to give you more than the "hint" as you name it 🙂 since I don't see your columns. But you might find @Aurelien's suggestion useful. 

[Aurelien]

Hi @Ebel 

Assuming your table structure match this expression:

[Producto].[BODEGA QTY] 
-
SUM(
  SELECT(Sales[SOLD QTY 1],
  [_THISROW].[Producto]=[Producto])
  -[_THISROW].[SOLD QTY 1]
)

If not, can you provide your table structure ? 

Thanks

[Ebel]

Hi @Aurelien ,

Below is my Inventory table where prices and stocks data are located.

Below table is my Sales Table where I enter all Sales and quantity sold items (QTY SOLD 1) then it will deduct the total quantity from the BODEGA QTY (picture above).

It has Column up to QTY SOLD 10 to accommodated all items sold in 1 Receipt per say.

I am also using Automation to trigger this actions/event which I saw from tutorials and samples over the internet.

Your Help is very much appreciated.

[Aurelien]

Im' note sure of what you are willing to accomplish.

Would you want to know, when looking at a product detail, which current stock you have ?

[Ebel]

I wanted to deduct the product quantity automatically that has been sold from the inventory (BODEGA table) using Sales Entry Form. In this case, the stocks inventory of each product is updated.

Example:

I sold Keyboard, mouse and SSD, then i entered it in my Sales form all the details including the quantity sold of each item.

Then the Apps will deduct it directly from my Stock Inventory each quantity that has been sold, Keyboard, Mouse, SSD.

[Aurelien]

Thank you for providing further informations.

I think you may need to change your table structure. 

I suggest:

Table BODEGA

key ==> Initial value UNIQUEID()

Item ==> your item

initialStock ==> Number

Then, table Sales:

key ==> Initial Value UNIQUEID()

item ==> Ref ==> your table BODEGA

Quantity ==> Number

Then, bak to your table BODEGA :

Add a virtual column "CurrentStock", with Expression:

[InitialStock]+SUM([Related Sales][Quantity])

This way, this:

---

Then the Apps will deduct it directly from my Stock Inventory each quantity that has been sold, Keyboard, Mouse, SSD.

---

will be calculated automatically