Custom Multi-Select Picklists in Sales/Service Cloud allow users to select one or more values from a predefined list:
When a user picks more than one value, the selected values show in a field separated by a semicolons:
The values will be in the same, semicolon-delimited format, if you synchronize the picklist field into Marketing Cloud and store the data in a Data Extension. This means that you will have to break each of those fields up into separate values to be able to use them in your script.
My example multi-select picklist in Sales Cloud is used for storing subscriber preferences and has five possible values:
- Special Offers
In this tutorial, we will first pull the multi-select picklist data onto a CloudPage to display it for the subscriber as an HTML form, where they can pick which of the above they are interested in, and once they submit the form, we will write that data back into Sales Cloud.
Splitting a string by a delimiter
First, let’s pull the data into our script. In the example below, I will use the RetrieveSalesforceObjects() function to pull the data onto a CloudPage directly from Sales/Service Cloud. Depending on your use case, you might want to use the Lookup() or LookupRows() functions instead, if you are working with Synchronized Data Extensions.
My multi-select picklist field is called
Preferences__c and it’s on the Contact object:
When you output the data using
%%=v(@preferences__c)=%%, you will see it in exactly the same semicolon-delimited format as mentioned earlier:
In order to split multi-select picklist data into separate values and to be able to assign those values to AMPscript variables, we will use the BuildRowSetFromString() function. The
BuildRowSetFromString function will create a rowset from a character string by splitting the string at the specified delimiter – in our case, a semicolon.
As a first step, we will check if our
@preferences__c variable has any data inside by using the Empty() function. If the variable is not empty, we can proceed with the
Output(rowcount(@rs)) will output the number of values contained in our multi-select picklist field.
Now let’s add a loop to iterate through the rowset and match the values in our script with multi-select picklist values from Saleforce. Remember, that the more picklist values you have in Salesforce, the more complicated your loop will get.
The biggest challenge we need to tackle in our script, is the fact that we never know how many and which values each subscriber will have assigned to them. That’s why we need to iterate through all of the values and use a conditional statement to check if each of the values found matches a picklist value we know exists in Salesforce.
Here’s how the script will look like once we add our loop with the conditional statements:
Above script will display
true for all values where it found a match.
Let’s now add an HTML form and display our picklist data as checkboxes.
HTML checkboxes and AMPscript
We will use AMPscript and the values retrieved using the
BuildRowSetFromString function to conditionally mark checkboxes as checked if there is a matching value in Salesforce. In HTML forms, the
checked attribute specifies that an element should be pre-selected when the page loads (for type=”checkbox” or type=”radio”):
For each of the checkboxes representing the multi-select picklist values, we will add an AMPscript variable to conditionally add the
checked attribute when the value is found in our rowset. Let’s also add the basic subscriber information like First Name, Last Name and Email to the form:
Now we need to adjust or script to display the word
checked instead of
true in case there is a match:
Now you should be able to correctly display the multi-select picklist data for each subscriber visiting your CloudPage.
Update Multi-Select Picklist values in Salesforce using AMPscript
Once the subscriber makes changes to their preferences and clicks on the
Submit button, the page will reload and the form parameters will be posted back to the same page. If you would like to learn more about this approach of working with forms on CloudPages, take a look at one of my earlier articles: Create a Sales Cloud-integrated lead capture form using AMPscript.
Now we can add the script to process the posted form data and update it in Salesforce:
To pass the data back to Salesforce, we need to transform the posted form data back into the initial form: a string with semicolon-delimited values. In order to achieve this, I have used the Iif() function to conditionally check which values have been selected in the form (a selected checkbox will return the value of
on). If a parameter returns the
on value, we substitute it with the corresponding picklist value and concatenate all the returned values together into one string to pass them to Salesforce.
The full script
Here is the full script, including a try/catch statement for debugging:
To see this script in action, visit my CloudPage here.
Leave a comment below or email me at firstname.lastname@example.org.