0

In my report, I want to show activities that the created date is BEFORE the account close date and account close date is NOT blank. I have the first part, but cannot figure out how to add in the account close date is not blank piece. My statement should be:

Account close date is blank = 0
Activity Date > Close Date = 0
Activity Date < Close Date = 1

This is what I'm working with:

IF(
    ISNULL(Account.Account_Close_Date__c),
    0,
    1
)
OR(
    Activity.Activity_Date_Time_t__c < Account.Account_Close_Date__c,
    1,
    0
)

EDITED FORMULA

IF(
    OR(
        DATEVALUE(Activity.Activity_Date_Time__c < Account.Account_Close_Date__c,
        1,
        0
    )
    ISNULL(Account.Account_Close_Date__c, 0, 1)
)
)

Error when encoding row-level formula: Syntax error. Missing ')'

3
  • This is somewhat similar to this other question that I answered yesterday in that you have made the same mistake about using OR() that they did. Please check that one out to see if it helps you with your issue. If it doesn't, then please edit your question to show an updated attempt.
    – Derek F
    Apr 26 at 17:45
  • Thank you - not there yet but closer?? IF( OR( DATEVALUE(Activity.Activity_Date_Time__c)<Account.Account_Close_Date__c,1,0 ), ISNULL(Account.Account_Close_Date__c) )
    – Stephanie
    Apr 26 at 17:59
  • Please edit your question to add that. Comments are temporary and have fewer formatting options (which makes them harder to read). Also, if you're getting any error messages, you should include them as well (the entire message, verbatim. Paraphrasing errors generally means you're gonna leave out important information).
    – Derek F
    Apr 26 at 18:02

1 Answer 1

0

Formulas are kinda close to programming, so you can't just throw things together in whatever order and hope for it to work. You need to pay attention to what each function you call needs to take (and how much, and in what order) and what each function returns as a result. Looking at the help page on Formula Operators and Functions is a good habit to get into. There's also a quick help reference for functions that you see on the right-hand side of the window where you write your formulas.

OR() is a Boolean function. It takes at least one Boolean (true/false) input, or expressions that return Booleans (e.g. Name = "DerekF", you get a true/false from comparisons). So the following is incorrect

OR(
    DATEVALUE(Activity.Activity_Date_Time__c) < Account.Account_Close_Date__c,
    1,
    0
)

because 1 and 0 are integers, not boolean true/false (and also because those are the outputs that you want from your formula, and your OR() isn't the last thing (not a "top-level" thing) that your formula is doing)

IF() takes 3 parameters (all 3 are required, and they must be in this order):

  1. A boolean expression
  2. The value to return if the expression is true (the "then" part)
  3. The value to return if the expression is false (the "else" part)

Like I said in the question I linked to in the comments, writing out what you want to do in plain language can be helpful and the resulting formula should read very similar to the plain language description.

It takes a bit of knowledge and practice to know how to draft a formula in plain language. One thing that's good to keep in mind is that your formula must only return a single result, and it must match the type that you told Salesforce your formula will return.

So the following would be incorrect because you're trying to return more than one result:
If the account close date is blank, then return 0. Otherwise return 1. If the activity date is greater than the close date, then return 0. Otherwise return 1.

In your case, the plain language description would be
If the account close date is blank or the activity date is greater than the close date, then return 0. Otherwise, return 1

turning that into an actual formula

IF(
    OR(
        ISBLANK(Account.Account_Close_Date__c),
        /* Also notice that for every open paren you have, it needs to have a */
        /*   matching close paren. */
        /* Parenthesis are mainly used to tell you where a function starts and ends */
        DATEVALUE(Activity.Activity_Date_Time__c) > Account.Account_Close_Date__c
    ),
    0,
    1
)

I hope it's clear how this formula is pretty similar to how we described it in plain language.

1
  • Thank you! Such a great explanation.
    – Stephanie
    Apr 26 at 19:01

You must log in to answer this question.

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