Set NetSuite date format on saved searches

Anitha Abraham

Updated September 25, 2024 12:14

Consider a scenario where you exported records from NetSuite via a saved search flow, but the date fields were exported in the European format D/M/YYYY rather than your preferred format, DD-MMM-YYYY. You might have wondered if the issue was because the Celigo export had used the date format in the company preferences setting, which had the European format.

To clarify, an export uses the date format and time zone associated with the user

who authenticated with NetSuite when setting up the connection in Celigo, or
who manually created the tokens

This is how NetSuite handles everything for APIs, saved searches, SuiteScripts, and so on.

In this scenario, you could create a formula text field on your saved search instead of setting the date format field in your preferences. To set the data format according to your preference,

Go to the Results tab of a saved search.
Click the Set Formula icon in the Formula column for the Formula (Text) results field.
In the Formula text field, add your preferred formula.

For example,
- For date: Enter to_char({now},'YYYY-MM-DD'). Replace now with the date field you want to use.
- For datetime: Enter to_char({now},'YYYY-MM-DD HH24:MI:SS').
- For timezone conversion to GMT: Do the following in your saved search in NetSuite or with handlebars in the Celigo platform.
  - In NetSuite: In your saved search, enter to_char(new_time({now}, ‘CST’, ‘GMT’),‘YYYY-MM-DD HH24:MI:SS’), assuming your user preference is in CST (Central Standard Time) and you're converting to GMT (Greenwich Mean Time).
  - In the Celigo platform: In your export, convert the time zone using handlebar expressions. See integrator.io supported time zones. However, if anyone else on your team re-authenticates the NetSuite connection and they have different preferences, then it could negatively impact your data flow by switching to their preference.