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") )

  • Thank you Jason. The solution worked but in the end I decided to implement a very simple solution to minimize clicks. – Cameron Yoemen Oct 30 '14 at 15:07
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)
)
  • Thank you! This solution worked for my request. I appreciate the assistance. – Cameron Yoemen Oct 30 '14 at 15:08
  • Cameron, great to hear; please mark as solved so the community benefits – cropredy Oct 30 '14 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.

  • While this works, I'd like to say it's incredibly inefficient, and would reduce their ability to make more formulas later. – sfdcfox Oct 22 '14 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 '/' – Cameron Yoemen Oct 23 '14 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 '/' – Cameron Yoemen Oct 23 '14 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. – Jason Clark Oct 23 '14 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. – Cameron Yoemen Oct 23 '14 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.