Integration: Case management in Salesforce, documents in SharePoint

This is a multi-part series where we discuss how to integrate SharePoint and Salesforce. There isn’t a one size fits all, I will share a few of the different ways we’ve done it in past projects.

• Best In Class(es): Bringing Salesforce and Office 365 Together. Instead of forcing one platform to do it all, aim to leverage the best of both platforms: Salesforce and Office 365.
• THIS POST: Case management in Salesforce, documents in SharePoint. We created a folder creation process to store case files in SharePoint and giving users the full breadth of document capabilities of SharePoint.

Coming up next:

• Uploading files to SharePoint tightly coupled to Salesforce metadata. We created a custom upload form to associate specific metadata from Salesforce to the file in SharePoint, along with providing Salesforce the references to the files.
• Salesforce Files Connect using a SharePoint Library. A basic approach to displaying files from SharePoint in Salesforce.
• Enterprise search across Salesforce and SharePoint. Search and discovery across both platforms is essential for users, in this approach we created a hybrid search center to allow users to perform searches across both Salesforce and SharePoint.

---

Quick intro: this customer is a legal firm and uses Salesforce to manage all of the contacts and cases they go to court with.

Within my customer’s Salesforce, they have a case management screen, which includes the complainant, respondent, legal areas in violation, and loads of other metadata. From this screen, they wanted to be able to manage files associated to this case.

We couldn’t use the native Files Connector in Salesforce as that is a read-only view of a file store. They can’t upload or edit those files within Salesforce. Also, the customer wanted to leverage the full suite of document management capabilities that SharePoint has to offer. Instead of trying to force this into Salesforce, we simply send the user to a SharePoint library and they can just use it all.

We added a button to the case screen creatively named “Document Management”. Easy enough!

When a user presses that button, the SharePoint Online library is opened in a new tab in the browser. But where do we send the user? How do we know what case we’re on?

The button in Salesforce goes to a SharePoint page that has a little custom code on it, and the query string has the metadata associated to the case: complainant, respondent, respondent ID, case number, etc. We are navigating to something like this:

https://contoso.sharepoint.com/sites/cases/pages/casefolder.aspx?casenumber=ABC123&respondent=Bruce%20Wayne&complainant=Tony%20Stark...

We created a custom page (JavaScript in a basic page) in SharePoint which received that metadata, checked to see if folders existed and then either 1) created the folders if they didn’t exist or 2) send the user to that folder.

Upon creation the users can begin uploading files to the folders and sharing with their colleagues as needed. The metadata from Salesforce is automatically applied to each file uploaded to help with search and discovery of content.

Finally, if metadata ever goes out of sync, mainly from users changing the data in Salesforce, the data can be refreshed in SharePoint using an Azure Logic App.

High Level Design

Some technical details

• We leveraged the SharePoint PnP JavaScript library and jQuery on the case folder page in SharePoint.
• We have a default case folder structure setup in a JSON file on SharePoint to define what folders we should create. The folders we created looked similar to:
  • Respondent Last Name, First Name IDABC123
    • Case Number
      • Research
      • Drafts
      • Correspondences
      • etc
• We used unique content types for each folder level, i.e. Respondent Folder, Case Folder, Working Folder, each with metadata fields to allow the users to view the metadata about the files.
• The default metadata was set by the Azure Logic App, which listens for new requests for folders, and then sets the Column Default Value Settings for the associated folders. Unfortunately these values cannot be set using JavaScript as the users need full control to the library to set it.

Final note

We tried using an iframe on a Salesforce page, showing SharePoint’s library, but SharePoint wouldn’t work! Users couldn’t drag and drop files, couldn’t click to edit a file. Most of the scripts that support SharePoint wouldn’t run in an iframe, so we went with opening a new tab. This ended up being beneficial as it kept the user on their case screen as well as the associated file library.