Authored by Zishan Razzaq

Tuesday, June 8, 2010

Count Selected Items in MultiPicklist in Formula Field in Salesforce.com

Scenario:
  • Need to capture the count of selected items in a multi-select picklist  into a field.
  • Note: This will work once the user clicks save on the record.
Solution:
  1. Create a formula field in the salesforce object where the multi-select picklist resides.
  2. Make it a Number attribute with decimal holding of Zero
  3. Give it a name
  4. Click on Advance Formula
  5. Two conditions we will use is 
    1. INCLUDES(multiselect_picklist_field, text_literal) method
    2. IF(logical_test, value_if_true, value_if_false) method
  6. Let say our Multi-Select Picklist was named "Colors__c" and the values were "Red", "Green", "Blue"
  7. We need to count the values that the user selected within this "Colors__c" Picklist.
  8. Formula would be as follows:
    1. Taking the INCLUDES(multiselect_picklist_field, text_literal) method first:
      1. INCLUDES(Colors__c,'Red') <== Basically means that when the user has selected "Red" from the multi-select picklist box named "Colors__c"
      2. Now place in IF Then Method:
        1. IF(INCLUDES(Colors__c,'Red'),1,0) <== If(the user chooses "Red" then give the value of the field a 1 otherwise a zero.
        2. Now we need to add to it so that it increments when a user selects more then 1 value from the multi-select picklist.
        3. IF(INCLUDES(Colors__c,'Red'),1,0)
          + IF(INCLUDES(Colors__c,'Green'),1,0)
          + IF(INCLUDES(Colors__c,'Blue'),1,0) 
      3. Click save. That is it.
Please check out the screen shots below. but please provide feedback on this:
Colors__c Field Multi-Select Picklist: (Red, Green, Blue)
No_of_Colors_Selected__c Formula Field:
Using the Field in the Object (Account in this case):<== As you can see I selected Red and Green Values from the Colors__c box.
Click Save to see if Formula Works:
No_of_Colors_Selected is 2 which is correct because I only selected Red;Green values from the Colors__c Picklist.
Thank you
Check out my Other Blogs:

VisualForce Made Easy
Data Migration Made Easy
eTechCareers.com <== Coming Soon!!!! All of your Employment needs for Information Technology and Project Management.
LINKS:

3 comments:

  1. Hey thanks but I'm facing a problem. Ihv 150 items and the formula gives an error "Formula tooo big" :(

    ReplyDelete
  2. This is a great solution!! Thanks! Now, it has brought up one more issue. I used this formula for 2 different Multi-picklist fields. My client needs to see the two "multi-picklist fields in a grid format. I don't think it will be possible anymore since there are many fields (instead of the two that I had before). Do you have any ideas for this issue?

    Thanks!

    ReplyDelete
  3. Get new certificates that improves your knowledge. Getting certified in Cisco 300-410 Exam improves your professional advancement. Marks4sure.org offers real updated 300-410 Dumps and practice test.

    ReplyDelete