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:
- Create a master/detail relationship between two objects.
- Create a picklist field on the detail object for calculation.
- Create a numeric field on the detail object for use in calculations (e.g. Offer_Code__c).
- Create a workflow rule that updates the above field with a numeric code the represents type.
- Create a roll-up summary field SUM of the above numeric field on the master object (e.g. Offer_Codes__c).
- Create a text formula on the object that spits out the appropriate text for each type.
Key Contact: 100
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).
"| " &
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â€¦