Hi, please bear with me for a little bit. We have a custom object, Service, that has a picklist, also Service, and a multi-select picklist, Subtype. For an unknown reason Subtype was not made dependent on Service. Instead administrators select allowable Subtype values for each service manually.
Another custom object, ServiceAuth, has a lookup to Service.Service__c. It also has a Subtype multi-select picklist. Its allowable values are filtered to the values chosen in Service.Subtype__c. This is controlled by code. I know!
Finally, I am making a third custom object, Voucher. It has a lookup to ServiceAuth, and a formula field that gets the ServiceAuth.Service__c value. I need to make a picklist for Subtype that only allows the user to select the values chosen in ServiceAuth.Subtype__c, and would prefer to avoid more code. Am I out of luck?
You're not out of luck! While Salesforce doesn't provide a built-in feature to directly create a dependent picklist from a chosen multi-select picklist, you can achieve this with a combination of formula fields and validation rules.
Here's a general approach you can take:
Formula Field on Voucher: Create a formula field on the Voucher object to pull the ServiceAuth.Subtype__c values. This formula field can concatenate the selected Subtype values from the related ServiceAuth record.
Validation Rule: Create a validation rule on the Voucher object to check if the selected Subtype value matches any of the concatenated values from the formula field. If it doesn't match, prevent the record from being saved.
Here's a simplified example:
Formula Field (Concatenated_Subtypes__c) on the Voucher object:
This formula field concatenates the selected Subtype values from the related ServiceAuth record.
Formula:
TEXT(ServiceAuth__r.Subtype__c)
Validation Rule on the Voucher object:
This validation rule ensures that the selected Subtype value matches one of the values in the concatenated Subtypes from the related ServiceAuth record.
Rule Criteria:
NOT(ISPICKVAL(Voucher_Subtype__c, Voucher__r.Concatenated_Subtypes__c))
Error Message:
The selected Subtype is not valid. Please choose a valid Subtype from the list provided.
