IfElse returning null

I have an ifElse function of this construct:

ifElse ( condition, return null, otherwise return today's date )

As things are currently my function returns itself. The field is a date called course_date:

ifElse ( condition, $course_date, today() )

and this returns 0 on condition true. This then causes an error as 0 is not a date and then fails when being submitted.

I would like to know if it is possible to return null or an empty string.

Parents
  • Hi ,

    I am surprised that this formula worked at all! Normally when doing a calculated value for a field where the field type is an explicit format (e.g. integer, date, etc.), the calculated formula validation ensures that the returned value will match that format. Historically, ifElse statements would not pass that validation even if you were careful enough to ensure all the returned values were of the expected format. This resulted in workarounds in doing a format casting on the actual formula you want to calculate such as:

    date(ifElse(condition, $course_date, today())

    It looks like that validation has been enhanced to verify the return values match the expected field format, so the leading date() formula is no longer necessary.

    I tried your same scenario on the Opportunities module in a custom date field with the following formula:

    ifElse(equal($sales_status,"In Progress"),$service_start_date,today())

    The custom field populated with the expected value in both the true and false outcomes. Since it's working for me, here are a few potential reasons it may not be working for you:

    • Date format of the system and/or user - I have seen in the past where differing formats can cause unexpected behavior. In my tests, the system format (Admin > Locale) was set to yyyy-mm-dd, and I tried the user profile settings of yyyy-mm-dd and mm/dd/yyyy. If you're using a different format for either, let me know and I can see if your issue is reproducible.
    • Sugar version - I tested in Sugar's latest cloud release (12.3). If you're on an earlier release, it's possible you are encountering a defect that has since been resolved.

    I hope this helps!

    Chris

Reply
  • Hi ,

    I am surprised that this formula worked at all! Normally when doing a calculated value for a field where the field type is an explicit format (e.g. integer, date, etc.), the calculated formula validation ensures that the returned value will match that format. Historically, ifElse statements would not pass that validation even if you were careful enough to ensure all the returned values were of the expected format. This resulted in workarounds in doing a format casting on the actual formula you want to calculate such as:

    date(ifElse(condition, $course_date, today())

    It looks like that validation has been enhanced to verify the return values match the expected field format, so the leading date() formula is no longer necessary.

    I tried your same scenario on the Opportunities module in a custom date field with the following formula:

    ifElse(equal($sales_status,"In Progress"),$service_start_date,today())

    The custom field populated with the expected value in both the true and false outcomes. Since it's working for me, here are a few potential reasons it may not be working for you:

    • Date format of the system and/or user - I have seen in the past where differing formats can cause unexpected behavior. In my tests, the system format (Admin > Locale) was set to yyyy-mm-dd, and I tried the user profile settings of yyyy-mm-dd and mm/dd/yyyy. If you're using a different format for either, let me know and I can see if your issue is reproducible.
    • Sugar version - I tested in Sugar's latest cloud release (12.3). If you're on an earlier release, it's possible you are encountering a defect that has since been resolved.

    I hope this helps!

    Chris

Children
  • Thanks for looking into this - much appreciated.

    I have tried all date formats but it makes no difference.

    However, perhaps I was not clear enough in my question. I would like the formula to return an empty date if the condition is true. The field $course_date is of a type Datetime and the default value is none, although changing the default value makes no difference. 

    Examples of what I have tried:

    ifElse ( condition, $course_date, today() ) returns 0 on true (regardless of default value) 

    ifElse ( condition, null, today() ) formula will not save - "null: Syntax Error, no open parentheses found"

    ifElse ( condition, null(), today() ) formula will not save - "null: No such function defined"

    ifElse ( condition, date("0000-00-00 00:00:00", today() ) returns 0 on true

    ifElse ( condition, date(""), today() ) returns 0 on true

    ifElse ( condition,"", today() ) formula will not save - "The formula must be of type date"

    The version is 12.1.0 and is a clone of a cloud version.