Rolling up Primary Contact Picklist Value to text on Account

Question

I'm attempting to solve what I think is a fairly simple problem. We are using NPSP, and track our board members on the Contact level. For some complex reporting reasons, I need a way to simply display the value of the Accounts Primary Contact Board Member Status as text on the account record.

I could do this in flow pretty easily, but would like to try a formula first.

My current formula is as follows:

IF(AND(ISPICKVAL(npe01__Primarycontact__r.Board_Member__c, "Current", Current),
IF(AND(ISPICKVAL(npe01__Primarycontact__r.Board_Member__c, "Former", Former),
IF(AND(ISPICKVAL(npe01__Primarycontact__r.Board_Member__c, "Null", Null)

The values for the picklist in question are Current, Former, and no selection/null.

When attempting to save I'm getting the "missing parentheses" error.

I originally tried this formula which I found on trailhead

IF(AND(ISPICKVAL( Picklist , "A"), FormulaField = "A"), "Result A",

IF(AND(ISPICKVAL( Picklist , "B"), FormulaField = "B"), "Result B",

IF(AND(ISPICKVAL( Picklist , "C"), FormulaField = "C"), "Result C",

FormulaField )))

However I'm unsure of how to populate this with my current fields or if it would even work. I'm not sure what "FormulaField" would be, since it's not accessible to me as I haven't saved the current formula I'm working on.

Any assistance would be greatly appreciated! Or if there's another, simpler way I can achieve this, that would be great too!

Answer 1

Your formula has several issues with incorrect number of parameters and missing closing parentheses. But above all, if the account formula field should always mirror the Board Member field, then why not just use

TEXT(npe01__Primarycontact__r.Board_Member__c)

If you need to use a more complex formula, a CASE would work too, like this:

CASE(
    npe01__Primarycontact__r.Board_Member__c,
    "Current", "Currently board member",
    "Former", "Used to be board member",
    "Prospective", "we hope they'll join us",
    NULL
)

You should check the Formula/Function documentation to understand these functions and parameters. Note that NULL is a constant, you do not need to put it in quotes.

Also be sure to check out Salesforce Trailhead and Youtube channel for tutorials on formulas.

Also when building a complicated formula, it's best to use a text editor with brace / bracket matching to help you close your parentheses, as well as auto-indenting to make things readable.