Ask Search:
Hester LauHester Lau 

Formula with Picklist (Multi-Select) Field

 We've got an Accounts Field called "Terms". It is a list of all the school terms, and it's used to show the terms that  the account/organization has been on the Programme.

As a result, when we produce a report for this field, and ultimately export it to Excel for another company to utilise, this field has a very long string of data.

e.g. "Term 4, 2008; Term 1, 2009; Term 2, 2009; Term 4, 2009; Term 1, 2010; Term 2, 2010; Term 4, 2010; Term 1, 2011; Term 2, 2011; Term 4, 2011; Term 1, 2012; Term 2, 2012"

Essentially, all the partner company needs is the last term that's been selected. (i.e. from the above example, Term 2, 2012)

The multi picklist has been arranged in chronological order.

Is it possible for me to create a new formula field that would just show the last term that's been picked?

Elizabeth DavidsonElizabeth Davidson
I was thinking you could, but then you get limited with the functions you can use with multi-select picklists.  I even tried converting the answer to text first in the formula and it still would not work.

You can do this easier once it is in excel- once you export to excel let's say the information string is in a single cell, B3.

In a blank cell (say, C3 for example) type the following:

=RIGHT(B3, 12)

C3 should now display the last segment of information ( Term 2, 2012 ) from the list, no matter how long it is.  As long as your picklist values remain constant in their length this will remain true.

Someone else may know of a way to get it to work in SFDC, but that should help you for now.  Personallly, for the most part I'm avoiding multi-select because it is driving me crazy come report time.  It's a necessary evil though and it can provide good data, just trickier to work with.

Hester LauHester Lau
 Thank you eliz! I didn't know about that formula in Excel, that would be handy.

I was also hoping it worked out in SFDC because that large field has made my report page really long with giant gaps between records (because the Terms field has been text wrapped several times). It would make my report much neater on the screen.
Elizabeth DavidsonElizabeth Davidson
With the picklist getting longer, have you considered changing this to two fields?  A "start date" and an "end date" for the terms?  This would resolve your problem with the reports both in SFDC and in excel without extra work.

If the time on program is consecutive, it'll work nicely.  You can keep the field you have now if you've got stuff that is running based on one of the multi-picklist formulas (is value, etc)

Hester LauHester Lau
 Yeah, as you mentioned, unfortunately schools may not necessarily be on the programme consecutively, start and end term doesn't cater for those in between terms that the schools are not on the Programme. 

So I was hoping there were some special SFDC formula codes like "last" that I didn't know about that could help me. But that's OK, we'll just work with what we've got at the moment.