In my PROJECT TASKS module, I need the FINISH DATE to be automatically populated by adding a custom field DURATION (Decimal field which is a whole number of days) to the START DATE. I use the date format “d/m/Y” in both the system settings and my personal (admin) settings.
I have 2 issues:
ISSUE #1
My Workflow has the formula:
Parameters:
START DATE {P0}
DURATION {P1}
{addDays(dmY;{P0};{P1})}
Yet I get the errors:
suitecrm.log
[FATAL] Calculated Field Exception: DateTime::__construct(): Failed to parse time string (5) at position 0 (5): Unexpected character
error.log
PHP Notice: Undefined index: SweeterCalc in /home2/investo5/public_html/crm/modules/AOW_Actions/FormulaCalculator.php on line 108
PHP Notice: Undefined offset: 2 in /home2/investo5/public_html/crm/modules/AOW_Actions/FormulaCalculator.php on line 438
ISSUE #2
I thought I’d try a simple Workflow formula to see if I could get something to work at all so I changed the formula to:
{now}
Nothing at all and no errors recorded.
I do notice that when click in the FINISH DATE field, it has a time there although it is just a DATE field. When I create a new DATE field, thew same thing happens yet the START DATE field doesn’t show a time.
The thing I noticed on the workflow calculated fields page is that all the datetime related functions appear to require format strings… which doesn’t make any sense if you want to go from datetime to datetime type. ie you want to take input datetime DateCreated and add 1 day to it and store the result in a DueDate datetime type field.
The only case a format string would make sense would be if you were parsing a datetime from a string (a dedicated function could provide this in the calculated fields module) or if you wanted the output of the datetime calculation to be a string for some reason. You probably don’t because that breaks internationalization where different users can have different date formats. Also breaks sorting and filtering by date ranges amongst other things.
Am I missing something?
My guess is that these datetime functions are expecting a string as input rather than a datetime type (yeah, doesn’t make sense to me either). This is why you have to specify a format string to the function…to tell the datetime function how to parse your STRING representation of a datetime.
You could try temporarily adding a new text field with a string representation of a datetime in the specified format and see if your workflow calculation works with that. My guess is that the input data is coming from a datetime type and is not being provided to the function in the format you think.
Pretty dumb if there isn’t a way to work easily to/from native datetime types and we have to use these formatting strings.
fixing this?
In order to not break existing APIs, a new set of datetime functions could be added that work from/to datetime types with no format strings required. A separate pair of functions could be provided for people that need/want to work to/from strings.
parseDatetime
input: type:string a string representation of a datetime
input: type:string a string representing the format of the provided datetime string
output: type:datetime for use in calculations or storage in DateTime type fields
formatDatetime
input: type:datetime
input: type:string representing desired output format
output: string representation of provided datetime in format requested
Another idea would be to make the format parameter optional or ignored if the provided datetime is a datetime rather than a string. However I would still want datetime as output and not string as output.