function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Gary PayneGary Payne 

Need help with cross-object CASE formula for Lookup Relationship

I need help with the following formula: 
CASE( SourceCode__c , Source_Cross_Reference__r.External_Unique_Id__c , Source_Cross_Reference__r.Name ,"N/A")
The SourceCode__c is a custom Lead text field that is populated via the Data Loader with an alphanumeric value, like A1234. I have created a custom object, Cross Reference, that has a lookup relationship with the Lead object. I want to pull the Cross Reference Name associated with the Source Code value, which equals the External Unique Id field value in the Cross Reference object.  I cannot get this formula to work in a Workflow Update, nor in a Formula field on the Lead object. Does anyone have a solution that might work?
Rajiv Bhatt 16Rajiv Bhatt 16
I am afraid that you may not be able to do this with workflow and will have to write a trigger. Can you please provide an elaborated example (with data values) to give you a precise answer.
Gary PayneGary Payne
Following is actual data for this issue: Lead object – API field names: Source__c (picklist), SourceCode__c (Text), Source_Cross_Reference__c (Lookup(Cross Reference)), SourceCrossRefName__c (Formula-Text: CASE( SourceCode__c, Source_Cross_Reference__r.External_Unique_Id__c , Source_Cross_Reference__r.Name ,"N/A") Values: Source__c = “E4198” this picklist field is being populated via automated Data Loader process SourceCode is a Text field that is updated to the Source__c value via workflow update Cross Reference is a custom object that is related to the Lead object via Source_Cross_Reference__c (Lookup(Cross Reference)) CrossRef__c object has text fields: External_System__c, External_Unique_Id__c, and Name Formula for the Lead object’s SourceCrossRefName__c: CASE( SourceCode__c, Source_Cross_Reference__r.External_Unique_Id__c , Source_Cross_Reference__r.Name ,"N/A") Data Values: Source__c = “E4198” this picklist value is updated to the SourceCode__c field CrossRef__c field value for External_Unique_Id__c = “E4198” CrossRef__c field value for Name = “HUDSON VALLEY” CrossRef_c field value for External_System__c = “HUDSON VALLEY” (possible future duplicates in the Unique Id field but there won’t be the same Unique ID + External System values combined) Need to update the Lead’s Cross Reference Name field based on the SourceCode__c value. Regards, Gary Payne, CRM Administrator/Information Technology SWBC 9311 San Pedro Ave., Suite 600 San Antonio, TX 78216 888-391-6707 - Toll Free/ Help Desk 210-376-6057 - Direct 714-474-5915 - Mobile 210-468-4135 - Fax [cid:image001.png@01D0575E.271DC870] [Description: Description: Description: Description: facebook-official-28px] [Description: Description: Description: Description: linkedIn-official-28px] [Description: Description: Description: Description: youTube-sm] [Twitter_logo_email] [cid:image007.png@01D0575E.271DC870] Visit our website at