Replace subclasses on search results to show their parent class instead

Scenario:

Users need to replace subclasses on Saved Search Results to show their parent Class instead.

Search Results shows Class column this way parent class :  child class.

 

Example:  

1. Classes of a Transaction:

 

                First

                   ---    A

                   ---    B

                   ---    C

                Second

                   ---    A

                   ---    B

                   ---    C

 

2. Select Class column field on a Saved Transaction Search at Results tab > Columns subtab. Click Preview.

 

Actual Result: If a Transaction is set to class First : B, Transaction Search Results shows First : B.

Users want to show Class First only if the Transaction falls under class First : B.

 

Solution:

1. Navigate to Results tab of the Saved Search > Columns subtab. 

2. Add Formula (Text) column field. 

3. Enter this formula on the Formula field:  

case NVL2(REGEXP_REPLACE(REGEXP_SUBSTR({class},'.+ :'),' :', NULL),1,0) when 0 then {class} else REGEXP_REPLACE(REGEXP_SUBSTR({class},'.+ :'),' :', NULL) END

4. Click Save&Run.