Calculated Field functions

Hi Brian Curran,

Off the cuff, I came up with the following formula.

I used date_entered and date_modified because those were available date fields in the Accounts module in my instance. If you change the fields and try this, let us know how it works out:

ifElse(
  not(greaterThan(strlen(toString($date_entered)),0)),
  "Start date to be confirmed",
  ifElse(
    greaterThan(number(timestamp($date_entered)),number(timestamp(today()))),
    "Inactive",
    ifElse(
      or(greaterThan(number(timestamp(today())),number(timestamp($date_entered))),equal(timestamp(today()),timestamp($date_entered))),
      ifElse(
        not(greaterThan(strlen(toString($date_modified)),0)),
        "Active",
        ifElse(
          greaterThan(number(timestamp(today())),number(timestamp($date_entered))),
          "Ended",
          ""
        )
      ),
      ""
    )
  )
)

Hi Patrick,

Thanks for this, most of it works...

Start date empty = "Start date to be confirmed"

Start date in the past = "Active"

Start date in the future = (blank, nothing displayed although it should be "Inactive")

End date in the past = "Ended"

End date in the future = "Ended" which is incorrect, as it's still active for now.

ifElse(
not(greaterThan(strlen(toString($date_started_c)),0)),
"Start date to be confirmed",
ifElse(
greaterThan(number(timestamp($date_entered)),number(timestamp(today()))),
"Inactive",
ifElse(
or(greaterThan(number(timestamp(today())),number(timestamp($date_started_c))),equal(timestamp(today()),timestamp($date_started_c))),
ifElse(
not(greaterThan(strlen(toString($date_ended_c)),0)),
"Active",
ifElse(
greaterThan(number(timestamp(today())),number(timestamp($date_started_c))),
"Ended",
""
)
),
""
)
)
)

I may have mixed up the fields when swapping for mine, any suggestions?

EDIT:
Oops! Line 5 was incorrect so I amended it and that now works fine. Just need to account for 'future' end dates now

Many thanks

Brian.

Hi Brian Curran,

When troubleshooting complex formulas, I debug by creating a custom textfield type field and put parts of the formula into those fields so the output of just that section can be displayed.

What is the output of this part?

greaterThan(number(timestamp(today())),number(timestamp($date_started_c)))

Conversely, what is the output of this part?

greaterThan(number(timestamp($date_entered)),number(timestamp(today())))

It could also be insightful to know the output of this part:

equal(timestamp(today()),timestamp($date_started_c)))

Brian Curran,

I just noticed you still have $date_entered in the "Start date in the future" part of the formula.

greaterThan(number(timestamp(today())),number(timestamp($date_started_c)))

True if the Start date is in the past

False if the Start date is in the future

greaterThan(number(timestamp($date_started_c)),number(timestamp(today())))

True if the Start date is in the future

False if the Start date is in the past

equal(timestamp(today()),timestamp($date_started_c)))

Hi Brian Curran,

My fault on the cop and paste of that last one. It has one too many parentheses at the end.

It looks like it should be:

equal(timestamp(today()),timestamp($date_started_c))

It works in the larger formula because that last parenthesis was the closure of the or() function.

That returns False for dates in the past and in the future but it returns True when using today's date.

Hi Brian Curran,

Okay. If those are all as desired, then that's great!

Did you see my other comment that the "Start date in the future" part was probably just returning undesired results because you still had $date_entered in that part of the formula?

Yes thanks Patrick, I spotted it just after posting so promptly amended it.

The only bit that doesn't quite work is the "$date_ended" part, it returns "Ended" even if the end date is in the future, which isn't technically correct. Is there a way to compare that date field to today and only return "Ended" if the date is in the past?

I'm not sure I'll ever grasp this 'language', is it specific to SugarCRM or is it a known piece of programming code?

Brian.

Hi Brian Curran,

My apologies. I saw your amendment, but I interpreted it all wrong. I thought you were referring to the fifth of your list of what was working/not working. I now understand my error.

Regarding the end date returning "Ended," is line 13 supposed to read:

greaterThan(number(timestamp(today())),number(timestamp($date_ended_c))),

--------------

Here is an off-the-cuff crash course example regarding Sugar's Calculated Field Formulas:

Calculated field logic is written as PHP function calls and corresponding JavaScript behaviors. I would not describe it as entirely unique to Sugar. I appreciate that in JavaScript and PHP, like in Excel formulas, processes like math can be done with common symbols in the code, whereas Sugar's interface handles these as functions. Handling them as function calls instead of symbols between parameters is where users can have difficulty.

Example:

For this example, $field_name is a textfield (not a number) but contains a string of a number, "5".

Another field contains this formula:

add(number($field_name),45)

add() is a function that accepts at least two parameters. Those parameters must be of number type before this function can process them. Like PHP functions, they can accept other functions and variables as parameters.

$field_name is a variable.

number() is a function that accepts one parameter.

45 is an Integer type number.

This add() function has two parameters separated by a comma:

number($field_name)

45

The number function converts $field_name into a number and outputs the Integer: 5.

The add function adds 5 and 45, outputting 50.

It gets difficult to read when you have multiple layers of embedded functions, as is the case in the ifElse() formula we are discussing here. But, if you keep in mind that each function does a specific thing, and each embedded function can be replaced by a variable or value of the desired format, complex formulas can be parsed into their pieces for diagnostic debugging.

This is the end of the crash course.

I hope that helps.