I think it would be valuable to be able to concatenate in the rules engine. My use case is below:
I would like to transform data from multiple lines to populate a single text or multi-picklist field to load to SFDC. For example, the user below has three lines of data, one for each type of training they completed. I would like to be able to turn that into one line of data for this user that has a field that lists the training they completed separated by commas or by semi-colon.
Turning this:
Into this:
Thank you!
Best answer by dan_ahrens
Hi Kate,
So we do have a text concatenation formula in bionic rules available today. How many unique training titles do you have potentially in use? The text concatenation formula has a max of 10 elements that can be combined.
As long as your list of trainings is less than 10, this can be done via bionic rules through a series of filters, formula fields, merges and transformations. You wouldn't need an MDA table until the very end to store the final output. References to tables in steps 2 and 3 refer to bionic rule steps (in memory).
So we do have a text concatenation formula in bionic rules available today. How many unique training titles do you have potentially in use? The text concatenation formula has a max of 10 elements that can be combined.
As long as your list of trainings is less than 10, this can be done via bionic rules through a series of filters, formula fields, merges and transformations. You wouldn't need an MDA table until the very end to store the final output. References to tables in steps 2 and 3 refer to bionic rule steps (in memory).
I do have 13 training titles (and probably more to come). Do you think I can just split them into two groups and repeat the process twice, combining the output from the first concatenation with the second?
Would getting the data into a single row (basically step 3 above) be sufficient or do you need to get to the final step 4 where ALL the trainings they've taken are represented in a single field?
If you need to get to step 4, then you could add additional transformation steps to knock out 10 concatenations at a time and keep adding to that list.
What will get hairy is for steps 2 and 3 you'll need one transform and one merge per training category. So if you had 13 different trainings today, this would total be a ~28 step bionic rule!
When you are using concat function I suppose right now you are configuring it as concat (c1,c2)
if you need them to be separated by a space or comma
you can change the inputs given to concat function like concat(c1,,,c2)
if you notice the second argument you have to type yourself. It can be space as well then it will look like contact (c1, ,c2)
You do not need to create temp tables for each type of training. This can be accomplished by using the Pivot task in the Bionic Rule as follows. For each training type, you just need to add a new picot condition. Below is how you can accomplish this:
Thanks! I was trying to do this but didn't realize I could press "tab" on my keyboard to move to the next field. Good to know for next time.
I not actually going to use the commas this time, however, because it takes up one of the ten allowed concat fields, and also because not every training type will have a value, which results in a lot of repetitive commas:
I used the original dataset as the left dataset in my first merge, because not all users would have a value for all training types.
I also used semi-colon in the actual original fields for each training type. For example, instead of saying "Core", I updated the subjects to include "Core;". This way, when I concatenate the text without commas, the semi-colon acts to separate the values. They can now be used in a lookup in SFDC.
Thank so much for all of your help Dan, Jitin, and Nitin!
If you ever had a profile with us, there's no need to create another one. Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select.
