Thread Details
  • State Not Answered
  • Replies 2 replies
  • Subscribers 371 subscribers
  • Views 102 views
  • Users 0 members are here
Forum Actions
Related

Formula error, value must be numeric

Francesca Shiekh

I have a formula field in a custom module.

The formula is: abs(subtract(daysUntil($agreement_date_c),daysUntil($date_entered)))

Agreement date is a Date field, date_entered is a DateTime

Today, for the first time, I got an odd 500 error that I don't quite understand. 

There are two FATAL entries in the log: 

Fri Aug 22 20:21:38 2025 [3856067][1][FATAL] Exception evaluating expression in SetValueAction, abs(subtract(daysUntil($agreement_date_c),daysUntil($date_entered))) : value '' must be numeric

#0 /var/www/html/sugarcrm/include/SugarMath/SugarMath.php(122): SugarMath->testValue()

#1 /var/www/html/sugarcrm/include/SugarMath/SugarMath.php(62): SugarMath->setValue()

#2 /var/www/html/sugarcrm/include/SugarMath/SugarMath.php(88): SugarMath->__construct()

#3 /var/www/html/sugarcrm/include/Expressions/Expression/Numeric/SubtractExpression.php(30): SugarMath::init()

#4 /var/www/html/sugarcrm/include/Expressions/Expression/Numeric/AbsoluteValueExpression.php(25): SubtractExpression->evaluate()

#5 /var/www/html/sugarcrm/include/Expressions/Actions/SetValueAction.php(124): AbsoluteValueExpression->evaluate()

#6 /var/www/html/sugarcrm/include/Expressions/Dependency.php(209): SetValueAction->fire()

#7 /var/www/html/sugarcrm/include/Expressions/Dependency.php(184): Dependency->fireActions()

#8 /var/www/html/sugarcrm/data/SugarBean.php(2529): Dependency->fire()

#9 /var/www/html/sugarcrm/clients/base/api/ActionButtonApi.php(366): SugarBean->updateCalculatedFields()

#10 /var/www/html/sugarcrm/include/api/RestService.php(320): ActionButtonApi->evaluateExpression()

#11 /var/www/html/sugarcrm/api/rest.php(26): RestService->execute()

#12 {main}

Fri Aug 22 20:21:56 2025 [3856063][1][FATAL] An exception happened: (500: db_error) Database failure. Please refer to sugarcrm.log for details.

Which suggests that it tried to enter something other than an integer in the DB.
since neither the agreement_date_c nor the date_entered are empty, how could the formula return anything other than an integer?
in this particular example agreement_date_c = 2024-08-02 and date_entered =  2024-01-31 20:27:18

It's been a long week and there may be something obvious I'm not seeing...

Thanks,
FrancescaS

Parents
  • 0

    Hi Francesca,

    We have encountered similar issues linked to this. The problem, in your case, seems to be the fact that one of your dates is "empty". Sugar cannot interpret this as a "date" and thus daysUntil function fails.

    To make a formula that works, you first have to check that both dates are filled before using the field in daysUntil. A combination of ifElse and/or casting the values (though this is difficult for dates) is required.

    Hope this helps.

    Kind regards,

    Vlad

Reply
  • 0

    Hi Francesca,

    We have encountered similar issues linked to this. The problem, in your case, seems to be the fact that one of your dates is "empty". Sugar cannot interpret this as a "date" and thus daysUntil function fails.

    To make a formula that works, you first have to check that both dates are filled before using the field in daysUntil. A combination of ifElse and/or casting the values (though this is difficult for dates) is required.

    Hope this helps.

    Kind regards,

    Vlad

Children