Coding Against the Salesforce.com API

Salesforce.com has done an incredible job documenting its API, as well as providing useful examples. In fact, all the code required to access your account and retrieving data is provided in the SDK, which allows you to focus on your business requirements.

In this case, the only requirements are to fill a list box with Salesforce.com contact records and then fill the Work Order with contact details from the selected contact.

First add a custom control to the project and name it SFTaskPane. Draw a list box control on it, and name it lstRecords. This is all that is required for the custom control, as it does not contain any code. Instead, ThisDocument.vb contains all code for this project.

Open ThisDocument.vb, and declare the following namespace declarations at the top of the class:

  Imports WorkOrder.sforce1
  Imports Word = Microsoft.Office.Interop.Word
  Imports Office = Microsoft.Office.Core

Next, declare the following class-level variables needed to execute the solution:

  Private binding As Sforce1.SforceService
  Private loggedIn As Boolean = False
  Private loginRes As LoginResult = Nothing

  Private contacts() As String = Nothing
  Dim ctl As New SFTaskPane
  Dim WithEvents lst As Windows.Forms.ListBox

The code sequence for this application follows a predictable pattern. The first step at startup is to login to Salesforce.com, display the custom SFTaskPane control in Word's document actions task pane, and fill the control with records retrieved from Salesforce.com. The Startup method of the class is the proper location for this code.

  Private Sub ThisDocument_Startup(ByVal sender As Object,
    ByVal e As System.EventArgs) Handles Me.Startup

    LoginToSF("testSF@gmail.com", "test") 
    Globals.ThisDocument.ActionsPane.Controls.Add(ctl)

    RetrieveContacts()
    lst = ctl.lstRecords

  End Sub

Notice that the last line of the method sets a reference to the SFTaskPane control's list box (lstRecords). Since the list box was declared as a class-level variable using the WithEvents key word, you can code against the list box's events.

As you may have noticed in the Startup method, the document automatically logs the user into her Salesforce.com account. The LoginToSF method is straight out of the SDK; I just changed the name of the method as it suited my purposes better. The code creates a reference to the Salesforce.com Web services, and establishes a live session by passing the login credentials.

Private Function LoginToSF(ByVal userName As String,
    ByVal passWord As String) As Boolean
    '//Create the binding to the sforce servics
    binding = New SforceService

    '//Time out after a minute
    binding.Timeout = 60000

    '//Attempt the login giving the user feedback
    Try
      loginRes = binding.login(userName, passWord)
    Catch e As System.Web.Services.Protocols.SoapException
      '// This is likley to be caused by bad username or password
      Console.Write(e.Message & ", please try again."
        & vbCrLf & vbCrLf & "Hit return to continue...")
      Console.ReadLine()
      Return False
    Catch ex As Exception
      Console.Write(ex.Message & ", please try again."
        & vbCrLf & vbCrLf & "Hit return to continue...")
 
      Return False
    End Try

    '//Change the binding to the new endpoint
    binding.Url = loginRes.serverUrl

    '//Create a new session header object 
    '//and set the session id to that returned by the login
    binding.SessionHeaderValue = New sforce1.SessionHeader
    binding.SessionHeaderValue.sessionId = loginRes.sessionId

    loggedIn = True

    '//call the getUserInfo method

    loggedIn = True
    Return True


  End Function

For the purposes of this article, I decided to hard-code the username and password inside the code. In production, you would place these values as settings outside the code — either using the application's Settings dialog, or in an outside xml file or a database. This would allow you to change the values without having to recompile the code.

Once logged in, the Startup method calls RetrieveRecords. This method sends a SQL statement to Saleforce.com's Query method. In this case, we want to select all the fields that would map to the elements in contact.xsd. All records returned by the query are then inserted into the lstRecords list box.

Private Sub RetrieveContacts()

    Dim qr As QueryResult = Nothing
    binding.QueryOptionsValue = New Sforce1.QueryOptions
    binding.QueryOptionsValue.batchSize = 3
    binding.QueryOptionsValue.batchSizeSpecified = True

    Try

      qr = binding.query("select FirstName, LastName from Contact")
      'create a looop control variable for the loop & 1 behavior 
      Dim bContinue As Boolean = True
      Dim loopCounter As Integer = 0
      While bContinue
        'process the query results
        loopCounter += 1
        For i As Integer = 0 To qr.records.GetUpperBound(0)
          Dim con As Contact = CType(qr.records(i), Contact)
          Dim fName As String = con.FirstName
          Dim lName As String = con.LastName
          If fName Is Nothing Then

            ctl.lstRecords.Items.Add(fName & " " & lName)
          Else

            ctl.lstRecords.Items.Add(fName & " " & lName)
          End If
        Next
        'handle the loop + 1 problem by checking to see if the most recent queryResult
        'set is
        If qr.done Then
          bContinue = False
        Else
          qr = binding.queryMore(qr.queryLocator)
        End If
      End While
      Console.WriteLine(vbCrLf & "Query succesfully executed.")

    Catch ex As Exception
      Console.WriteLine(vbCrLf
      & "Failed to execute query succesfully, error message was: "
      & vbCrLf & ex.Message)

    End Try

  End Sub

When the list box contains data, the user will most certainly click on items in the list and expect something to happen. This "something" is triggered by the list box's SelectedIndexChanged method:

Private Sub lst_SelectedIndexChanged(ByVal sender As Object,
    ByVal e As System.EventArgs) Handles lst.SelectedIndexChanged
    Dim sName As String = lst.SelectedItem.ToString
    Dim sArray() As String = Split(sName)

    InsertContactInfo(sArray(0).ToString, sArray(1).ToString)

  End Sub

Anytime the user clicks on a new item, this method parses the value of the selected item into a string array. It passes the values to the InsertContactInfo method, where it's used to query the selected contact's records.

Private Sub InsertContactInfo(ByVal FirstName As String, ByVal LastName As String)
    Dim qr As QueryResult = Nothing
    binding.QueryOptionsValue = New Sforce1.QueryOptions
    binding.QueryOptionsValue.batchSize = 3
    binding.QueryOptionsValue.batchSizeSpecified = True

    Try
      'Query the desired fields from the Contact table for the selected record.
      qr = binding.query("select FirstName,LastName,Department,Email,
           MailingCity,MailingPostalCode,MailingState,MailingStreet,
           Phone,Title  from Contact
           where LastName = '" & LastName & "'
            and FirstName = '" & FirstName & "'")


      Dim con As Contact = CType(qr.records(0), Contact)

      Me.SF_ContactFirstNameNode.Text = con.FirstName.ToString
      Me.SF_ContactLastNameNode.Text = con.LastName.ToString
      Me.SF_ContactDepartmentNode.Text = con.Department.ToString
      Me.SF_ContactEmailNode.Text = con.Email.ToString
      Me.SF_ContactMailingCityNode.Text = con.MailingCity.ToString
      Me.SF_ContactMailingPostalCodeNode.Text = con.MailingPostalCode.ToString
      Me.SF_ContactMailingStateNode.Text = con.MailingState.ToString
      Me.SF_ContactMailingStreetNode.Text = con.MailingStreet.ToString
      Me.SF_ContactPhoneNode.Text = con.Phone.ToString
      Me.SF_ContactTitleNode.Text = con.Title.ToString


    Catch ex As Exception
      Console.WriteLine(vbCrLf
      & "Failed to execute query succesfully, error message was: "
      & vbCrLf & ex.Message)

    End Try

  End Sub

This method uses the passed name values to retrieve the record selected in the list box. Once retrieved, the method inserts the contact's values into the document by inserting its field values into the matching XML elements in the Work Order document.

All of the nodes dropped into the document are accessible from the My object. This means that all you need to know about the document, in order to insert data, is the XML schema. There is no longer any need to manipulate the Word object model. This makes for a much cleaner and simpler method for automating documents.

Run the Work Order Solution

Figure 3. The Work Order document at run-time.

Now that all the code is written, press F5 to run the application. Does the list box fill with contacts from your Salesforce.com account? Try clicking the various accounts. Does the document update to reflect the contact info of the selected record? If so, your application should look something Figure 3.

As you can see, building a Smart Client application in Word does not require a significant investment time. Although the application discussed in this article is fairly simple, the concepts behind are not; VSTO just makes it look trivial which is a testament to how Microsoft built the product.

The point to take away is that Office can be much more than a tool for authoring documents. It can be a gateway to business information that aids business users everywhere to perform their job as efficiently as possible. And the best aspect here is that VSTO does almost all of the heavy lifting, which allows you to focus on the business issue at hand.