SELECT AND SUM FORMULA

AppSheet AppSheet Q&A
, , , ,
1

Hi All,

I need help with below formula.

The formula is working but i realized that I need to add another condition that If the Current Stock in the Inventory Table is more than the Quantity ordered in Adhoc Order Table then i just want the system to subtract Current Stock by itself because i cannot have a negative number.

IF([Current Stock]<=0,

SUM(
SELECT(
Adhoc Order[Quantity],
[Stock Code] = [_THISROW].[Stock Code])),

[_THISROW].[Current Stock]-
SUM(
SELECT(
Adhoc Order[Quantity],
[Stock Code] = [_THISROW].[Stock Code],

)))

2

Hi @KathB27

What about:

IF([Current Stock]<=0,

SUM(
  SELECT(Adhoc Order[Quantity],
    AND(
      [Stock Code] = [_THISROW].[Stock Code],
      [Quantity]<[_THISROW].[Current Stock]
    )
  )
),

[_THISROW].[Current Stock]-
SUM(
  SELECT(Adhoc Order[Quantity],
    AND(
      [Stock Code] = [_THISROW].[Stock Code],
      [Quantity]<[_THISROW].[Current Stock]
    )
  )
)
)
1 Like
3

Hi @Aurelien ,

I have 3 conditions: (for Sales)

If Current Stock <=0 – i want it to remain 0

If Current Stock < Adhoc Order [Quantity] — i want the Current stock to change to 0

If Current Stock > Adhoc Order [Quantity] — i just need the Adhoc Order [Quantity] to be deducted from the Current Stock

The above 3 conditions are for all the sales made.

I would have to add another condition: where i will add Quantity to Current Stock from the Restock Table [Quantity] when restock is made.

Thank you and hope you will be able to help me with this…

4

Hi @KathB27

I think you are expecting a kind of circular expression. It’s not possible.

This is possible with an action performed from another table, on form saved event for example.

Can you please be more specific:

  • which column were you trying to calculate with your expression ?

  • which operations are you performing, in plain language ?