Custom field based on selection from a very large picklist field - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Paul HPaul H 

Custom field based on selection from a very large picklist field

Here is my problem.....

We execute a number of events where contacts are only invited if their job title matches a defined job title criteria (i.e. a qualified title, defined by us).

We have an existing single picklist field (Client_Title__c) with ~500 value options. This is the list of all potential titles we recognize in our organization and includes titles that will allow the contact to be invited to events as well as titles that will not.

I need to create a custom field (let's call it "Title_Eligible__c") that returns a Yes or No value based on whether the "Client_Title__c" option selected is considered to be qualified or not.

I've looked into the following options:

1. Use a Controlling Field (i.e. "Client_Title__c" determines whether "Title_Eligible__c" is a Yes or No) 
   - however, for this to work the controlling picklist field needs to have <300 values (ours is ~500)

2. Use Formula (i.e. ISPICKVAL Client_Title__c = "list all potentially qualified titles on a separate line")
   - however, a formula can only be a max of 3,900 characters and having to write out each possible ISPICKVAL field line is ~15,000 characters!

3.. Use VLOOKUP
   - however, this is only valid for Valdation Rules, not on custom fields

Is there anything I'm missing? Is there another way to do this?
Can the formula option be used using a much more simplified syntax?
sakshi nagpalsakshi nagpal
Consider this-:
You can store controlling picklist values in custom metadata, Then use process builder,to update value to Yes/No
 
Shawn KurugantiShawn Kuruganti
Hi Paul,

I'm not sure if there is an easy way to use formula, unless the non-eligible list is quite small compared to those who are eligiable. However, perhaps I could help with a suggestion to refactor the picklist into actual records and a look-up field instead...
  • For instance, create a new object called "Client Title" then create a lookup field from your Contact object to the Client Title object.
  • Next create two fields on the Client Title object, one text field called "Title" then a checkbox field called "Eligible"
  • Then create a CSV with 2 columns a row for each Title in the picklist and a true or false value in the "Eligible" column. Insert those records into your new "Client Title" Custom Object.
  • Next you can pull the Client Title records out with their Title values into a CSV, pull the Contacts with Id and Client Title values into another CSV, vLookUp to match the Contacts to Client Title records by useing the "Client Title" picklist value as a key on the "Title" text value of the Client Title records.
  • Now you have a list of Contact Ids along side the Client Title Record Ids, update the Contacts to store the Client Title Record Ids in the new Lookup Field.
This is a little bit of work upfront, but now you can always formula your way to the Eligible value of the looked-up Client Title record, you can insert new values into that object as you go and use it to maintain this huge list moving forward. 

No more 500 picklist value field to maintain :).

I hope that is clear and helpful, let me know if you have any questions, or find a better solution.

Best,
Shawn