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

  • 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 '12 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 '12 at 15:00
  • Is the main issue just the size of the formula? – Mike Chale Oct 26 '12 at 16:56
  • Yes sir. It is. – Robin Terrell Oct 26 '12 at 18:07
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|||||
  • You could also try removing the BR() tags, these might be chewing up some characters. – zachelrath Nov 1 '12 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|||||
  • I will try and see if that works. – Robin Terrell Oct 26 '12 at 18:10
  • Sure, I tried that and it works. Give it a whirl. – techtrekker Oct 26 '12 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 '12 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 '12 at 18:24
  • To be fair, your question doesn't say anything about the picklist being multi select. – techtrekker Oct 26 '12 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|||||
  • 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 '12 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 '12 at 16:34

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.