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

# Validated by calculation with If statement?

### 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?

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?

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?

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?

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 )