Multi-Language Solution for Multi Value field in CRM Analytics/Tableau CRM/Einstein Analytics
Known Fact: -
CRM Analytics supports localization of text at the Menu level, translations pertaining to data need to be handled by the user.
Problem Statement: -
If the Projected Data in the chart or KPI is a single value, then we could directly perform the look-up join to get the equivalent Value of the other language.
But what can we do if the Projected Data is multivalue or multi-picklist?
In this blog, I am focusing on how we can handle multi-language solutions for Multivalue fields.
Solution Approach
we will use custom metadata objects in the Salesforce Org and then pull them through Data Recipe while populating the dataset.
Step by Step Approach
Step 1: Create a Custom Metadata Type in Salesforce for Each Dashboard
We need to create Custom metadata once again to hold the categorical data. Essentially, we create one metadata object for all categorical fields.
Also, note that each metadata object holds as many fields as the languages you are trying to support in your dashboard.
From the home page of your organization, go to the setup section by clicking on the gear icon in the top left corner and choosing Setup from the menu. From the left-side menu, go to Custom Code → Custom Metadata Types.
Once you click save you will be redirected to the Metadata type page. where you can declare new custom fields.
Create three text fields with the names English Values, Japanese Values, and Related Field Name to support two languages.
Note: “Related Field Name” helps minimize the number of Custom metadata Object creations. example: If we want to translate the Opportunity Stage and Forecast Category data instead of creating the two custom metadata objects, we can create another extra field to reference what field it’s related to. so that while pulling them from the data recipe, we can filter out the respective field value augmentation.
Step 2: Load the Custom Meta Data Type Object in the Data Recipe
We will essentially pull the data pertaining to categorical fields from the Custom Metadata Type Object input node by clicking “Add Input Data at the top left”.
Consideration :
Since we cannot use the Multivalue against a Single value as a joining key in the recipe, So first we have to split the MultiValue into a single value and then augment the mainstream. We have two ways to accomplish the Multivalue split; those are as below:
- Split the Multivalue by using standard “Split” functions. Then augment each split value from the Right side to get the equivalent value. The downside of this approach is that when you have high values present in a single record, we will end up with many joins, which will be time-consuming as well as impact the performance.
Example: When your maximum number of multi values in a single record is ’10, then you will have to Augment 10 times to the mainstream to get the Equivalent Translated values.
2. Let’s create a Picklist Values Array on the Right side (Metadata Type Object) and then augment to the mainstream. After having the Picklist Array on the mainstream, let’s compare the index position of each multivalve in a record present in the Picklist Value Array or not. If present, then we will get those values in a separate column. The same step has to be repeated for the maximum number of picklist values present in the Records. After the split, we have to form the Array of split values.
So, in this blog, we will go with the second approach because it will be the most efficient one.
Step 3: Create an Array of the Targeted Data field (Custom Metadata Object).
A custom meta-data type object has all the available Picklist values in a particular field. In our case, it is the ‘Product Field’. Let’s create an array of product fields by using the collect_list window function for all the columns of translated values.
Example: In this demonstration, I considered the English and Japanese translations. So, I have created the Array of Products column for both fields.
After the Creation of the Product Array field, we have to flag the last records within the subgroup, where we will have all the values within the subgroup, then filter out the Last Record flag to keep the right side ready for mainstream augmentation.
I have enclosed a step-by-step walkthrough video for clarification.
for more details about Collect_list functions then refer to the below previous article.
Step 4: Augment the Product Array field to the Mainstream.
Now we have Product Array fields in the right stream and have to augment them to the mainstream, but the custom Metadata Type object doesn’t have any matching keys, so either we need to perform the cross-join or create a new column on the left side that mainstreams the values present on the right-hand side.
When to follow which approach….?
- If the Cross limitations are in line with your data, then you can go ahead with the Cross join operation to augment the Product Array field to the mainstream. Otherwise, go with the Custom join key creation at the mainstream and use that field as a joining key for the augmentation.
Since we all know about the cross-join approach. I used the second approach for the demonstration. Go through the video below for the step-by-step procedure.
Step 5: Get the maximum Picklist value in a record.
Now we have Product Array fields in our mainstream. The next step will be to split out the Multivalue “Product” field. Because of that, we wanted to know what the maximum product values were in the records. Only then will we be able to create that many fields to capture the product values in separate columns.
Step 6: Split the Multi-value into Individual columns.
As we know how many fields we need to create for splitting the multivalue field, Now we are going to Split the Multipicklist Value by Looping through each index of the Multipicklist (product) field into the index of the master product Array to check whether the Multipicklist (“Product”) field value is present in the Master product Array field or not. If it is present, then we are taking that index value of the master product Array into a separate column.
Example:
The maximum product value present in the records was “4”. So, we have to create four separate values to hold each individual product value. First, we have to check if the 1st index of the Multipicklist (product) field matches any index of the Master Product Array or not. If we get matches, then we are taking that particular index of values in the master product Array. then the 2nd index of the Multipicklist (product) field comparison, and we have to follow the same step until the 4th index of the Multipicklist (product).
case
when element_at(Product__c,1) = element_at("Product.English_Values_Product_List",1)
then element_at("Product.English_Values_Product_List",1)
when element_at(Product__c,1) = element_at("Product.English_Values_Product_List",2)
then element_at("Product.English_Values_Product_List",2)
when element_at(Product__c,1) = element_at("Product.English_Values_Product_List",3)
then element_at("Product.English_Values_Product_List",3)
when element_at(Product__c,1) = element_at("Product.English_Values_Product_List",4)
then element_at("Product.English_Values_Product_List",4)
end
Go through the below video for the Multivalue split.
Finally, we have split out the English value of the product, but how can we get the split equivalent translated values?
So, we can follow the above approach in a different way to get the equivalent Translated values.
Now we will loop through Each Splitted field into Each index of the Master product Array to check if the Splitted field value is present in the Master product Array field or not. If it is present, then we are taking that index value position of the master product Japanese Array into a separate column.
Example:
So, now we have four Product values columns. Now we need to get the Equivalent Translated value for those four fields. We have to check if the first split field value matches any index of the Master product Array (English Values) or not. If we get matches, then we are taking those particular index values of the Master Product Array (Japanese). then the second split field comparison, and we have to follow the same step for all the split Columns.
case
when Product1 = element_at("Product.English_Values_Product_List",1)
then element_at("Product.Japanese_Values_Product_List",1)
when Product1 = element_at("Product.English_Values_Product_List",2)
then element_at("Product.Japanese_Values_Product_List",2)
when Product1 = element_at("Product.English_Values_Product_List",3)
then element_at("Product.Japanese_Values_Product_List",3)
when Product1 = element_at("Product.English_Values_Product_List",4)
then element_at("Product.Japanese_Values_Product_List",4)
end
go through the below video for the Equivalent Japanese Value split.
Step 7: Formatting the Final Product Array
At the moment, we have field values and equivalent Translated Values. Now we have to format the individual split Product column into an array to keep the source record Data format. But here the challenge is that we have created four new fields based on the Maximum picklist values present in our records. but all of the columns will not be filled all the time in that case when we do the Array formation. Null will also be present, which will create a duplication count in the data that is not correct. So, we have found a way to form the Array without null Accumulation.
What we can do is pass the not-null method to the Nested case statement, and if it’s not null, then we will take that column into the array, or else we will leave that column.
English Value Array Formation
case
when Product1 is not null
then
case
when Product_2 is not null
then
case
when Product_3 is not null
then
case
when Product_4 is not null
then array(Product1,Product_2,Product_3,Product_4)
else array(Product1,Product_2,Product_3)
end
else array(Product1,Product_2)
end
else array(Product1)
end
else array ('')
endJapanese Value Array Formation
case
when Product_1_ja is not null
then
case
when Product_2_ja is not null
then
case
when product_3_ja is not null
then
case
when Product_4_ja is not null
then array(Product_1_ja,Product_2_ja,product_3_ja,Product_4_ja)
else array(Product_1_ja,Product_2_ja,product_3_ja)
end
else array(Product_1_ja,Product_2_ja)
end
else array(Product_1_ja)
end
else array ('')
endFinal Results
Conclusion
Finally, we have a field to translate. Let’s follow the same Approach in the SOQL step for pulling the User LocaleLanguageKey and Result bindings to dynamically render translations.
Happy Learning !!!!!
Follow me LinkedIn linkedin.com/in/ramdoss-k-5157566a
