Create unique label & values for a custom Picklist field

Question

I want to create a custom picklist field that would represent a packaging type which would in turn represent a divider for the shipment's total quantity. For example, if the dropdown had the following options:

• 100lb Drum
• 30lb Container

And the total quantity being shipped was 300lbs. Then depending which option was selected from the dropdown I would either be shipping 3 (300/100) or 10 (300/30) units based on the given unit type.

I want the number of units to be a formula field that does this simple equation, but I don't believe I can do that if the picklist is a text field, so I want the picklist value to just be the Integer (either 100 or 30), but the label to be a string that is easy for the user to understand.

My research of this so far says it is not possible so I need to either confirm that and figure out another option, or figure out how it is possible. Thanks in advance for any help.

Answer 1

I've used formula fields to translate people friendly strings into numbers. For example, a start time string into minutes into the day:

CASE(Time__c,
'8 am', 480,
'8:15 am', 495,
'8:30 am', 510,
'8:45 am', 525,
'9 am', 540,
'9:15 am', 555,
'9:30 am', 570,
'9:45 am', 585,
...
)

For your example, "100lb Drum" would be mapped to 100 and "30lb Container" would be mapped to 30 in a UnitWeight formula field. The "300lbs" would be mapped to 300 in a ShippingWeight formula field and the Units formula field would be the ratio.

This only works if the number of picklist entries is reasonably modest so the formula field size limit is not exceeded.

If you don't mind Apex code, another approach is to parse the contributing picklists (e.g. throw away any non-numeric characters) and calculate the values you need in a trigger whenever a contributing picklist changes. This has the benefit that the code will work when extra values are added to the picklists (providing the new values follow the same parseable pattern).