Combine Many Rows into One: Mastering NetSuite's NS_CONCAT Function

NetSuite saved searches are powerful, but sometimes they return more rows than you need. What if you could combine related data from multiple rows into a single, consolidated row? Enter NS_CONCAT – NetSuite’s hidden gem for row consolidation.

The Challenge: Too Many Rows

Imagine you’re creating a sales order report that shows items purchased by each customer. Without consolidation, you might see:

This creates multiple rows when you might prefer to see consolidated results with all items listed together.

The Solution: NS_CONCAT

Now for the magic!

Start by adding some groups to collapse the data. Next, you’ll want to add a Formula (Text) field with a summary type of Minimum. Set the formula value to: NS_CONCAT(TO_CHAR({item.internalid}))

Keep in mind you can substitute the {item.internalid} field to any field you want. In our case, we needed to also use TO_CHAR to convert the integer to string.

RESULTS!

Notice there’s only 6 rows now! Each of the product ids were combined into a single row, separated by commas.

How to Use NS_CONCAT

Here’s the step-by-step process to implement row consolidation:

Step 1: Add Grouping

First, add grouping to your saved search for the fields you want to keep unique. In our example:

• Group by Date Created
• Group by Internal ID

Step 2: Create the Formula Field

Add a Formula (Text) field with these settings:

• Summary Type: Minimum (or Maximum - both should work)
• Formula: NS_CONCAT(TO_CHAR({item.internalid}))

Step 3: Handle Data Types

If you’re concatenating non-text fields (like internal IDs), convert them to strings first using TO_CHAR():

NS_CONCAT(TO_CHAR({item.internalid}))

Practical Examples

Example 1: Consolidate Item SKUs

Show all SKUs purchased in a single order:

NS_CONCAT({item.itemid})

Result: “SKU001, SKU002, SKU003”

Example 2: List Transaction Numbers

Combine multiple related transactions:

NS_CONCAT({appliedtotransaction.tranid})

Result: “INV1234, INV1235, INV1236”

Example 3: Aggregate Customer Contacts

Show all contacts for a customer:

NS_CONCAT({contact.entityid} || ' (' || {contact.email} || ')')

Result: “John Doe (john@example.com), Jane Smith (jane@example.com)“

Advanced Techniques

Custom Separators

While NS_CONCAT defaults to comma separation, you can create custom formatting:

REPLACE(NS_CONCAT({item}), ',', ' | ')

Result: “Widget A | Widget B | Widget C”

Conditional Concatenation

Combine NS_CONCAT with CASE statements:

NS_CONCAT(
  CASE 
    WHEN {quantity} > 10 THEN {item} || ' (Bulk)'
    ELSE {item}
  END
)

Limiting Length

For fields with character limits:

SUBSTR(NS_CONCAT({item}), 1, 300)

Real-World Use Cases

1. Order Summaries

Create concise order summaries showing all items in a single row, perfect for customer communications or quick reviews.

2. Audit Reports

Consolidate all changes made to a record, showing who made what changes in a single line.

3. Inventory Tracking

List all locations where an item is stored:

NS_CONCAT({location.name} || ': ' || TO_CHAR({quantityavailable}))

Result: “Warehouse A: 50, Warehouse B: 30, Store 1: 20”

4. Customer Activity

Show all activities for a customer in one row:

NS_CONCAT({activity.title} || ' (' || {activity.date} || ')')

Important Considerations

Performance Impact

• NS_CONCAT can slow down searches with large datasets
• Consider adding filters to limit the number of rows processed
• Test performance with your actual data volume

Character Limits

• NetSuite text fields have character limits
• Formula fields are typically limited to 4,000 characters
• Plan for truncation in high-volume scenarios

Sorting

• Items are concatenated in the order they appear in the search
• Add sorting to control the order within concatenated values

SuiteScript Usage

While primarily a saved search function, NS_CONCAT may also work in SuiteScript contexts, though this is undocumented and should be tested thoroughly.

Troubleshooting Common Issues

Issue: Formula returns empty

Solution: Ensure you’ve set a summary type (Minimum or Maximum)

Issue: Performance is slow

Solution: Add filters, reduce date ranges, or consider alternative approaches for large datasets

Best Practices

1. Document Your Searches: Since NS_CONCAT is undocumented, add clear descriptions to your saved searches (I wish! NetSuite, please add this capability!)
2. Test Thoroughly: Verify results across different data scenarios
3. Monitor Performance: Keep an eye on search execution time
4. Have a Backup Plan: Know how to achieve similar results without NS_CONCAT if needed

Conclusion

NS_CONCAT is a powerful tool for simplifying complex NetSuite reports. By consolidating multiple rows into one, you can create cleaner, more readable outputs that better serve your business needs. While it’s an undocumented feature, its utility in creating concise, informative reports makes it invaluable for NetSuite administrators and developers.

Remember to test thoroughly and monitor performance, especially with large datasets. With proper implementation, NS_CONCAT can transform your NetSuite reporting capabilities.

Need help optimizing your NetSuite saved searches? Contact Contra Systems for expert assistance in leveraging NetSuite’s hidden features to their fullest potential.

Related Services

Need help implementing these concepts?

NetSuite Administration

Expert tips and optimization

→

Managed Services

Let us handle it for you

→