Complex Valid_if expression help

[Elijah_Magrane]

Hi,

I am trying to put valid_if constraints on a inventory app in order to prevent users from making check-in/check-out errors. This ensure that we can’t check in/check out more than was produced and that once stock leaves for an order we can not go over the new balance.

My expression is as follow

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

The expression is bloated and is not working as intended. Any help would be appreciated

[Marc_Dillon]

I’ll start by reformatting your expression for clarity:

AND(

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

  ,

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

  )

[Marc_Dillon]

Couple questions:

Do you have a “Products” table or similar, that is being referenced by this [QR] column? If not, I’d highly suggest it.

The remaining inventory balance is calculated by “Initial Packaging” minus “Expedition of Order”, correct?

I don’t understand why you are comparing that to a [Qty-In] value? Is not the validity check for when taking inventory Out? And also the equality seems backwards to me.

You can remove the “[_THISROW].” from the “[_THISROW].[Qty-In]”.

The second condition of the overall AND() seems a bit more like what I’m expecting, but I think best to wait on answers to these initial questions first before going further.

[Elijah_Magrane]

Thanks for the reply @Marc_Dillon

Do you have a “Products” table or similar, that is being referenced by this [QR] column? If not, I’d highly suggest it.

Yes the [QR] column is referenced by a products table. The FG In Out table is a transaction table.

The remaining inventory balance is calculated by “Initial Packaging” minus “Expedition of Order”, correct?

More or less yes, The inventory is actually calculated by the sum of the transaction table Check in (+1) vs. Check out (-1)

I don’t understand why you are comparing that to a [Qty-In] value? Is not the validity check for when taking inventory Out ? And also the equality seems backwards to me.

You are right. that was an oversight on my part. I got too wrapped up in the calculation

[Marc_Dillon]

Elijah_Magrane:

Yes the [QR] column is referenced by a products table. The FG In Out table is a transaction table.

That’s great. So what I would do then is to calculate the “Current Inventory” in that referenced table, per each product. A simple expression in a virtual column such as:

SUM( [Related FG In Out][In] ) - SUM( [Related FG In Out][Out] )

Then you can reference this value in your valid_if, instead of having to recalculate it out with multiple SELECT() expressions. Something like:

[Qty-Out] <= [QR].[Current Inventory]

Important to note here is that this will only work, in all cases, for new transaction records. If you want to then modify an existing transaction record, you need to compare against the total excluding the current transaction record. Now I haven’t tested this, but I believe that you can now use [_THISROW_BEFORE] in such cases (used to just be usable in workflows). So hopefully you can do:

[Qty-Out] <= ( [QR].[Current Inventory] + [_THISROW_BEFORE].[Qty-Out] )

The above is all fairly generic advice, it sounds like your specific circumstance is maybe slightly more complicated, I hope you can adapt the advice to your situation. Please test well.

[Elijah_Magrane]

Thanks @Marc_Dillon Your suggestions give me some new jumping off points. Much appreciated