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