1

I'm trying to retrieve field values from Sales Cloud, where the field is a picklist.

I'm can retrieve other fields, but not the picklist.

The picklist in Sales Cloud looks like this:

picklist

The API name of the field containing the picklist is Interests__c.

Here's my AMPscript code. How I can set the @hobbies variable to display the selected Interests__c values?

<script runat=server language=ampscript>

var @Id, @retrievePersonAccountRecord, @limitRow, @mobileNumber, @dateofBirth, @birthDay, @birthMonth, @birthYear, @hobbies, @hobbiesCount

set @Id = RequestParameter('id')

set @retrievePersonAccountRecord = RetrieveSalesforceObjects('Account','PersonMobilePhone, Date_of_Birth__c, Interests__c','Id', '=', @Id)
set @limitRow = Row(@retrievePersonAccountRecord, 1)
set @mobileNumber = Field(@limitRow, 'PersonMobilePhone')
set @dateofBirth = Field(@limitRow, 'Date_of_Birth__c')
set @birthDay = Substring(@dateofBirth,9,2)
set @birthMonth = Substring(@dateofBirth,6,2)
set @birthYear = Substring(@dateofBirth,0,4)
set @hobbies = ????

</script>

<div class="alert alert-info">
  <h3>debug section 2</h3>
  <p>
     @Id: <code>%%=v(@Id)=%%</code><br />
     @mobileNumber: <code>%%=v(@mobileNumber)=%%</code><br />>
     @birthDay: <code>%%=v(@birthDay)=%%</code><br />
     @birthMonth: <code>%%=v(@birthMonth)=%%</code><br />
     @birthYear: <code>%%=v(@birthYear)=%%</code><br />
     @hobbies: <code>%%=v(@hobbies)=%%</code><br />
  </p>
1

It appears that the RetrieveSalesforceObjects() AMPscript function works with most Salesforce field types, except multi-picklist values.

However I've come up with a solution that uses uses a combination of AMPscript, SSJS and GTL.

Firstly, you will need to create a Connected App in Sales Cloud. Once you've done this, you will use the client_id and client_secret from the app along with your Sales Cloud username and password to obtain an access_token and the instance_url.

I then use these credentials to make a get request to the required Salesforce Object and retrieve the specific field(s).

Finally, I set the response payload from my Salesforce REST API request to an AMPscript variable using SSJS and parse my response payload (a JSON object) on the page using Guide Template Language to extract the specific name/value pair that I want to use.

Note that the payload response includes all picklist field values for the record delimited by semicolons. For example:

{
  "attributes": {
    "type": "Account",
    "url": "/services/data/v20.0/sobjects/Account/0019000000nI8zTAAS"
  },
  "Interests__c": "Art;Cycling;Architecture",
  "DA_Person_Relationship__c": "DA Member;Donor;Event;Cloth Donor;Staff",
  "Id": "0019000000nI8zTAAS"
}

You can use the UpdateSingleSalesforceObject AMPscript function to update the object using the same syntax, for example:

UpdateSingleSalesforceObject("Account",@Id,"Interests__c","Shooting;Hunting;Fishing")

Obviously the picklist values have to be available in the field in Sales Cloud, but this works.

Refer to complete code below. This is used on a CloudPage, but the same should work in email.

<script runat=server language=ampscript>
    var @Id, @responsePayload
    set @Id = RequestParameter('id')
</script>

<script runat="server">
Platform.Load("Core","1");

var varId = Variable.GetValue("@Id"); // salesforce Id 
var boundaryMarker = 'WebKitFormBoundaryRZR1LUENOp85XKAd' // assign boundary marker
var boundary = '------' + boundaryMarker; 

     function formData(obj) {
      var result = '';
      for (var i in obj) {
        result += boundary + '\n' +  'Content-Disposition: form-data; name="' + i + '"\n\n' + obj[i] + '\n'
      }
      return result + '\n' + boundary + '--';
    }

    var oauthLoginCredentials = {
      username: 'insertSalesforceUsernameHere', // insert your salesforce username here
      grant_type: 'password', // leave this as is
      client_id: 'insertConnectedAppClientId', // insert the connected app client id here
      client_secret: 'insertConnectedAppClientSecret', // insert the connected app client secret here
      password: 'insertSalesforcePasswordnameHere' // insert your salesforce password here
    }; 
    var body = formData(oauthLoginCredentials);
    var url = 'https://login.salesforce.com/services/oauth2/token';
    var response = HTTP.Post(url, 'multipart/form-data; boundary=----'+ boundaryMarker, body, [], []);
    var obj = eval('[' +  response.Response + ']');

    var access_token =  'Bearer ' + obj[0].access_token

    // update 'Account' with Object you are retrieving
    // update 'fields' parameter with picklist fields to retrieve
    var getUrl = obj[0].instance_url + '/services/data/v20.0/sobjects/Account/' + varId + '?fields=Interests__c,DA_Person_Relationship__c';
    var headerNames = ['Authorization'];
    var headerValues = [access_token];
    var getResponse = HTTP.Get(getUrl, headerNames, headerValues);

    Variable.SetValue("responsePayload",getResponse.Content);

</script>

<!-- the following line is required to reset delimiters in OMM when GTL is used in CloudPages -->
  %%{={{ }}=}%%

  {{.datasource salesforceAccountObject type=variable}}
  {{.data}}
  { "target" : "@responsePayload" }
  {{/data}}
  <p>Hobbies are: {{Interests__c}}
  </p>
  {{/datasource}}

This page will produce the output:

Hobbies are: Art;Cycling;Architecture

Obviously you can use this in conjunction with AMPscript to transform the string further and display it as required.

Enjoy!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.