4 Replies Latest reply on Jun 2, 2015 6:23 AM by philmodjunk

    Validated by calculation with If statement?

    vtdonn

      Title

      Validated by calculation with If statement?

      Post

      I am trying to figure out the syntax to validate a field.

       

      As in, if Field1 = x and Field 2 < y, then value for Field 3 must be "0". Should I be using an If statement in the calculation window? I am using the following statement but its not working and I am thinking I'm going about this the wrong way.

      In the Validation by Calculation window for Field 3 I am doing the following:

      If ( Field = "x" and Field 2 < "110"; 0 )

      Anyone know what I'm doing wrong?

        • 1. Re: Validated by calculation with If statement?
          SteveMartino

          Is Field 2 set as a number field?  Also, what does 'x' stand for? A number (number field) or word (text field)

          In your calc, you may have a typo as the first field just says field.

          What's should be the value if the statement is false?

          First I would see if you need the quotes in "110".  I don't think you do.

          If (field1=x and field2 < 110; 0 if true; something here if false)

          You can also do this with a case statement.

          • 2. Re: Validated by calculation with If statement?
            philmodjunk

            Note that, because of the quotes, "5" > "110" is a true statement while 5 > 110 is not a true statement.

            • 3. Re: Validated by calculation with If statement?
              vtdonn

              Thanks all. I'm sorting through a few moving parts in regards to field formatting and also leading zeros, so I'll have to do some experimenting. But so far, this is what I am working with for the calculation, with syntax and typos corrected-

              If ( field1 ≠ "1 - merch" and field2 < 110; 0)

              field1 is a text field, field2 is a number field. 
               
              I am trying to say that if field1 does not equal "1 - merch" and field2 is less than 110, then the entry must be 0. That is the validation that I am looking for. Am I barking up the wrong tree?

              Right now, the message   "... is defined to contain only specific values. Allow this value?"  appears no matter what I enter. So even when I use the calculation above, that message pops up when I enter "0" as a value. 

               

               

              • 4. Re: Validated by calculation with If statement?
                philmodjunk

                A validation calculation must be a Boolean calculation. That's a calculation that evaluates to a result of True or False. With a number result values of 0 or Null will be False and all other number results will be True. Your expression always evaluates as either 0 or Null and thus always produces a False result (validation fails) and you get the validation error message.

                As Steve has pointed out, you haven't indicated what values are permitted if your field 1 value either equals the quoted text or field 2 is greater than 110. I will assume that all values are permitted in that case:

                If ( field1 ≠ "1 - merch" and field2 < 110; Self = 0 ; self )