Calculating and Setting Due Date in Workflow Formula

This article explains how to calculate and set a due date in a NetSuite workflow using a formula that incorporates an interval and a date field. The formula uses NetSuite’s SuiteScript API functions nlapiDateToString and nlapiAddDays to compute the due date dynamically.

Overview

In NetSuite workflows, you can use formulas to dynamically set field values based on specific conditions or inputs. To calculate a due date by adding a specified interval (in days) to a given date, you can use the following formula:

nlapiDateToString(nlapiAddDays(new Date(nlapiGetFieldValue('date')), parseInt({interval} || '0')))

This formula retrieves a date from a field, adds a specified number of days (interval), and formats the result as a string to set the due date.

Formula Breakdown

1. nlapiGetFieldValue(‘date’): Retrieves the value of the field with the internal ID date. Replace ‘date’ with the actual internal ID of your date field.
2. new Date(…): Converts the retrieved date string into a JavaScript Date object for manipulation.
3. parseInt({interval} || ‘0’): Retrieves the value of the interval parameter (a custom field or parameter in the workflow) and converts it to an integer. If no value is provided, it defaults to 0 to avoid errors.
4. nlapiAddDays(date, days): Adds the specified number of days (from the interval) to the provided date.
5. nlapiDateToString(date): Converts the resulting date back into a string in the format expected by NetSuite (based on the user’s date format preferences).

Steps to Implement in a Workflow

1. Create or Edit a Workflow:

• Navigate to Customization > Workflow > Workflows in NetSuite.
• Create a new workflow or edit an existing one.

1. Add a Set Field Value Action:

• In the workflow, add a Set Field Value action to set the due date field.
• Select the target field (e.g., a custom field for the due date, such as custbody_due_date).

1. Use the Formula:

• In the Value section of the action, select Formula.
• Enter the formula:

nlapiDateToString(nlapiAddDays(new Date(nlapiGetFieldValue('date')), parseInt({interval} || '0')))

• Replace ‘date’ with the internal ID of the source date field (e.g., trandate for transaction date or a custom field like custbody_start_date).
• Replace {interval} with the internal ID of the field or parameter that stores the number of days to add (e.g., {custbody_interval}).

1. Save and Test:

• Save the workflow and test it on a record to ensure the due date is calculated correctly.
• Verify that the source date field and interval field contain valid values to avoid errors.

Example Scenario

Suppose you have a custom field custbody_start_date (internal ID: custbody_start_date) and another custom field custbody_interval_days (internal ID: custbody_interval_days) that stores the number of days to add. You want to set the custbody_due_date field.

In the workflow:

• Add a Set Field Value action.
• Set the field to custbody_due_date.
• Use the formula:

nlapiDateToString(nlapiAddDays(new Date(nlapiGetFieldValue('custbody_start_date')), parseInt({custbody_interval_days} || '0')))

When the workflow runs, if custbody_start_date is 10/01/2025 and custbody_interval_days is 5, the custbody_due_date will be set to 10/06/2025.

Best Practices

• Validate Input: Ensure the interval field contains a valid integer to avoid errors in parseInt.
• Field IDs: Always use the correct internal IDs for fields. You can find these by navigating to Customization > Lists, Records, & Fields and checking the field’s details.
• Default Value: The || ‘0’ ensures the formula doesn’t break if the interval is undefined or empty.
• Date Format: The nlapiDateToString function respects the user’s date format preferences in NetSuite, ensuring consistency.
• Testing: Test the workflow with various scenarios (e.g., missing interval, negative intervals, or invalid dates