Data Valid if bug

[Paras_Sood]

Hi

I am trying to calculate a virtual column only if that record falls under a particular category.

I add the following to the column's valid if expression:

OR([Material SKU].[Category]<>"LBR",[Material SKU].[Category].[Parent Category ID]<>"RSRC")

I have tested this expression and it shows "N" in the record which I do not want it to calculate so the expression is ok.

But the expression is still calculated and I can see the value where I don't want it to. 

Am I using valid if wrong?

[Suvrutt_Gurjar · Accepted solution]

I believe a virtual column may not respect valid_if expression for its own app formula calculation. Valid_if expressions are typically used to validate a user's input in form. So if you are having this VC in form, it will validate.

You may instead wish to modify your app formula as below

IF( OR([Material SKU].[Category]<>"LBR",[Material SKU].[Category].[Parent Category ID]<>"RSRC"),

      Expression that you want with the above condition being TRUE,

       Expression that you want with the above condition being FALSE

      )

[Suvrutt_Gurjar · Accepted solution]

I believe a virtual column may not respect valid_if expression for its own app formula calculation. Valid_if expressions are typically used to validate a user's input in form. So if you are having this VC in form, it will validate.

You may instead wish to modify your app formula as below

IF( OR([Material SKU].[Category]<>"LBR",[Material SKU].[Category].[Parent Category ID]<>"RSRC"),

      Expression that you want with the above condition being TRUE,

       Expression that you want with the above condition being FALSE

      )

[TeeSee1 · Accepted solution]

The value of a VC is calculated based on the Formula expression no matter what you have in the valid-if box.

If you do not want it to 'calculate' (or default to a blank value for instance), you should put it in the FORMULA like

IF( OR(the condition, to control your "calculation" ), target calculation, default value)