How to Format Numbers and Currency in NetSuite Custom Fields

To format numbers and currency in NetSuite, the SQL function TO_CHAR can be utilized. This function allows for precise control over the display of numerical values.

TO_CHAR({field_name},'$B99,999,999')

The string '$B99,999,999' represents the formatting specification for the number. Detailed information on formatting variables is available in Oracle SQL documentation. The TO_CHAR function can also be used to format dates.Important Considerations:Oracle pads the string on the left with spaces equal to the number of digits specified in the format string. Use LTRIM to remove unwanted leading spaces.If a number contains more digits than specified in the format formula, Oracle defaults to displaying # signs.Ensure that the NetSuite field type for the custom field is set to Text when using TO_CHAR for display purposes.The TO_NUMBER function also works and returns an Oracle number data type, which can be useful for calculations.For scenarios requiring number formatting that includes a currency symbol, especially when preparing documents, the following CASE statement provides an example. While Advanced PDF/HTML Templates now provide robust formatting options for custom fields and multi-currency on printed documents, this method offers explicit control within saved searches or custom fields.

CASEWHEN {currency}  'Euro' THENto_char({amountremaining},'LB999,999,990.99','NLS_CURRENCY  ''€''')WHEN {currency}  'US Dollar' THENto_char({amountremaining},'LB999,999,990.99','NLS_CURRENCY  ''$''')WHEN {currency}  'Canadian Dollar' THENto_char({amountremaining},'LB999,999,990.99','NLS_CURRENCY  ''CAD''')WHEN {currency}  'British pound' THENto_char({amountremaining},'LB999,999,990.99','NLS_CURRENCY  ''£''')END