I have an issue with a formula case statement in a NetSuite workflow, I am using the same case statement in a saved search and it works just fine, but generates an error in the workflow formula for sourcing date, to set the date field on a cashsale record created from a sales order.
NetSuite formula which basically states if it is 10:00 PM (22:00) or after increment by one day, otherwise use datecreated date. (this is used to calculate settlement date for banking reconciliation matching)
The workflow event is “Before Record Load” on “Create” a Cashsale record which is definitely a server-side event. I have also independently pulled out
to_date({createdfrom.datecreated}) + 1
and
{createdfrom.datecreated}
Which work independently as expected without errors, really not sure what I am doing wrong
CASE WHEN to_number(to_char({createdfrom.datecreated}, 'HH24')) >= 22 THEN to_date({createdfrom.datecreated}) + 1 ELSE {createdfrom.datecreated} END
Here is the case statement I used in my saved search which works perfectly
CASE WHEN to_number(to_char({datecreated}, 'HH24')) >= 22 THEN to_char(({datecreated} + 1), 'DD/MM/YYYY') ELSE to_char(({datecreated}), 'DD/MM/YYYY') END
Advertisement
Answer
Unfortunately Netsuite Workflow formula value is not reliable in terms of using the Netsuite formulas that run in saved search. Some work and some don’t. In this case the To_char and date formatting seems to be not recognized at all.
Solution is a custom field named “Created Hours” that is dynamically sourcing value from a saved search. The field is exposed in Sales Order and Cash Sale transaction form (You can hide it as well). I then use the field as my comparison to achieve the condition that will set the date value. I tested few old Sales orders converting to Cash Sales and works well
Hope this helps other people with the same or similar issues.