0

I have a field that a consulting team recreated to capture multi-select picklist values. The issue that when we are excluding one of the values, I seem to only capture the data of "one" value when only one is selected and nothing when the "excluded" field is included in the multipicklist.

Example: I want to exclude "A" but creating the formula the way I have researched makes everything "NULL" if it's included in the picklist. I want to simply and not have the "[]" display if it's the only option selected on the picklist.

IF(ISBLANK(MultiPicklist__c), "",
Record_ID__c&" "&
IF(OR(ISNULL(MultiPicklist__c), INCLUDES(MultiPicklist__c, "A")),"",

"["& 
IF(INCLUDES(MultiPicklist__c,"B"),"B,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"C"),"C,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"D"),"D,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"E"),"E,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"F"),"F",NULL)&
"]"))

I also tried this...

IF (ISBLANK(MultiPicklist__c), "", 
Record_ID__c&" "&IF(ISNULL(MultiPicklist__c),"", 

IF(INCLUDES(MultiPicklist__c,"A"),NULL, NULL)& 
"["& 
IF(INCLUDES(MultiPicklist__c,"B"),"B,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"C"),"C,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"D"),"D,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"E"),"E,",NULL)& 
IF(INCLUDES(MultiPicklist__c,"F"),"F",NULL)&"]"))

I get what I am looking for but I cannot remove the "[""]" for "A". Thoughts, please?

Updated Scenario:

IF (ISBLANK(MultiPicklist__c), "", 
Record_ID__c&" "&IF(AND(ISNULL(MultiPicklist__c),NOT(INCLUDES(MultiPicklist__c,"B")),NOT(INCLUDES(MultiPicklist__c,"C")),NOT(INCLUDES(MultiPicklist__c,"D")), NOT(INCLUDES(MultiPicklist__c,"E")), NOT(INCLUDES(MultiPicklist__c,"F"))),"", 
"["& 
IF(INCLUDES(ID_Type__c,"B"),"B,",NULL)& 
IF(INCLUDES(ID_Type__c,"C"),"C,",NULL)& 
IF(INCLUDES(ID_Type__c,"D"),"D,",NULL)& 
IF(INCLUDES(ID_Type__c,"E"),"E,",NULL)& 
IF(INCLUDES(ID_Type__c,"F"),"F",NULL)&"]"))  

I got nothing, but still show the "[""]" when A is selected.

1

This formula is written incorrectly based upon the objective you've stated. Formatting as below can help to make the logical sequence clear:

IF(ISBLANK(MultiPicklist__c), 
   "",
   Record ID__c&" "&
   IF(OR(ISNULL(MultiPicklist__c), INCLUDES(MultiPicklist__c, "A")),
      "",
     "["& 
       IF(INCLUDES(MultiPicklist__c,"B"),"B,",NULL)& 
       IF(INCLUDES(MultiPicklist__c,"C"),"C,",NULL)& 
       IF(INCLUDES(MultiPicklist__c,"D"),"D,",NULL)& 
       IF(INCLUDES(MultiPicklist__c,"E"),"E,",NULL)& 
       IF(INCLUDES(MultiPicklist__c,"F"),"F",NULL)&
       "]"))

The key issue here is the OR function. If the OR evaluates to true, your result will be the value of Record ID__c, a space, and then an empty string (""). Your bracketed list of letter codes are only included if the OR condition evaluates to false, meaning that INCLUDES(MultiPicklist__c, "A") must also evaluate to false. That's why you get a mostly blank result if the multi-select picklist includes this value.

You actually don't need the OR function at all, because you already checked whether MultiPicklist__c is blank at the top level of your logic.

I'm not sure there is an elegant way to suppress the brackets when the picklist is just "A", since the functions applicable to multi-select picklists are so few. One way to do it would be to replace INCLUDES(MultiPicklist__c, "A") with AND(NOT(INCLUDES(MultiPicklist__c,"B")), [and so on for C-F]). This will, of course, become unmanageable as your picklist grows, which would suggest moving to an Apex solution.

  • Which would be the best example? – Awesome User2 Mar 29 '18 at 3:38
  • What do you mean? – David Reed Mar 29 '18 at 3:39
  • I added the AND(NOT(INCLUDES(MultiPicklist__c, "B"), NOT(INCLUDES(MultiPicklist__c, "C"), NOT(INCLUDES(MultiPicklist__c, "C"), and so on, and I still see the "[""]" – Awesome User2 Mar 29 '18 at 3:51
  • What does your current formula look like? – David Reed Mar 29 '18 at 11:28
  • I'll add it up top @david Reed – Awesome User2 Mar 29 '18 at 13:22

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.