The Condition Visual Builder in workflows only allows comparisons to a Static Date or Date/Time value as shown in the screenshot below.
There are use cases that would require comparison to a Dynamic Date or Date/Time value such as the Date Today, the Day a Month Ago, Last Week at 5:00 PM, and so on. In these scenarios, Custom Formula should be used.
Writing SuiteFlow Condition Custom Formulas would differ in whether a Server or Client Trigger is used and whether Date or Date/Time fields are involved. Approaches for each of these setups are detailed in the sections below.
Server Triggers
NetSuite field references (e.g. {field}), and Oracle SQL or PL/SQL syntax are the only acceptable custom formula formats for actions/transitions that runs under this trigger type.
The {today} field reference returns the current date and time at the timezone set in the User Preference (Home > Set Preferences) by the Current User.
See the screenshot below for an example:
ℹ️ In Oracle/PLSQL syntax, conditions are written in full as CASE WHEN (<expression>) THEN <result> END. In SuiteFlow Condition Custom Formulas, only <expression> is required to be specified. So in the above example, instead of writing CASE WHEN ({trandate} < {today}) THEN 1 END , only {trandate} < {today} has been specified.
Date Fields:
Values of Date fields will have the time component initialized to 00:00 (12:00AM) regardless of the timezone set in the User Preference by the current user. So, when comparing the equality of date fields with the {today} field reference, you should initialize it as well using the TO_DATE() Function. See the sample screenshot below:
Date/Time Fields:
The {today} field reference can be compared directly to a Date/Time field since its timezone will be always the same as the User Preference. See the sample screenshot below:
Client Triggers
SuiteScript 1.0 and JavaScript syntax are the only acceptable custom formula formats for actions/transitions that runs under this trigger type.
The {today} field reference is not available for actions/transitions running under this trigger type. So, to obtain the current date and time in this case, the JavaScript Date Object is used instead in which the expression is new Date().
Take note however that the JavaScript date object follows the local timezone of the system where the browser/application accessing NetSuite is running which might be different from the User Preference and could cause issues.
ℹ️ In JavaScript syntax, conditions are written in full as if (<expression>) { <result> }. In SuiteFlow Condition Custom Formulas, only <expression> is required to be specified. So, instead of writing if ( nlapiGetFieldValue(‘trandate’).valueOf < new Date(nlapiGetFieldValue('createddate').valueOf()).setHours(0,0,0,0)) { 1 }, only nlapiGetFieldValue(‘trandate’).valueOf < new Date(nlapiGetFieldValue('createddate').valueOf()).setHours(0,0,0,0) should be specified.
ℹ️ To insert formula snippets that enable easier building of Client Trigger Condition Custom Formulas, see SuiteFlow (Workflow Manager) Tip: Easily Build Workflow Formulas for Client-side Actions and Cond.
ℹ️ Another important detail to remember is when JavaScript date objects are converted to integer values with thevalueOf()method, they are equivalent to the number of milliseconds elapsed since January 1, 1970 00:00:00 UTC timezone until the date/time value. This is especially useful in Date Arithmetic (e.g. To add 7 days to a date object, convert it first to milliseconds (7*1000*60*60*24) then add the result to the date object).
ℹ️ The list of SuiteScript 1.0 Date APIs that can be used in workflow condition formulas is documented in SuiteAnswers Article 10250.
Date Fields:
Although NetSuite date fields have their time components initialized to 00:00, there is a tendency for a comparison to a wrong date if the current date and time returned by the JavaScript date object has a different timezone. So, in any case, it is recommended to convert the timezone of the returned value by the JavaScript date object to the User Preference timezone using the toLocaleString() method. Also, don't forget to remove the time component using the setHours(0,0,0,0)method. See the sample screenshot below:
Date/Time Fields:
Building condition formulas for Date/Time fields is almost similar to Date fields. The only difference is you do not need to remove the time component. See the sample screenshot below:
Have you come across scenarios that the above solutions would apply? Let us know what you think about this topic by leaving a comment or using the reaction buttons below.
-Jack
