0

in my custom Activities report type, I need to show which object the activitiy is related to, e.g. lead, account. Contact or lead activities have an ID in the field WhoId, account, opportunities and shipment (custom object) activities have the WhatID filled in.

I am trying to create a formula field on activities that would check the three first ID characters of WhoId, or if this one is empty the WhatId and return a defined value.

My formula keeps on returning the syntax error "Missing ')'" which I'm sure is not a problem. The issue must be probably with the CASE or the ISBLANK functions that were wrongly used. I keep on rebuilding the whole formula but it didn't work so far:


IF(ISBLANK(WhatId),
    (CASE(LEFT(WhoId,3),"003","Contact",
                        "00Q","Lead"),
    (CASE(LEFT(WhatId,3),"001","Account",
                        "006","Opportunity",
                        "0YP","Shipment"),                      
    "Other")
     )
)

I would be grateful for any suggestions.

Improve this question

1 Answer 1

Reset to default
1

Formatting formulas like code and keeping things on separate lines can help identify issues.

Applying that (perhaps a bit overzealously) to your formula yields

IF(
    ISBLANK(WhatId),
    (
        CASE(
            LEFT(WhoId, 3),
            "003", "Contact",
            "00Q", "Lead"
        ),
        (
            CASE(
                LEFT(WhatId,3),
                "001", "Account",
                "006", "Opportunity",
                "0YpP", "Shipment"
            ),
            "Other"
        )
    )
)

The parenthesis appear to all be matched, but the issues I see are:

  • Your CASE() functions are missing the default value (for when there is no match), which is required
  • "Other" is just kinda hanging out in space (and seems like it should be part of the CASE() functions)
  • Your IF() doesn't have the 3rd argument (for the "value if the expression is false", i.e. the "else" part)

Some simple re-arrangement should fix things here

IF(
    ISBLANK(WhatId),
    /* Getting rid of extraneous parenthesis */
    CASE(
        LEFT(WhoId, 3),
        "003", "Contact",
        "00Q", "Lead",
        /* The final else_value for CASE() is required */
        "Other"
    ),
    /* The "else" part of the IF() is required */
    CASE(
        LEFT(WhatId, 3),
        "001", "Account",
        "006", "Opportunity",
        /* reducing this prefix to length 3 (otherwise it'll never be matched) */
        "0Yp", "Shipment",
        /* The final else_value for CASE() is required */
        "Other"
    )
)
Improve this answer
1
  • thanks a lot. This worked for me now. I also thought adding the final else for the first CASE is necessary but was worried that it would then put "Other" for all records with a WhatId. I thought my second CASE function should be treated as an else for the first one.
    – MarkB
    Commented Dec 6, 2022 at 19:55

You must log in to answer this question.

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