function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ankur GurhaAnkur Gurha 

Need help in creating a formula field to retrive a phone number from multiple phone fields with a set logic

Desired Logic:

Field 1: CAS Phone 1 (New Field in Salesforce)

Desired Logic:

If "Mobile" is not blank, then CAS Phone 1 = "Mobile"
If "Mobile" is blank, then CAS Phone 1 = "Work Direct"
If "Work Direct" is blank, then CAS Phone 1 = "Business Phone"
If "Business Phone" is blank, then CAS Phone 1 = "Corporate Phone"

Able to create this field with below formula:-

BLANKVALUE(MobilePhone, BLANKVALUE(Work_Direct_Phone__c, BLANKVALUE(Phone, BLANKVALUE(Corporate_Phone__c,"") ) ))

Field2: CAS Phone 2 (New Field In Salesforce)

Desired Logic:

If CAS Phone 1 = "Mobile," then CAS Phone 2 = "Work Direct"
If CAS Phone 1 = "Mobile" AND "Work Direct" is blank, then CAS Phone 2 = "Business Phone"
If CAS Phone 1 = "Mobile" AND "Work Direct" is blank AND "Business Phone" is blank, then CAS Phone 2 = "Corporate Phone"
If CAS Phone 1 = "Work Direct," then CAS Phone 2 = "Business Phone"
If CAS Phone 1 = "Work Direct" AND "Business Phone" is blank, then CAS 2 = "Corporate Phone"
If CAS Phone 1 = "Business Phone," then CAS Phone 2 = "Corporate Phone"
If CAS Phone 1 = "Corporate Phone," then CAS Phone 2 is blank

Not able to create formula for this.

Can some please help
 
Andrew GAndrew G
check out the use of the CASE formula
try something like: 
CASE( CAS Phone1,
'Mobile', IF(AND(ISBLANK(BusinessPhone),ISBLANK(WorkDirect), CorporatePhone, 
                 IF(ISBLANK(WorkDirect), BusinessPhone, 
                 'Work DIrect')
               ),
'Work Direct', IF (ISBLANK(BusinessPhone), CorporatePhone, WorkDirect),
'Business Phone', CorporatePhone,
'Corporate Phone', '',
''
)
That should put you in the right direction.,

cheers
Andrew

 
Ankur GurhaAnkur Gurha

Hello Andrew,

Thank you for a prompt response. Can you please check the below formula if this is correct as I am getting a syntax error stating "Error: Syntax error. Missing ')'"

 

CASE( Phone_Field_CAS_1__c,
'MobilePhone', IF(AND(ISBLANK(primaryphone__c),ISBLANK(Work_Phone__c), Corp_HQ_Phone__c, 
                 IF(ISBLANK(Work_Phone__c), primaryphone__c, 
                 'Work_Phone__c')
               ),
'Work_Phone__c', IF (ISBLANK(primaryphone__c), Corp_HQ_Phone__c,  Work_Phone__c),
'primaryphone__c', Corp_HQ_Phone__c,
'Corp_HQ_Phone__c', '',
''
)
Andrew GAndrew G
best way to resolve these issues is to ensure we format the formula.  note, the one I supplied was free-texted and not run via an editor. 
CASE(
   Phone_Field_CAS_1__c,
   'MobilePhone', IF(
                              AND(
                                 ISBLANK(primaryphone__c),
                                 ISBLANK(Work_Phone__c)
                               ), 
                               Corp_HQ_Phone__c, 
                               IF(
                                 ISBLANK(Work_Phone__c), 
                                 primaryphone__c, 
                                 'Work_Phone__c'
                               )
                             ),
'Work_Phone__c', IF (
                                ISBLANK(primaryphone__c),
                                Corp_HQ_Phone__c,  
                                Work_Phone__c
                               ),
'primaryphone__c', Corp_HQ_Phone__c,
'Corp_HQ_Phone__c', '',
''
)

try that
 
Ankur GurhaAnkur Gurha

Thank you Andrew,

 

The formula worked without any error, however the field I created is not populating any value from any of the phone fields whereas all fields have values in Salesforce. Is there a reason?