Help with Formula

We have a formula on a field in the Meetings module 

'required_formula' => 'and(isAfter($date_start,date("2024-03-05")),equal($status,"Cancelled"))',

Please note that formula says date('2024-03-05')

The issue is that this causes an error 

attempt to convert invalid value to date: 2024-03-05

This error occurs in include/Expressions/Expression/Date/DateExpression.php. Our users are in the US and we do not format dates as YYYY-MM-DD. As Sugar allows users to configure dates to their preference, how can we avoid this error message?

The OOTB Sugar Code

public static function parse($date)
    {
        if ($date instanceof DateTime) {
            return $date;
        }

        if (empty($date)) {
            return false;
        }

        //String dates must be in User format.
        if (is_string($date)) {
            $timedate = TimeDate::getInstance();
            if (static::hastime($date)) {
                // have time
                $resdate = $timedate->fromUser($date);
            } else {
                // just date, no time
                $resdate = $timedate->fromUserDate($date);
            }
            if (!$resdate) {
                throw new Exception("attempt to convert invalid value to date: $date");
            }
            return $resdate;
        }
        throw new Exception('attempt to convert invalid non-string value to date');
    }

  • Hi Jeff,

    Have you tried using the expected internal format ("YYYY-MM-DD HH:MM:SS") in the formula?

    I tested the formula using the following format and it worked as expected:
    and(isAfter($date_start,date("2024‑03‑05 00:00:00")),equal($status,"Canceled"))

    cheers, Yvonne

  • Yvonne's solution may well work for you but I have always had trouble with dates in formulae for the exact reason you give.

    My method to avoid issues is to not use dates at all. In your case I would create a new Checkbox field on Meetings and default it to true. Then each new record has that set to true. When the field is created though all records will likely be true so you would need to Mass Update all old records with a $date_start prior to your specified date and make them False. That way you can simply make your formula:

    'required_formula' => 'and($is_valid_date,equal($status,"Cancelled"))',

    This is not really great development practice but may suffice seeing as we are tied to using the tools provided ;)

    Of course, if your date is going to fluctuate then you need some way of updating the Checkbox to reflect that so in that instance this solution is less useful unless you can create a process or job to do that work.

    Thanks,

    JH.