Valid_if statement to prevent inventory imbalances

[Elijah_Magrane]

Hi, I am trying to come up with a Valid_if statement to prevent users from “checking-in” or “Checking out” more stock than what w have available at any given time.

For example, I have a transaction table that tracks all of the in and outs. I know how much we first produce and I know how much leaves the building. We utilize Bin locations and then I have a summary table that adds all of the locations together.

If we produce say a 1000kg of something for a specific lot we should never have more than 1000kg across all of the bin locations at any given time; similarly, there should never be < 0.

But it is not really working as intended. Any help would be appreciated. I also think it should be easier to do than the below statement

([Qty-in]+

SUM(
    SELECT(
      FG In Out[Qty-Out],
      AND(
        [QR]=[_THISROW].[QR],
        [Category]="Expedition of Order"
        )
      )
    )
    		)
  <= 
  SUM(
    SELECT(
      FG In Out[Balance],
      AND(
        [QR]=[_THISROW].[QR],
        [Category]="Inital Packaging"
        )
      )
    )