Formula Problem (Auto Sum all items)

[GirardCasuga]

I'm currently making a inventory management system with employee supply request. I'm having a problem with this formula on current available stock:

SUM(
SELECT(
Inventory[Quantity Received],
[Item ID] = [_THISROW].[Item ID]
))
-
SUM(
SELECT(
RIS[Quantity],
[Status] = [_THISROW]."Approved"
))

The logic is the employee will request a quantity of supply and the supply officer will approve or disapprove the request, upon approval the quantity will be deducted to the specific item that the employee requested and if disapproved the available stock will stay the same. however with the formula above when a specific item gets approved, all the items in the list will be deducted also.

Attached also herewith the screenshot of the formula for your reference. Hope you guys can help me. Thanks in advance!

[Aurelien · Accepted solution]

Hi @GirardCasuga 

I'm not sure of your table structure, but based on your informations, here is how I think you expect it to be:

SUM([Related Inventorys][Quantity Received])
-
SUM(
  SELECT(RIS[Quantity],
    AND(
      [Item ID]=[_THISROW],
      [Status] = "Approved"
    )
  )
)

 Please note I was not aware that an expression like this would work. This is the first time I see this.

[Status] = [_THISROW]."Approved"

[GirardCasuga]

Thank you very much for your help your code works like a charm :))))

SUM(SELECT(Items[Total Stock Purchased],([Item ID] = [_THISROW].[Item ID])))
-
SUM(
SELECT(RIS[Quantity],
AND(
[Item ID]=[_THISROW],
[Status] = "Approved"
)
)
)

[Aurelien]

Great to hear that worked 😁

If you started from the Simple inventory management sample app, you may want to prefer this instead, as it is more efficient (using deref expression is more efficient than SELECT and FILTER)

SUM([Inventory Change logs][Total Stock Purchased])
-
SUM(
  SELECT(RIS[Quantity],
    AND(
     [Item ID]=[_THISROW],
     [Status] = "Approved"
    )
  )
)

Additional tip: for adding expression-look-alike, click on this in the top-bar editor:

And for modifying then, double-click on the expression you just validated 😉

More information:

https://help.appsheet.com/en/articles/4575708-list-dereference

[GirardCasuga]

Thank you very much. Hope you can help me again in the future 😊