Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Chris ParisChris Paris 

Validation Rule Formula help - Picklist & Formula Field

I am trying to create a validation rule which references a picklist field [Field A] and a formula field(text) [Field B]

Field A has options A1, A2, A3 = picklist 
Field B has options B1, B2, B3 = Formula

How would I create a validation rule that allows for the following conditions, if any are met: 
If Field A = A1 and Field B = B1
If Field A = A2 and Field B = B1 or B2
if Field A = A3 and Field B = B1 or B2 or B3

The formula I am attempting to write is : 
If (and( ispickval(Field A = A1, Contains(Field B = B1),,
If (and(ispickval(field A = A2, OR(Contains(Field B = B1, Contains(Field B = B2),,
If (and(ispickval(field A = A3, OR(Contains(field b = B1, Contains(Field B = B2, Contains(Field B = B3,,
)))

Is it appropriate to use a 'IF" statement or would an "OR" statement be more appropriate?

Any help would be grateful!

Thanks,
Best Answer chosen by Chris Paris
ClintLeeClintLee
If you're saying that you only want to allow those combinations then you should be able to do it this way.

Keep in mind that when a validation rule formula evaluates to true the validation rule fires the error message.  

OR(
    AND(
        TEXT(Field_A) = "A1"
       ,NOT(Field_B = "B1")
    )
   ,AND(
        TEXT(Field_A) = "A2")
       ,AND(
            NOT(Field_B = "B1")
           ,NOT(Field_B = "B2")
       )
   )
  ,AND(
       TEXT(Field_A) = "A3"
      ,AND(
           NOT(Field_B = "B1")
          ,NOT(Field_B = "B2")
          ,NOT(Field_B = "B3")
      )
  )
)

In this rule, for example, if Field A = A1 and Field B = B2 the validation rule would fire your error message.

Hope that helps,

Clint

All Answers

ClintLeeClintLee
If you're saying that you only want to allow those combinations then you should be able to do it this way.

Keep in mind that when a validation rule formula evaluates to true the validation rule fires the error message.  

OR(
    AND(
        TEXT(Field_A) = "A1"
       ,NOT(Field_B = "B1")
    )
   ,AND(
        TEXT(Field_A) = "A2")
       ,AND(
            NOT(Field_B = "B1")
           ,NOT(Field_B = "B2")
       )
   )
  ,AND(
       TEXT(Field_A) = "A3"
      ,AND(
           NOT(Field_B = "B1")
          ,NOT(Field_B = "B2")
          ,NOT(Field_B = "B3")
      )
  )
)

In this rule, for example, if Field A = A1 and Field B = B2 the validation rule would fire your error message.

Hope that helps,

Clint
This was selected as the best answer
Chris ParisChris Paris

Hi Clint,

I'm receiving the error when attempting to add the validation rule: 

Error: Incorrect parameter type for function 'TEXT()'. Expected Number, Date, DateTime, Picklist, received Text

For reference the field for TEXT() is a formula text field.  Any suggestions on how to move forward?

Thanks,
Chris ParisChris Paris
I removed the Text() from the suggested TEXT(Field A) and added NOT(ISPICKVAL(Field B) to the NOT(Field B) statements to get everything working. 

Thank you much Clint!