Details about this thread
  • State Verified Answer
  • Replies 11 replies
  • Subscribers 294 subscribers
  • Views 3134 views
  • Users 0 members are here
Related content from around SugarClub

Calculated Field functions

Brian Curran

Hi,

I'm looking for help on Calculated Field Functions, I've looked through the knowledge base but the examples there don't help very much. Previous experience of creating nested If and Case statements in Excel along with building a database in FileMaker.

I have two date fields "Start" and "End", which may or may not be blank. I want to build a function expression that mirrors the statements below:

If "Start" = blank/empty/null etc., display "Start date to be confirmed"

If "Start" > Today, display "Inactive"

If "Start" <= Today AND "End" = blank/empty/null etc., display "Active"

If "Start" <= Today AND "End" < Today, display "Ended"

Any help with the above would be really appreciated, many thanks...

Brian.

Parents
  • 0

    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",
              ""
            )
          ),
          ""
        )
      )
    )
Reply
  • 0

    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",
              ""
            )
          ),
          ""
        )
      )
    )
Children
  • 0 in reply to Patrick McQueen

    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.