Can I make a conditional constraint?

RogerFarmer1

Can I make a conditional constraint?

I have a required field to record M.E.N. Resistance in Ohms.

I also have another two optional fields for Bank1 resistance and Bank2 resistance (Ohms).

Condition is M.E.N. cannot be greater than Bank1 or Bank2.

When Bank1 and/or Bank2 is empty, entering any value in M.E.N. will obviously be greater than Bank1 and/or Bank2.

Can I put an IF statement in the Constraint column?

Earth Resistance Test.xlsx

MarcoPoetsch_0-1656373012258.png

Anonymous User

Hi Roger,

I didn't cover all the cases you were expecting, it was intended to be an example expression so you could see how nested if statements work and try to adjust it yourself to get the result you were after.

You need to add an extra if statement at the start to check when both values are null, and ensure the value is 0 or greater for MEN, as you made this question required I assume it should always be a positive number. If not, and you expect negative numbers also, you may need to adjust this to work with any number. I have added this logic to the expression and tested it with Connect and App and it works:

if(string-length(${bank1})=0 and string-length(${bank2})=0

   ,.>=0
      ,if(string-length(${bank1})>0 and string-length(${bank2})>0
         ,.<=${bank1} and .<=${bank2}
            ,if(string-length(${bank1})>0 and string-length(${bank2})=0
               ,.<=${bank1}
                  ,if(string-length(${bank2})>0 and string-length(${bank1})=0
                     ,.<=${bank2}
                          ,''
                     )
               )
         )
   )

As for your second screenshot above, this does not make sense when compared with the message you display. The constraint message says that:

MEN cannot be greater than Bank 1 or Bank 2

In your example, Blank1 is "1", Blank2 is "1.1" and the MEN value is "1.1". This will cause the constraint to be true, as 1.1 is greater than 1. This occurs because we used an "and" statement in the expression were both Blank1 and Blank2 had values. If you want it to be an either or constraint, you then need to use an "or" statement. However for me, the constraint message is not clear in this case, as the way it reads would mean that MEN should not be greater than both the values, not just one or the other. In that case use the following expression with an "or" in the second if statement:

if(string-length(${bank1})=0 and string-length(${bank2})=0
   ,.>=0
      ,if(string-length(${bank1})>0 and string-length(${bank2})>0
         ,.<=${bank1} or .<=${bank2}
            ,if(string-length(${bank1})>0 and string-length(${bank2})=0
                 ,.<=${bank1}
                     ,if(string-length(${bank2})>0 and string-length(${bank1})=0
                        ,.<=${bank2}
                           ,''
                        )
               )
         )
)

Another way you could do the constraint checks would be to put the constraint messages on the Blank1 and Blank2 fields, that will simply things as you are not trying to validate two different values in the one field where either could be true and accepted.

Test out both the above examples, and you may need to adjust them, test for all cases of values, null, higher and lower values to ensure it works the way you are expecting.

Regards,

Phil.