+ Start a Discussion
Jonathan PenaJonathan Pena 

Automatically assign value to pick list field based on option selected within existing field


I'm looking to create a custom Pick list field on the "Account" object that is automatically populated based on the options of another field within the Account Object.  For example  the new field name is "Type" and the available options are: Strategic and Emerging. I would like the value of this field to be automatically populated based on the value selected under existing picklst field  field "Vertical" within standard account object.  

Vertical field has the follwoing options: Automotive,  CPG, Entertainment, Financial, Oil Gas and Power, Other, Pharmaceuticals, Restaurants, Retail, Tech, Telecommunications, Travel, Insurance

The mapping will be as follow: 
Automotive - Strategic
CPG - Strategic
Entertainment - Emerging
Financial - Strategic
Oil Gas and Power - Emerging
Other - Emerging
Pharmaceuticals - Strategic
Restaurants - Strategic
Retail - Strategic
Telecommunications - Emerging
Travel  - Emerging
Insurance - Strategic

Greatly appreciate any guidance you can provide. 

Best, 
JP
Sure@DreamSure@Dream
Hi JP,

You can create a formula field(Type) instead of a picklist field. You may use the following formula:
CASE(TEXT(Vertical__c),'Automotive' ,'Strategic','CPG','Strategic','Entertainment','Emerging',
'Financial','Strategic',
'Oil Gas and Power', 'Emerging',
'Other','Emerging',
'Pharmaceuticals','Strategic',
'Restaurants','Strategic',
'Retail','Strategic',
'Telecommunications','Emerging',
'Trave','Emerging',
'Insurance','Strategic',
'NA')
If there is no value for Vertical, Type will be NA.

Mark this as the solution, if it solves your problem.

Thanks,
Akhil AnilAkhil Anil
Hi Jonathan,

This is the best use case for a formula field. You just need to create a formula field of the type "Text" in the Account object with the below formula. It's better and optimized to use nested IF and OR condition for such a use case as it will greatly reduce the compilation size of the formula.

The formula posted above by @SureDream has a compilation size of : 402 characters

The formula below has a compilation size of : 233 characters

 
IF(
OR(
TEXT(Vertical__c) = "Automotive",
TEXT(Vertical__c) = "CPG",
TEXT(Vertical__c) = "Pharmaceuticals",
TEXT(Vertical__c) = "Restaurants",
TEXT(Vertical__c) = "Retail",
TEXT(Vertical__c) = "Insurance",
TEXT(Vertical__c) = "Financial"
),
"Strategic",
IF(
OR(
TEXT(Vertical__c) = "Entertainment",
TEXT(Vertical__c) = "Oil Gas and Power",
TEXT(Vertical__c) = "Other",
TEXT(Vertical__c) = "Telecommunications",
TEXT(Vertical__c) = "Travel"
),
"Emerging",
NULL
)
)

Hope that helps.

Kindly mark a solution if that resolves your problem !
Jonathan PenaJonathan Pena
Thank you Akhil. 

I had a few minor adjustments to make as far as the actual value but i'm receiving a " Error: Syntax error. Found ')'" error message. 

Greatly appreciate any assitance you can provide. 

IF(
OR(
TEXT(Vertical__c) = "Automotive",
"Automotive",
)
IF(
OR(
TEXT(Vertical__c) = "CPG",
"CPG",
)
IF(
OR(
TEXT(Vertical__c) = "Entertainment",
TEXT(Vertical__c) = "Oil Gas and Power",
TEXT(Vertical__c) = "Other",
TEXT(Vertical__c) = "Telecommunications",
TEXT(Vertical__c) = "Travel"
"Emerging",
)
IF(
OR(
TEXT(Vertical__c) = "Financial",
TEXT(Vertical__c) = "Insurance",
"FinServ",
)
IF(
OR(
TEXT(Vertical__c) = "Pharmaceuticals",
"Pharmaceuticals",
)
IF(
OR(
TEXT(Vertical__c) = "Restaurants",
"Restaurants",
)
IF(
OR(
TEXT(Vertical__c) = "Retail",
"Retail",)
NULL
)
)