-1

We have a formula field that references a picklist field. It should have data in it only for a certain record type, but somehow in migration it was populated on all records. I need to clear out those values. Since it's not possible to clear values from a formula field with Data Loader, I need to update the formula to say if the Stage picklist field is null, then null.

How do I update this formula to include that?

    If(AND(
    Text(Stage__c) <> "red",
    Text(Stage__c) <> "blue",
    Text(Stage__c) <> "green"), "Active", "Not Active")

UPDATE: commenter gives great additional context to how to look at this. For anyone wondering, the full solution here was this:

    IF(
    ISPICKVAL(Stage__c,""),"",

    IF(AND(
    Text(Stage__c) <> "red",
    Text(Stage__c) <> "blue",
    Text(Stage__c) <> "green"), "Active", "Not Active"))
Improve this question
1
  • Thanks, maybe it would if I better understood formulas and how translate what's there to a formula referencing a picklist. I don't know enough yet to read what's there and reach a conclusion.
    – sna
    Oct 28, 2021 at 5:56

1 Answer 1

Reset to default
2

You can chain IF() in a formula much like you can in code. You just add another IF() in the "then" and/or "else" portions.

We can do things a bit cleaner in code than what I'm about to present, but the equivalent to what you're looking for would be

if(String.isBlank(Stage__c)){
    // then
    return null;
}else{
    if(Stage__c != 'red' && Stage__c != 'blue' && Stage__c != 'green'){
        // then
        return 'Active';
    }else{
        return 'Not Active';
    }
}

Translating to a formula from that is pretty simple

IF(
    ISBLANK(Stage__c),
    /* Then */
    Stage__c,
    IF(
        AND(/* stage checking for red/green/blue */),
        /* Then */
        'Active',
        'Not Active'
    )
)

As always, indenting formulas like they were code helps you keep track of things (it's really easy to get lost in a sea of parenthesis otherwise)

Improve this answer
5
  • Thanks, you're very good at explaining things. I'm afraid I'm new to writing formulas and know nothing about code, so even trying to translate your formula there into something that works...isn't working. I've landed at this:
    – sna
    Oct 28, 2021 at 5:35
  • Stage is a picklist so pretty sure I need the TEXT part because the existing formula uses it. Not sure how after that first 'then' to say if it's blank then null. Also afraid I don't know how to make this look right in comments. IF( ISBLANK(TEXT(Stage__c), TEXT(Stage__c), IF( AND( Text(Stage__c) <> "red", Text(Stage__c) <> "green", Text(Stage__c) <> "blue"), "Active", "Not Active") ) )
    – sna
    Oct 28, 2021 at 5:41
  • @sna There are some functions that can use picklists directly, but most of them do need you to use TEXT(). My primary goal here was to give you a way to think about how to nest IF() inside one another. Also, comments only allow for limited formatting (which is part of why we generally encourage everyone to not attempt to put code/formulas into comments).
    – Derek F
    Oct 28, 2021 at 13:50
  • @sna When you use IF(), it's like choosing between two options (and you will always choose one. Choosing none isn't allowed). Like say "if today is Saturday, then don't set my alarm clock. Else, set my alarm clock". Either the alarm clock is set, or it is not set. So in the outermost IF()'s "then" part, you already checked to see if Stage__c is null. The only thing left to do there is to return a null value (for which I think either TEXT(Stage__c) or null would work).
    – Derek F
    Oct 28, 2021 at 13:56
  • Thanks for the additional context! As it turns out, I was able to accomplish my goal by preceding my original formula with IF(ISPICKVAL(Stage__c,""),"",
    – sna
    Oct 28, 2021 at 17:25

Not the answer you're looking for? Browse other questions tagged .