2

I am stumped so I hope I can present my question in a manner that allows me to get some support. I am creating a Lead validation rule that in essence is 3 tiers i.e. if tier 1 is selected require that tier 2 is selected and if this value in tier 2 is selected require tier 3 text to be completed. Here are my picklist requirements

Lead Source           Lead Source Details                       Complete Text Field

Wholesaler
                          >ABC Wholesaler
                          >XYZ Wholesaler
                          >Other if other is selected complete   [text field]   

Personal Referral
                         nothing else here

Trade Show/Event
                         nothing else here but we need to >   [text field]

TBP Event
                         nothing else here but we need to >   [text field]

Website
                         nothing else here

Other
                          nothing else here but we need to >   [text field]

When this was only a 2 tier picklist I used this validation rule but now I can't figure out how to rewrite for 3 tier

ISBLANK(Lead_Source_Details__c) && ( ISPICKVAL(LeadSource, "Distribution Partner") || ISPICKVAL(LeadSource, "Event/Trade Show") || ISPICKVAL(LeadSource, "TBP Event") || ISPICKVAL(LeadSource, "Other") )

1
  • Thank you Jason. The solution worked but in the end I decided to implement a very simple solution to minimize clicks. Commented Oct 30, 2014 at 15:07

2 Answers 2

1

This would be a cleaner way to see what is going on and easy to extend

AND('1'=
     CASE(LeadSource,  /* set Case result to '1' if text field is required */
          'Wholesaler',CASE(Lead_Source_Details__c,
                                        'Other','1',
                                        '0'),
          'Personal Referrer','0',
          'Trade Show/Event','1',
          'TBP Event','1',
          'Website','0',
          'Other','0',
         '0') ,
     ISBLANK(TextField__c)
)
2
  • Thank you! This solution worked for my request. I appreciate the assistance. Commented Oct 30, 2014 at 15:08
  • Cameron, great to hear; please mark as solved so the community benefits
    – cropredy
    Commented Oct 30, 2014 at 15:55
0

A validation rule's formula should be true when rules fails, so we need to have criteria for each failure condition, combined with an OR ( "||" ). If I read your table correctly, here's how to express your requirements. I used inline comments to highlight each LeadSource's section of the formula.

/* first check Wholesaler */
( ISPICKVAL(LeadSource, 'Wholesaler')
  && ( ISBLANK(Lead_source_Details__c)
       || (ISPICKVAL(Lead_Source_Details__c, 'Other') && ISBLANK(TextField__c))
     )
) ||
/* Personal Referral has no other requirements */
/* check Trade Show */
( ISPICKVAL(LeadSource, 'Trade Show/Event') && ISBLANK(TextField__c)
) ||
/* check TBP */
( ISPICKVAL(LeadSource, 'TBP Event') && ISBLANK(TextField__c)
) ||
/* Website has no other requirements */
/* check Other */
( ISPICKVAL(LeadSource, 'Other') && ISBLANK(TextField__c)
)

Note that I didn't have the name of your text field, so I used TextField__c; update for your field name.

5
  • While this works, I'd like to say it's incredibly inefficient, and would reduce their ability to make more formulas later.
    – sfdcfox
    Commented Oct 22, 2014 at 23:49
  • //first check Wholesaler ( ISPICKVAL(LeadSource, 'Wholesaler') && ( ISBLANK(Lead_source_Details__c) || (ISPICKVAL(Lead_Source_Details__c, 'Other') && ISBLANK(Lead_Source_Other__c)) ) ) || //Personal Referral has no other requirements //check Trade Show ( ISPICKVAL(LeadSource, 'Trade Show/Event') && ISBLANK(Lead_Source_Other__c) ) || // check TBP ( ISPICKVAL(LeadSource, 'TBP Event') && ISBLANK(Lead_Source_Other__c) ) || //Website has no other requirements //check Other ( ISPICKVAL(LeadSource, 'Other') && ISBLANK(Lead_Source_Other__c) ) Error: Syntax error. Found '/' Commented Oct 23, 2014 at 21:16
  • BTW name on the field is Lead_Source_Other__c . Thank you Jason. See above ... I received this error > Error: Syntax error. Found '/' Commented Oct 23, 2014 at 21:21
  • Sorry @CameronYoemen, I thought you could use // to denote a comment, but formulas only allow comments wrapped in /* and */. I've updated my answer with the corrected formatted. Commented Oct 23, 2014 at 21:46
  • Thanks again Jason. I think that was it but now I'm having to rethink based on this error. I will keep you posted if I learn anything. Error: Field Lead_Source_Details__c is a picklist field. Picklist fields are only supported in certain functions. Commented Oct 23, 2014 at 22:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .