IfElse returning null

Hi Richard Griffiths,

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

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.

Hi Richard,

I apologize for missing that last detail in your original post where you indicated you were looking to return a null or empty value. In my test, I created a custom datetime field with a default value of none to simulate your scenario. My formula was updated to:

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

Opportunities continued to save without issue and the field was empty as expected when the custom field was also empty. 12.1 is from ~6 months ago so there is still a potential for this being a since-resolved product defect. I recommend trying on your current cloud instance and/or an upgraded 12.3 sandbox if you haven't yet upgraded production. If you're still encountering problems there, I suggest opening a case with Sugar Support as there may be some difference with how the course_date field metadata was originally defined in comparison to how fields are created today in Studio / Module Builder.

Chris

A clever solution and it works perfectly. Thank you very much.