Saved Search – NS_CONCAT

I had a requirement to bring in a list of Invoices, comma separated, into a saved search for the purposes of a Map/Reduce script.
The search was originally designed for the situation where an entity was both a debtor and a creditor (via the “Other Relationships” field) – hence why it is in a single search

Netsuite does make use of the Oracle “CONCAT” function as standard in searches, however there is a hidden function called “NS_CONCAT” which will comma-separate values in a search for you.

Using the example above, invoices are grouped by client and summed using the standard amount field in a case statement.

Then, two formula fields, using NS_CONCAT, bring the internal ID’s of the invoice making up the respective balances

More information can be found on the (always helpful) Prolecto site – https://blog.prolecto.com/2014/05/16/reveal-both-aggregate-and-detail-in-netsuite-saved-search/