Custom Field > Ability to capture the nth level subentity from a custom field with a Parent : Child format.

Scenario:

I. Create a custom field that is a List/Record of type= Customer.

            1. Navigate to Customization > Lists, Records, & Fields > Transaction Line Fields > New.

            2. Give it a name (e.g. Customer List)

            3. Type= List/Record

            4. List/Record= Customer.

            5. Store Value= T.

            6. In the Applies To tab, set Sale Item= T.

            7. Click Save.

 

Note: Take note of the field id (e.g. custcol_subcustomer)

           

II. Create a Sales Order Transaction and select a sub-customer from the custom line field (in Step I)

            1. Navigate to Transactions > Sales > Enter Sales Order.

            2. Fill out the mandatory field (in the Main body)

            3. Fill out the mandatory field (in the Line Level) and select a sub-customer from the custom line field (in Step I) (e.g. Customer : Sub-customer)

            4. Click Save.

           

Actual:

- The custom line field display a format of "Customer : Sub-customer".

 

 

In order to get just the Sub-customer from the "Customer : Sub-customer" format, the user needs to create another custom line field that uses a SQL formula. Below are the steps:

            1. Navigate to Customization > Lists, Records, & Fields > Transaction Line Fields > New.

            2. Give it a name (e.g. Sub-Customer Name)

            3. In Type= Free-Form Text.

            4. Store Value= F.

            5. In the Applies To tab, set Sale Item= T.

            6. In the Validation & Defaulting tab, key in the SQL formula below in the Default Value field and substitute the value of xxx to field id: custcol_subcustomer.

           

            CASE NVL2(REGEXP_REPLACE(REGEXP_SUBSTR({xxx},': [^:]+*$'),': ',''),1,2) WHEN 2 THEN {xxx} ELSE REGEXP_REPLACE(REGEXP_SUBSTR({xxx},': [^:]+*$'),': ','') END

           

 

Sample SQL Formula based from the scenario above.

 

            CASE NVL2(REGEXP_REPLACE(REGEXP_SUBSTR({custcol_subcustomer},': [^:]+*$'),': ',''),1,2) WHEN 2 THEN {custcol_subcustomer} ELSE REGEXP_REPLACE(REGEXP_SUBSTR({custcol_subcustomer},': [^:]+*$'),': ','') END

 

Note: If the custom line field "Customer List" has a format of Parent : Child : Grandchild, the SQL formula above would still be able to capture just the Grandchild.