The NS_CONCAT function is typically used for Formula fields in the Results tab of a Saved Search to show a list of values in only a single row. Refer to this article I wrote that discusses more about this.
However, did you know that NS_CONCAT can also be used in the Criteria tab > Summary subtab of the Search?
Note: If you use this in a Formula field in the Criteria tab > Standard subtab, you will get an error since NS_CONCAT requires a summary type/grouping results.
The main use case of using NS_CONCAT in the Criteria Summary is to check whether a certain value shows up in a list or not.
For example: the requirement is to create an Item Search that shows a list of Items that do not have any Work Order transactions in the Related Records tab.
The first thing that might come to mind with this requirement is to add "Transaction: Type is not Work Order" as a Criteria. However, if you do so, the search will only filter out the Work Order transactions related to the item. If there are other transactions for the item, it will still show in the list.
This is where NS_CONCAT in the Criteria Summary comes in.
The formula we can use is:
NS_CONCAT(CASE WHEN {transaction.type} IN ('Work Order') THEN 1 ELSE 0 END)
Now, let's analyze how this formula works for this requirement:
The CASE statement identifies if the transaction type is a Work Order or not
- It results to 1 if the transaction type is Work Order
- It results to 0 if the transaction type is not a Work Order
The NS_CONCAT encloses the CASE statement
- This means that the result of the CASE statement will be treated as a text value list that is separated by commas
- Sample result: 0,0,0,0,0,0,1,0,0,0,0
The Description set is: "does not contain 1"
- This will look at the text value list, and if there is a 1 in the string, then it will be excluded from the Search Result
Note: There should be at least 1 field/column in the Results tab that has the Summary Type set to Group.
Here are just some of the threads in our community where this technique was proven useful:
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.
-----
If you find this information useful, let us know by reacting or commenting on this post!