Trying to update country code in phone field with Workflow rule

data-typesformula-fieldworkflowrule

Trying to update country code in phone field with Workflow rule.
In which rule will check the Country(picklist type) and add the country code before mobile number(Phone type) automatically.
Tried so many combinations but either it's not working or showing error before even get saved.
Last combination that I tried –

OR( 
    NOT( 
        ISBLANK( 
            IF( 
                ISPICKVAL( Country__c , "INDIA"), 
                (TEXT(+91) & Mobile_Landline__c ),
                IF( 
                    ISPICKVAL( Country__c , "USA"), 
                    (TEXT(+1) &  Mobile_Landline__c ), 
                    " "
                )
            )
        )
    )
)

Error – Formula result is data type (Boolean), incompatible with expected data type (Text)

IF(
    ISPICKVAL( Country__c , "INDIA"), 
    (TEXT(+91) & Mobile_Landline__c ),
    IF( 
        ISPICKVAL( Country__c , "USA"), 
        (TEXT(+1) &  Mobile_Landline__c ), 
        " "
    )
)

This one get saved but didn't worked.

IF(
    ISPICKVAL( Country__c , 'USA') && NOT(ISBLANK( Mobile_Landline__c )) && NOT(REGEX( Mobile_Landline__c ,"[+1]{2}[1-9]{1}[0-9]{9}")), 
    TRUE, 
    IF(
        ISPICKVAL(Country__c,'India') && NOT(ISBLANK( Mobile_Landline__c )) && NOT(REGEX( Mobile_Landline__c ,"[+91]{3}[1-9]{1}[0-9]{9}")), 
        TRUE, 
        IF(ISPICKVAL(Country__c,' ') && NOT(ISBLANK( Mobile_Landline__c )) && NOT(REGEX( Mobile_Landline__c ,"[0-9]{10}")), 
            'TRUE', 
            'FALSE'
        )
    )
)

Error – Incorrect parameter type for function 'IF()'. Expected Boolean, received Text

(Found this one in discussion forums)

Any suggestions?

Best Answer

As a general rule, indenting formulas like you would if they were code (which I've done for you in your question) really reduces the cognitive load (i.e. makes it easier to read/understand). That's helpful when you're trying to debug things.

The first example you gave looks like it was pretty close (if you are using it as the formula for a field update action), you just added on OR, NOT and ISBLANK for some reason. The second example should have worked (again, if you are using it as the formula for a field update action).

Workflow rules have two parts to them:

  • The execution criteria
  • Actions to take if the execution criteria has been met

If you're trying to use these formulas as the execution criteria, that's not what you want to do here. The execution criteria isn't where the field update happens, it's supposed to tell us whether or not we need to update the field. The execution criteria should be something like ISCHANGED(Country__c) (you only want to update the phone field if the Country has changed, possibly also when the record is first created).

The second example formula you gave could be used for the field update action, but I think that using the CASE() function would be easier to read, and save you some typing. Something like

CASE(
    Country__c,
    "India", "+91",
    "USA", "+1",
    /* Much like IF(), CASE() needs a default value to return if none of the previous options are matched */
    ""
) & Mobile_Landline__c

That's a starting point, not the final solution. If you took this as-is, any update to the Country would continue to append more and more country codes to your phone number until you hit the 40-character limit. You'd end up with something like +91+1+91+1+91+11234567890

To avoid that, you'd need to remove the country code from Mobile_Landline__c (if it has a country code, which you would need to find a way to detect) instead of simply appending the new country code to it. The RIGHT() function could help there, provided you know how many characters need to be retained. Another option would be to adjust the workflow's execution criteria to return FALSE if the phone number already has a country code (i.e. starts with a '+').

Related Topic