1

I'm trying to come up with a formula that will show the remaining picklist values that have not been selected by the user as the "remaining" choices. I'm using the formula listed below but unfortunately once I finalize the formula, I have over 5,000 characters. Everyone says I should utilize the CASE formula but I have not been able to make it work the way I need it. See below for an example of what formula I'm trying to create.

IF(ISPICKVAL(Program_Type__c , "Accessories - ACH"),
     IF( !INCLUDES(Paperwork_Completed1__c , "App for Dealer Status"), "App for Dealer Status" &BR() , null) &
     IF( !INCLUDES(Paperwork_Completed1__c , "Copy of Driver's License"), "Copy of Driver's License" &BR() , null) &
     IF( !INCLUDES(Paperwork_Completed1__c , "Copy of Voided Check"), "Copy of Voided Check" &BR() , null) &
     IF( !INCLUDES(Paperwork_Completed1__c , "ACH Authorization"), "ACH Authorization" &BR() , null) &
     IF( !INCLUDES(Paperwork_Completed1__c , "Resale Certificate"), "Resale Certificate" &BR() , null) &
     IF( !INCLUDES(Paperwork_Completed1__c , "Terms and Conditions"), "Terms and Conditions", null)&
     IF( !INCLUDES(Paperwork_Completed1__c , "W-9"), "W-9", null),
Improve this question
4
  • 1
    It looks like you're going through a checklist of a process, selecting each option as you go along; would a series of checkboxes or a regular "stage" picklist be a viable option to simplify?
    – Mike Chale
    Oct 26, 2012 at 14:55
  • You are correct. Paperwork__Complete is dependent on the Program_Type__c picklist. This formula looks at what program type is needed and then review the paperwork that is currently received and outputs the paperwork needed. We are unfortunately on Professional Edition, so my options are extremely limited due to field limitations, so this seemed to be the best option. Each program type has a different list of paperwork
    – Robin Terrell
    Oct 26, 2012 at 15:00
  • Is the main issue just the size of the formula?
    – Mike Chale
    Oct 26, 2012 at 16:56
  • Yes sir. It is.
    – Robin Terrell
    Oct 26, 2012 at 18:07

3 Answers 3

Reset to default
1

I don't think it is possible to use a CASE with multi-select picklist so I think your best bet is to try to shorten the formula. A few ideas:

  1. See if you can abbreviate the text being compared. Instead of "ACH - Authorization" maybe you can use "ACH-Auth", which would save 11 characters.
  2. Use a numbering system. Have your picklist values be "1: ACH-Auth", "2: Voided Check" and then your formula can just return the step numbers, "1, 2" saving characters.
  3. Combine ideas #1 and #2
Improve this answer
1
  • You could also try removing the BR() tags, these might be chewing up some characters.
    – zachelrath
    Nov 1, 2012 at 10:44
0

While this is not a great solution and will work dependant on the number of picklist values in your field (as the formula length will be proportional to number of Picklist values), if there aren't too many, here's how you can do it with case.

This is done for simplicity with the Rating field on Account, which has values {Hot, Warm, Cold}

CASE(Rating, 
"Hot", "Warm, Cold",   //if the value is Hot, then return "Warm, Cold"
"Warm", "Hot, Cold", 
"Cold", "Warm, Hot", 
"Hot, Warm, Cold") //Default value if no picklist value is selected

http://login.salesforce.com/help/doc/en/customize_functions.htm#CASE

Improve this answer
5
  • I will try and see if that works.
    – Robin Terrell
    Oct 26, 2012 at 18:10
  • Sure, I tried that and it works. Give it a whirl.
    – techtrekker
    Oct 26, 2012 at 18:11
  • Since I'm using a Mult-select picklist, wouldn't this cause the values to be duplicated? For example, if I selected Hot & Warm in your scenario, wouldn't it output "Warm, Cold" and "Hot, Cold"?
    – Robin Terrell
    Oct 26, 2012 at 18:14
  • Nevermind... I actually found out that Multi-select picklists cannot be referenced with a CASE formula, so I guess the answer is No.
    – Robin Terrell
    Oct 26, 2012 at 18:24
  • To be fair, your question doesn't say anything about the picklist being multi select.
    – techtrekker
    Oct 26, 2012 at 18:27
0

This is interesting, but what's the use case? Why would you want to show the other values that are not selected?

Improve this answer
2
  • So we could show what paperwork has not been received back without having to select those values on a second multi-select picklist. I'm definitely open to suggestions if anyone things I'm going the long way on this :)
    – Robin Terrell
    Oct 26, 2012 at 16:12
  • 1
    Yo Pete! Welcome to the salesforce stack exchange! There are some rules and etiquette on posting, answering and commenting. In actuality, this is not an answer. Please place this as a comment on the question. Here's some info on the FAQ: salesforce.stackexchange.com/faq -thanks!
    – pchittum
    Oct 26, 2012 at 16:34

You must log in to answer this question.

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