Scenario:
A user is attempting to utilize the NS_CONCAT function within a CASE statement to retrieve and concatenate all Sales Orders associated per Project. The formula used is CASE WHEN {transaction.type} = 'Sales Order' THEN NS_CONCAT({transaction.number}) END and this results to an error when the search is ran.
The Issue:
The problem lies in the placement of the NS_CONCAT function within the CASE statement. This arrangement is syntactically incorrect and leads to the encountered error.
Solution:
To resolve this issue, the formula requires a slight adjustment. Instead of nesting NS_CONCAT within the CASE statement, the CASE statement should be nested within the NS_CONCAT function. Here's the corrected formula:
NS_CONCAT(CASE WHEN {transaction.type} = 'Sales Order' THEN {transaction.number} END)
In this revised formula, the CASE statement is used to conditionally select the transaction numbers based on the transaction type. By enclosing the CASE statement within NS_CONCAT, you can successfully concatenate the desired transaction numbers without triggering any errors.
-----
If you find this information useful, let us know by reacting or commenting on this post!