Welcome back to SuiteTip, your essential guide to mastering NetSuite's capabilities! In this article, we'll explore how to use NS_CONCAT to consolidate multiple list items into a single row, making your reports more concise and efficient.
What is NS_CONCAT?
In NetSuite Saved Searches, NS_CONCAT is a function used to display a list of values in a single row, typically within a Formula (Text) field, by concatenating them into a comma-separated string. This is similar with the WM_CONCAT function in Oracle.
In other words, NS_CONCAT is designed to roll up detailed information into a single, aggregated string, making it easier to view multiple values in a single row within a NetSuite Saved Search.
Disclaimer: The NS_CONCAT function is not officially supported by NetSuite. Furthermore, result values for formula fields containing the NS_CONCAT function are limited to 4000 characters. Use this function at your own discretion.
How to use NS_CONCAT?
- Add it in a Formula (Text) field within the Results tab of a Saved Search
- The Summary Type should be set to either "Minimum" or "Maximum" for the formula field to work correctly
- It can also be used in the Criteria tab > Summary subtab of the Saved Search
Syntax
The syntax of this formula is: NS_CONCAT({fieldid}) wherein the {fieldid} is the actual Field ID of the field you want to show as only 1 row in the Results.
Sample Scenario:
Most of your Sales Orders contain multiple items. You need a Saved Search that will show only 1 row per Sales Order, but all the Items in that particular order should also be displayed.
Note that normally, in a Transaction Saved Search, when you add the "Item" column in the Results tab, the item line details will show as separate rows.
In this case, we can use the NS_CONCAT function to summarize the item list per order. Let's add the formula in a Formula (Text) field:
For this to work, the lines must be grouped, so make sure to group the lines by Internal ID or Document Number (or any other field that you need them to be grouped)
By default, the delimiter is a comma with no space. But if you want to change that, then you can use the REPLACE function to replace all the commas with whatever delimiter you need.
- REPLACE(NS_CONCAT({item}),',',' & ')
However, note that if your item names contain commas, those will also be replaced if you use this formula.
Pro Tip: Multi-Line in a Single Row
For a more organized presentation, especially with lengthy lists, consider combining the <br> HTML tag within the formula. This will cause each list item to appear on a new line within the same row, enhancing readability.
Continuing with the example above, here's the Formula you can use:
- REPLACE(NS_CONCAT({item}),',','<br>')
Note: When using HTML tags in formula, you need to utilize a Formula (HTML) field instead of a Formula (Text) one.
Note: If you export this as a CSV or XLS file, the <br> code will be exported.
NS_CONCAT is a valuable tool for data presentation, especially when dealing with long lists. It simplifies complex data, making it ideal for creating reports and searches that are user-friendly and efficient.
---
Stay tuned for our next SuiteTip, where we’ll explore more tips and best practices for navigating NetSuite Reports and Saved Searches!
We encourage you to share your own tips, ask questions, and engage with fellow community members. Your experiences can help others on their journey to becoming NetSuite analytics experts!