Ask Search:
Charles CooperCharles Cooper 

Use formula field to output text value based on picklist fields

I feel like there are a lot of other similar requests but I have not been able to get this to work. 

I'm looking to have a field that uses a formula to output a specific text value based on 4 other picklist fields. 

Basically, if field one says X (but other fields are blank), Output Word1. 
If field 1 says x and field 2 says x, output word2
If field 1 says x and field 2 says y, output word3
If field 1 says x and field 2 says x and field 3 says x, out word4. etc. 

I started with this but now I'm lost: 
IF(ISPICKVAL(Status__c,"Offered - Pending Acceptance"),"Pending Offer",
IF(ISPICKVAL(Offer_Status__c,"Offer Accepted"),"Offer Accepted",
IF(ISPICKVAL(Offer_Status__c,"Offer Declined"),"Offer Declined",
IF(ISPICKVAL(Travel__c,"Travel Accepted"),"Travel Accepted",
IF(ISPICKVAL(Travel__c,"Travel Declined"),"Travel Declined",
IF(ISPICKVAL(Assigned__c,"Assigned"),"Assigned - Pending Induction",
IF(ISPICKVAL(Assigned__c,"Removed"),"Declined/Removed From Assigned",Null)))))))


Field 1 (Status__c) has multiple values, and it would good if no other fields have a value, use whatever value is in field 1 as output of formula. 

For reference, these are depedent fields and are specific parts of a process, so while they could be all in one field, we want to keep them seperate to segment the different parts of the process. 
Best Answer chosen by Charles Cooper
Steve MolisSteve Molis
CASE(Assigned__c,
"Assigned","Assigned - Pending Induction",
"Removed","Declined/Removed From Assigned",
CASE(Travel__c,
"Travel Accepted","Travel Accepted",
"Travel Declined","Travel Declined",
CASE(Offer_Status__c,
"Offer Accepted","Offer Accepted",
"Offer Declined","Offer Declined",
CASE(Status__c,
"Offered - Pending Acceptance",
"Pending Offer",
NULL))))

 

All Answers

Steve MolisSteve Molis
So something like this?
CASE(Status__c,"Offered - Pending Acceptance",
"Pending Offer",
CASE(Offer_Status__c,
"Offer Accepted","Offer Accepted",
"Offer Declined","Offer Declined",
CASE(Travel__c,
"Travel Accepted","Travel Accepted",
"Travel Declined","Travel Declined",
CASE(Assigned__c,
"Assigned",
"Assigned - Pending Induction",
"Removed","Declined/Removed From Assigned",
NULL))))

 
Charles CooperCharles Cooper
I'm not super familiar with CASE but going through process it will change to Offer Accepted but no other values. 
 
Steve MolisSteve Molis
Can you write out an example of the expected formula resuly of your last scenario  using the actual picklist fields and values instead of 

"If field 1 says x and field 2 says x and field 3 says x, out word4. etc." 
Charles CooperCharles Cooper

Absolutely! That will probably help. 

There are 4 picklist fields: Status, Offer Status, Travel, Assigned. 

If Status says Offered - Pending Acceptance, but other fields blank = Pending Offer
If Status says Offered - Pending Acceptance, and Offer Status says Offer Accepted = Offer Accepted
If Status says Offered - Pending Acceptance, and Offer Status says Offer Declined = Offer Declines
If Status says Offered - Pending Acceptance, and Offer Status says Offer Accepted, and Travel says Travel Accepted = Travel Accepted
If Status says Offered - Pending Acceptance, and Offer Status says Offer Accepted, and Travel says Travel Declined = Travel Declined
If Status says Offered - Pending Acceptance, and Offer Status says Offer Accepted, and Travel says Travel Accepted, and Assigned says Assigned = Assigned - Pending Induction
If Status says Offered - Pending Acceptance, and Offer Status says Offer Accepted, and Travel says Travel Accepted, and Assigned says Removed = Declined/Removed From Assigned

Steve MolisSteve Molis
So baically something like this?
 
IF(TEXT(Status__c) = "Offered - Pending Acceptance",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Accepted",
TEXT(Assigned__c) = "Removed"
),
"Declined/Removed From Assigned",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Accepted",
TEXT(Assigned__c) = "Assigned"
),
"Assigned - Pending Induction",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Accepted"
),
"Travel Accepted",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Declined"
),
"Travel Declined",
IF(
TEXT(Offer_Status__c) = "Offer Accepted",
"Offer Accepted",
IF(
TEXT(Offer_Status__c) = "Offer Declined",
"Travel Declined",
"Offered - Pending Acceptance")))))))

 
Steve MolisSteve Molis
Wait, did you say that all of these fields are Dependant Picklists?
Charles CooperCharles Cooper
On that, Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2

They are dependent. Status must be Offered - Pending Acceptance to access Offer Status, Offer Status must be  Offer Accepted to access Travel. Travel must be Travel Accepted to access Assigned. 
Steve MolisSteve Molis
Ah! I missed one, try this 
IF(TEXT(Status__c) = "Offered - Pending Acceptance",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Accepted",
TEXT(Assigned__c) = "Removed"
),
"Declined/Removed From Assigned",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Accepted",
TEXT(Assigned__c) = "Assigned"
),
"Assigned - Pending Induction",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Accepted"
),
"Travel Accepted",
IF(
AND(
TEXT(Offer_Status__c) = "Offer Accepted",
TEXT(Travel__c) = "Travel Declined"
),
"Travel Declined",
IF(
TEXT(Offer_Status__c) = "Offer Accepted",
"Offer Accepted",
IF(
TEXT(Offer_Status__c) = "Offer Declined",
"Travel Declined",
"Offered - Pending Acceptance",
"Offered - Pending Acceptance" )))))))

 
Steve MolisSteve Molis
If the Picklists are all dependent, then this might have gotten a LOT easier
Steve MolisSteve Molis
CASE(Assigned__c,
"Assigned","Assigned - Pending Induction",
"Removed","Declined/Removed From Assigned",
CASE(Travel__c,
"Travel Accepted","Travel Accepted",
"Travel Declined","Travel Declined",
CASE(Offer_Status__c,
"Offer Accepted","Offer Accepted",
"Offer Declined","Offer Declined",
CASE(Status__c,
"Offered - Pending Acceptance",
"Pending Offer",
NULL))))

 
This was selected as the best answer
Charles CooperCharles Cooper
We are very close... everything is working accept if the value is Offered - Pending Acceptance it's blank. 

User-added image
Also thanks! 
Charles CooperCharles Cooper
Okay, I just replaced your last NULL with "Pending Offer" again and it's working. I'm happy, thanks so much!