How can you translate text from a related list into a field on your master object?

Magic.  Pure and simple.  That, and formulas.  Lots of formulas.  We’ll call it Formula Magic.

This works by assigning each entry within the picklist to a range of digits, using a roll-up summary formula to compile the totals, and then using remainder calculations to determine type.

Here’s how to set it up:

1. Create a master/detail relationship between two objects.
2. Create a picklist field on the detail object for calculation.
3. Create a numeric field on the detail object for use in calculations (e.g. Offer_Code__c).
4. Create a workflow rule that updates the above field with a numeric code the represents type.

   Example:

Employee:     1
Key Contact: 100
Menu:           10000
Poster:          100000

Since a numeric field has a maximum of 18 digits, you have several options:
a. Count up to 99 records of 9 different types (99=max of 2 digits, 9 types x 2 digits =18), as in my example
b. Count up to 9 of 18 different types (1 digit per type)
You could also use 999×6 or 9999×4, etc. You could use a different numerical base for even more options.  The other limit you face is the maximum compile size for formulas (3900 characters in the formula field, or 5000 compiled characters).

5. Create a roll-up summary field SUM of the above numeric field on the master object (e.g. Offer_Codes__c).
6. Create a text formula on the object that spits out the appropriate text for each type.

   Example:

"| " &
IF(MOD(Offer_Codes__c,100)>0,"Employee | ","") &
IF(MOD(ROUND(Offer_Codes__c/100,0),100)>0,"Key Contact Offer | ","") &
IF(MOD(ROUND(Offer_Codes__c/10000,0),100)>0,"Menu | ","") &
IF(MOD(ROUND(Offer_Codes__c/1000000,0),100)>0,"Poster | ","") &
IF(MOD(ROUND(Offer_Codes__c/100000000,0),100)>0,"Disclaimer | ","")

You could also use the remainder to tell you how many of each type are listed.

That’s it!  Now if only we could roll up text out of the box…