Scenario:
A user is working with a Project Saved Search, specifically focusing on extracting unique Sales Orders and their corresponding Total Amounts.
The initial approach involved using a formula to concatenate the Sales Order number and Amount, ensuring uniqueness by combining them into a single string.
- Initial Formula: REPLACE(NS_CONCAT(DISTINCT(CASE WHEN {transaction.type} = 'Sales Order' THEN ({transaction.number} || ':' || {transaction.totalamount}) END)),',','
<br>')
This resulted in a format like "SO01:1000<br>SO02:1000", where the Sales Order number and Amount are separated by a colon and different entries are separated by line breaks.
However, there came a new requirement which is to display the Sales Order number and Amount in separate columns, but still only show the results per Project in a single row using NS_CONCAT function.
Required Format
Project | Sales Order:Amount | Sales Order | Amount |
|---|
Project001 | SO01:1000 SO02:1000 | SO01 SO02 | 1000 1000 |
The Issue:
Using the formula: REPLACE(NS_CONCAT(DISTINCT(CASE WHEN {transaction.type} = 'Sales Order' THEN {transaction.number} END)),',','<br>') works well for the Sales Order column since the Sales Order numbers are unique.
The problem arises when the total amounts for the different Sales Orders might be the same, making it challenging to extract distinct amounts when they appear in multiple records.
In our example above, the amounts for both Sales Orders are both 1000. If we use the formula REPLACE(NS_CONCAT(DISTINCT(CASE WHEN {transaction.type} = 'Sales Order' THEN {transaction.totalamount} END)),',','<br>'), the result will only show 1 line for the amount of 1000 since the amount of both Sales Orders is the same.
Solution:
The solution for this involves refining the formula to meet the specific requirement.
- Revised Formula: REPLACE(REGEXP_REPLACE(NS_CONCAT(DISTINCT(CASE WHEN {transaction.type} = 'Sales Order' THEN ({transaction.number} || ':' || {transaction.totalamount} ) END)), '[^,]+\d+:', ''), ',', '<br>')
In this formula, the REGEXP_REPLACE function is used to replace the Sales Order number with blank/null which essentially removes it from the concatenated string, leaving only the amounts. This ensures that even if the amounts are the same across different sales orders, they will be displayed separately in the desired format.
-----
If you find this information useful, let us know by reacting or commenting on this post!