How to convert String to Multi-Select Picklist using rules

  • 12 February 2024
  • 1 reply
  • 13 views

Userlevel 2
Badge +1

 

Aim - There are scenarios where you have string values over multiple rows that define an attribute about a company. It could be the products used by a company, any stage that they are simultaneously a part of, etc. Check below for Sample Data - 

 

Table 1:

 

Company ID (ID)

Company Name (String)

Products Used (String)

101

IBM

Benchmark Starter

101

IBM

Falcon Pro

104

Cisco

Benchmark Starter

101

IBM

Pro Rata Service

104

Cisco

Neu Rocket

 

The above table entails information regarding Products used by different Companies. 

 

Our Aim is to Convert the Above Tabular information which has the details of one particular company distributed over many entries to be aggregated into 1 entry by leveraging Multi-select picklist for ‘Products Used’. Check below for the Final data -









 

Table 2:

 

Company ID (ID)

Company Name (String)

Products Used (Dropdown)

101

IBM

  • Benchmark Starter
  • Falcon Pro
  • Pro Rata Service
  • Neu Rocket

104

Cisco

  • Benchmark Starter
  • Falcon Pro
  • Pro Rata Service
  • Neu Rocket


 

Pre-requisite - Create a dropdown with all the products offered by the Organization.

 

Procedure - Firstly, we would have to convert the multi-row company information into one ROW. This can be achieved by Pivot operation in Rules Engine (Bionic Only; Horizon Rules (BETA) had some issues although the same operation was implemented).

 

Action on Table 1 - Pivot.

 

Pivot ON - Products Used. 

Group BY - Company ID, Company Name


 

Products Used == Benchmark Starter

Count ‘Number of Records’

Default Value = 0

Column Name = Benchmark Starter

Products Used == Falcon Pro

Count ‘Number of Records’

Default Value = 0

Column Name = Falcon Pro

Products Used == Pro Rata Service

Count ‘Number of Records’

Default Value = 0

Column Name = Pro Rata Service

Products Used == Neu Rocket

Count ‘Number of Records’

Default Value = 0

Column Name = Neu Rocket


 

The Pivot would convert Table 1 into the table below:

 

Table 3:

 

Company ID

Company Name

Benchmark Starter

Falcon Pro

Pro Rata Service

Neu Rocket

101

IBM

1

1

1

0

104

Cisco

1

0

0

1


 

We have achieved Single ROW, company information on products used. The ‘1’ acts as Yes, whereas ‘0’ acts as NO.

 

On Table 3, we should perform a Transform Operation wherein we must create as many ‘Case Expressions’ as there are products offered. In our case, it is 4. The idea is to convert under the fields (Products) as below: 

 

Before Transformation: 

 

Benchmark Starter

Falcon Pro

1

0

0

1

 

Post Transformation:

 

Benchmark Starter

Falcon Pro

Benchmark Starter;

 
 

Falcon Pro;

 

We are using ; at the end because a Multi-select picklist separates two different fields with a semi-colon. 

 

NOTE - The custom value should be equal to the string in the dropdown defined in Gainsight dropdown.

 

Sample Case Expression: 

 

FF3n-rt27nXT4pgZH3C84kMvJNTv2IINMI_XG_3vlluW1EkEnuaRPadTbZRI7vYFYB_foIllpnmv3DE922E8GBQk8yDPPHQeOJL8OwVGu_RZwkn0YXUCchT2mEHqb9E8aJO5PEy2KNRor3LsKuGosX4

 

As previously mentioned, the no. of case expressions would be the same as no. of fields derived in the Pivot Action; with only Case 1 with the product name and semi-colon, and NULL in Default Case. 

 

Transform Operation on Table 3:

 

Fields - Company ID, Company Name, Benchmark Starter (CE), Falcon Pro (CE), Pro Rata Service (CE), Neu Rocket (CE)

 

Here, CE refers to Case Expression.

 

Post Transformation, the sample data would look like this:


 

Table 4:

 

Company ID

Company Name

Benchmark Starter

Falcon Pro

Pro Rata Service

Neu Rocket

101

IBM

Benchmark Starter;

Falcon Pro;

Pro Rata Service;

 

104

Cisco

Benchmark Starter;

   

Neu Rocket;


 

This brings us to our Final Operation, which will be ‘Transformation’. 

 

The aim of this transform operation is to merge all the products into one column, separated by a semi-colon; as a multi-select dropdown would do. This would be achieved through Concat Operation present in transformation.

 

Transformation on Table 4:

 

Fields - Company ID, Company Name, Products (Concat Operation)

 

Concat Operation:

Concat(Benchmark Starter, Falcon Pro, Pro Rata Service, Neu Rocket)

 

Note - The concat operation must be performed on all the fields that are derived in the Pivot operation. It is 4 in this case. 

 

Post Transformation, the dataset would look like this:

Table 5:

Company ID

Company Name

Products

101

IBM

Benchmark Starter;Falcon Pro;Pro Rata Service;

104

Cisco

Benchmark Starter;Neu Rocket;


 

The above dataset is analogous to how a Multi-select dropdown stores values in back-end. 

 

When the above table is used to update the Company Object with the appropriate mappings; We will achieve Table 2.


 

Company ID (ID)

Company Name (String)

Products Used (Dropdown)

101

IBM

  • Benchmark Starter
  • Falcon Pro
  • Pro Rata Service
  • Neu Rocket

104

Cisco

  • Benchmark Starter
  • Falcon Pro
  • Pro Rata Service
  • Neu Rocket

1 reply

Userlevel 4
Badge +5

This is great, thank you for sharing @rajasekhar_parasu!

Reply