SELECT AND SUM FORMULA

[KathB27]

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],

)))

[Aurelien]

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]
    )
  )
)
)

[KathB27]

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

[Aurelien]

Hi @KathB27 

---

@KathB27 wrote:

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

---

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

---

@KathB27 wrote:

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

---

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 ?