Create Cases in Salesforce using CloudPages and AMPScript

Customer service is an important part of any business and Salesforce Service Cloud creates a nice hub for service reps to organize and respond to cases. Cases can be created a number of ways including manually, email-to-case, and web-to-case. Web-to-case allows admins to generate HTML that they can post on their website so that the end-users can fill out a form and automatically create their own cases. Web-to-case is great for basic customer service requests, but it has its limitations. For example, you can only generate HTML for fields that exist on the Case object, you cannot insert rich text into the description field and you cannot upload documents. In addition to the fields being limited to those on the Case object, you also cannot dynamically populate a dropdown picklist field if it changes. The HTML that is generated is representative of what exists at the time that it is generated.

If you have Marketing Cloud, however, you can create a nice looking form that resolves all of these pain points. You can then iframe that form onto your website. Yes, it has to be iframed, rather than pasted as HTML like the web-to-case form, because AMPScript has to run on a CloudPage both to process and to leverage Marketing Cloud Connect.

This solution assumes that you have Salesforce and have configured Marketing Cloud Connect. With Marketing Cloud Connect comes a set of AMPScript functions that allow you to communicate directly with your connected Salesforce org. The ones we will focus on for this solution are: CreateSalesforceObject, RetrieveSalesforceObjects, and UpdateSingleSalesforceObject.

CreateSalesforceObject creates a record on any Salesforce object; RetrieveSalesforceObjects retrieves specified fields from any Salesforce object; and UpdateSingleSalesforceObject updates a specified record on any Salesforce object. You can access and modify both standard and custom objects.

With Marketing Cloud Connect, there is also a stream of Salesforce data flowing back into Marketing Cloud in the form of synchronized data extensions, which are typically refreshed every 15 minutes. This will allow us to use the various lookup functions in AMPScript to speed up some processes.

Here is an example of a customer service form that was built using Bootstrap CSS:

This is what happens after the form is submitted:

Using Email Address as a look-up field, search for a Contact in Salesforce. If the Contact exists, then update fields related to the Contact record (First Name and Last Name in this case) and return the Contact Id. This can be done in one of two ways: a lookup to the Contact synchronized data extension in Marketing Cloud, or retrieval from the Contact object in Salesforce. There are pros and cons to each:

Arguments for lookups to data extensions:

A lookup to a data extension is faster than retrieving from Salesforce. Speeding up your form submission is good for user experience.
It’s a very rare error to come across, but if someone submits an email address with an apostrophe (yes, that is a thing), RetrieveSalesforceObjects will crash. This is because RetrieveSalesforceObjects parses the email address variable with single quotes and the apostrophe causes the email to break at the apostrophe. This is a known issue, and the resolution in this situation is to use a lookup to a data extension, which for some reason does not encounter the same problem.
Using LookupOrderedRows allows you to sort by CreatedDate so that you can access the most recently created record in case you are worried about duplicates. RetrieveSalesforceObjects does not have a sort function.

Argument for RetrieveSalesforceObjects:

Real-time accuracy. If someone was to submit a customer service request that resulted in a new Lead or Contact being created, and then they were to submit again just a few minutes later before the synchronized data extensions had updated, the form will try to create a duplicate Lead or Contact. By pulling directly from Salesforce, you are certain to always have the most accurate and up-to-date information.

With the Pros and Cons in mind, here are the two options for retrieving the ContactId if the email matches an existing Contact:

SET @retrieveContact = LookupRows('Contact_Salesforce','Email',@email)

SET @retrieveContact = RetrieveSalesforceObjects('Contact','Id','Email','=',@email)

Whichever option you choose, you are going to have to isolate the desired fields in the row into variables. In this case, we are going to just accept the first row that is returned as the result and the only variable we are grabbing is the Id. In many cases, only one row is going to be returned. However, if there are duplicates, more than one row will be returned. The best resolution to this is to dedupe your Salesforce org or use LookupOrderedRows with CreatedDate descending.

if RowCount(@retrieveContact) >= 1 then
   SET @contactRow = Row(@retrieveContact, 1)
   SET @contactId = Field(@contactRow, "Id") 
endif

Using the Salesforce Id that you just obtained, you can update the Contact with any relevant fields (in this case just first and last name) and create a new Case attached to this Contact:

UpdateSingleSalesforceObject(
          'Contact',@contactId,
          'FirstName',@firstName,
          'LastName',@lastName
          )

SET @caseId = CreateSalesforceObject(
          'Case',10,
          'ContactId',@contactId,
          'Subject',@subject,
          'Description',@description,
          'Product__c',@productID,
          'Point_of_purchase__c',@point,
          'Order_Number__c',@order,
          'Lot_A__c',@lot,
          'Best_by_Date_A__c',@formatBestByDate,
          'OwnerId',@ownerId,
          'Origin',"Web"
          )

You can specify a Case owner in your code, or rely on assignment rules in Salesforce to do it for you.

If your Salesforce org uses Leads, you will need to repeat the LookupRows or RetrieveSalesforceObjects process for the Lead object. If no Contact and no Lead match the email address, then you will create a new Lead. This is very similar to what web-to-case does, except that if web-to-case finds more than one match it will not connect the case to any contact or lead and instead make you do it manually.

SET @leadId = CreateSalesforceObject(
          'Lead',4,
          'FirstName',@firstName,
          'LastName',@lastName,
          'Email',@email,
          'Company',"Company Placeholder"
          )

After a Lead is created, the Salesforce Id of the new Lead is returned and you can then follow the previous steps to attached a new Case to that new Lead.

That is all you need to do to create a new Case in Salesforce. However, everything we have covered so far can be accomplished with a web-to-case form, so here are the extra things you can do that makes this process an improvement over web-to-case:

Populate a dropdown menu dynamically with AMPScript. In this case, we are going to populate the Product dropdown field with current active products in Salesforce. The Product object in Salesforce is not related to the Case object. However, there is a custom product field on the Case object in this org and we want to allow the end-user to select the product that they are contacting us about without having to manually type in the product. This also ensures that data is consistent for the purposes of reporting service trends on each particular product.
We are going to use a combination of AMPScript and HTML to organize a dropdown menu with a lookup to the Product synchronized data extension. In this case, I am fine with data that is only being updated every 15 minutes. However, if you need more real-time information for a dropdown menu (perhaps inventory matters), you can use RetrieveSalesforceObjects here as well.
The below code uses Bootstrap CSS to structure the form field. It also uses Bootstrap Select to make the select menu searchable.
The code between the AMPScript indicators – %%[ …. ]%% – is running two loops. The outer loop is grabbing the family of products and the inner loop is grabbing the products within each family. The loop starts with “Other” for orphaned products (which should be cleaned up in Salesforce) and then follows with each product family. Click here to see this in action.

<select class="form-control selectpicker" id="Select_Product" name="Select_Product" data-show-subtext="true" data-live-search="true">
    <option selected="selected">Select Your Product...</option>
            
    %%[
        VAR @i, @familyRows, @familyRow, @familyName, @prevFamilyName, @rowsToReturn

        SET @familyRows = LookupOrderedRows('Product2_Salesforce',0,'Family asc',
                                            'IsActive','True")

        SET @prevFamilyName = ""

        FOR @i = 1 to RowCount(@familyRows) DO
        SET @familyRow = Row(@familyRows,@i)
        SET @familyName = Field(@familyRow,'Family')
                
        IF empty(@familyName) THEN
            SET @familyName = "Other"
        ENDIF
  
        IF empty(@prevFamilyName) or @familyName != @prevFamilyName THEN
            SET @prevFamilyName = @familyName
    ]%%
        
    <optgroup label="%%=v(@familyName)=%%">
          
    %%[
        SET @familyName = Field(@familyRow,'Family')
    
        VAR @rows, @rowCount, @x
    
        SET @rows = LookupOrderedRows('Product2_Salesforce',0,'Name asc','IsActive','True', 
                                      'Family',@familyName)
            
        SET @rowCount = rowcount(@rows)
            
        FOR @x = 1 to @rowCount DO
            VAR @productName
            SET @row = row(@rows, @x)
            SET @productName = field(@row,'Name')
    ]%%
          
    <option>%%=v(@productName)=%%</option>
    
    %%[
           NEXT @x
         ENDIF 
       NEXT @i
    ]%%
      
    </optgroup>
</select>

Create an Email Message record on the Case so that the customer service agent can simply hit “reply” to a message in the Case Activity stream, rather than creating a new email message to the customer.
When we created the Case in Salesforce, we populated the description field on the Case with the comment section of the form. However, a really nice feature of email-to-case is that customer service agents can just hit “reply” in the activity section of the Case and the body of the incoming email is populated in the email like when you reply to any email in your inbox. We can actually spoof this behavior using AMPScript and the CreateSalesforceObject function. For reasons unknown, you need to populate both the from and to email address with the submitted email address.

SET @emailId = CreateSalesforceObject(
        'EmailMessage',6,
        'fromAddress',@email,
        'FromName',@fromName,
        'ToAddress',@email,
        'HtmlBody',@HtmlBody,
        'ParentId',@caseId,
        'Subject',@subject
        )

CreateSalesforceObject(
        'EmailMessageRelation',3,
        'EmailMessageId',@emailId,
        'RelationId',@contactId,
        'RelationType',"FromAddress"
        )

Create rich content in the EmailMessage HTML body. Web-to-case does not allow rich text areas, so any formatting in a textarea field will be lost in the standard web-to-case form. This is actually not a problem when populating the description field in the Case from an HTML form as we did above, where you are capturing comments in a textarea type field. The formatting naturally holds up. However, it becomes a problem when trying to insert the textarea content into the HtmlBody field of the EmailMessage record.
This can be fixed by replacing line breaks with HTML line breaks. You will notice in the above code that there is a variable called @HtmlBody. Here is how you populate that variable with a string that will show up properly formatted in the EmailMessage record:

SET @HtmlBody = replace(replace(@description ,char(13),""), char(10),"<br/>")

Allow users to upload a file. This is a big gap in web-to-case functionality. There are two ways to approach a solution for this:
1. Here is a great blog that walks you through uploading a file to Marketing Cloud.
2. Here are instructions on how to instead send the file via API to Salesforce. If you run this code after you have created the case then you have the record ID to relate the document to.