salesforcepicklist

How to Use Picklist Value to Formula in Salesforce

I have a custom field picklist with field name CompanyClassify__c(★) including values: Government offices／Group companies／Listed company／Listed subsidiary／Other.

I have a custom number field with field name CalcLevelNum__c(★★) and want to calculate the value based on (★)'s selection.

For example:
If (★) is selected as Government offices, 20 will be taken plus the value entered in another custom number field,
If (★) is selected as Listed company, 36 will be taken plus the value entered in another custom number field.

At that time, in the Default Value section of (★★) I set up the following formula but got the error: Error: Field CompanyClassify__c may not be used in this type of formula

My formula is as follows:

CASE (CompanyClassify__c,
"Government offices",20 + LevelNum__c,
"Group companies",20 + LevelNum__c,
"Listed company",36 + LevelNum__c,
"Listed subsidiary",48 + LevelNum__c,
"Other",60 + LevelNum__c)

■■■UPDATE

CASE(TEXT(CompanyClassify__c),
    "Government offices",20,
    "Group companies",20,
    "Listed company",36,
    "Listed subsidiary",48,
    60
)
+ LevelNum__c

enter image description here

Because I'm newbie of Salesforce, I don't know what the error is and how to fix the formula.
Can anyone Help ? Thanks!

Solution

  • Original answer

    Picklists are historically bit funny in formulas. You need either

    IF(ISPICKVAL(CompanyClassify__c, 'Government Offices'), do_something_if_true, do_something_if_false)

    Or if you want to use them in a CASE statement / flatten them to their string representation with TEXT(CompanyClassify__c)

    So for your scenario I think this would be simplest

    CASE(TEXT(CompanyClassify__c),
    "Government offices",20,
    "Group companies",20,
    "Listed company",36,
    "Listed subsidiary",48,
    60
)
+ LevelNum__c

    Edit

    Ah, you're doing it in a "default field value" formula. I thought you want a real new formula field (of type formula, not of type number). Won't work, the defaults are calculated just once when the "new" page renders. There's no fancy "as I change 1 field recalculate other fields" in UI, at least not without custom coding. The defaults must be hardcoded or can depend on who's looking (like based on some field read from user record) but can't depend on other fields.

    Do you need this new field to be editable, overridable by user? Or is it a hard mapping rule?

    If it's "hard" I'd say delete this field and make new one with same name but type = "Formula" and on next page you specify subtype = number. Nice and easy.

    If you want it to be editable, overridable by the user you'll need to go back to business and get some more logic from them. Once overridden - does it stay overridden even if CompanyClassify__c or LevelNum__c changes? Or on change they "win" and wipe the override? If user changes all 3 (classify, level and calculate level number) - the level number entered by the user should win?

    One (fairly simple) way would be to have a Level number override number field. And then your formula would be

    BLANKVALUE(Level_Number_Override__c,
    CASE(TEXT(CompanyClassify__c),
        "Government offices",20,
        "Group companies",20,
        "Listed company",36,
        "Listed subsidiary",48,
        60
    )
    + LevelNum__c
)

    if user entered something - it wins, else fallback. Easy to write, test and report on "records with overrides" to spot any issues.

    So 4 fields total: company classify (picklist), level num (number), calculate level (formula), calculate level override (number)

    If you need something fancier that changes the Calculate_Level_Number__c when CompanyClassify__c or LevelNum__c change (unless user changed the Calculate_Level_Number__c` too in same edit) - you're looking at some simple proper automation like "early flow" or an apex trigger.