Salesforce Replication to SharePoint and SQL Server

Chitra Dharaiya

May 23, 2023

All Post

We faced a client challenge for replicating Salesforce content, metadata and objects into SharePoint in real-time, and further, replicate the content into SQL Server.

As part of the existing process, users would create objects; in this case Companies, Contracts, Stock Tickers, Contract Teams, as objects in Salesforce.

Our automation would then replicate these objects and content as SharePoint Libraries and Document Sets, and would add as entries into SQL Server tables. The SQL data would be reflected in real-time in a Power BI dashboard.

The image below shows the flow of information-

Here’s how the Document Sets (Contract) and Libraries (Companies) are created in SharePoint and entries are added to SQL Server tables when they are created in Salesforce,

SharePoint Lists for managing contracts, deals, metadata and associations, as replicated out of Salesforce:

• Companies List – Used to maintain the number of Libraries in the SharePoint site
• Contracts List – Used to maintain the number of Document Sets in Libraries
• Intermediate Contracts List – Used to create Document Set when a Contract is created in Salesforce.
• Intermediate Companies List – Used to create Library when a Company is created in Salesforce.

SQL Tables used to store Company/Contract and associated metadata:

• Companies – Used to store information of Libraries in SharePoint site
• Contracts – Used to store information related to Contracts and Companies it resides in, and SharePoint details as well
• Contracts Item – Used to store information related to folders/documents created in Document Sets i.e., Contracts in SharePoint

For a New Company created or an existing one updated in Salesforce, we create a new Library in real-time, or update the exiting one in the SharePoint site.

The Companies flow gets triggered, with specific actions performed in sequence:

• Check if the Company is already added to the SQL table,
• If yes, we check the SQL table and get the Guaranteed Unique Identifier (GUID) and make further changes based on this lookup. If the metadata is updated or the Company’s name is changed, it is done on both the SQL table as well as in the SharePoint library.
• If no, we add an entry to the Intermediate Companies List. Once the entry is added a Remote Event Receiver (RER) we write is triggered.

The RER performs the following steps-

• Creates a Library in the SharePoint from an existing template
• Updates the name of the Library as provided in the List and adds the content type and columns needed
• Creates an entry in the Companies Table in SQL with the Library’s GUID

For a New Contract created or an existing one updated in Salesforce, we are creating a Document Set in the Library or updating an existing one in SharePoint site.

The Contracts flow gets triggered, here are the actions performed in sequence-

• Check whether the Contract is already added to the SQL table,
• If yes, we check if the Library already exists in the Companies SQL table. If not, we create the library first with the same process described above. Then, we check the SQL table and get the GUID of library and item’s ID and make any changes using our automation. If the metadata is updated or Company’s name is changed, it is done both the SQL table as well as in the SharePoint Document Set.
• If no, we add an entry to the Intermediate Contracts List. Once the entry is added, the RER is triggered.

The RER performs the following steps-

• Creates a Document Set in the SharePoint Library from an existing template
• Updates the name of the SharePoint Document Set and updates the metadata properties into columns as well
• Creates an entry in the Contracts Table in SQL with the Library’s GUID, Document Set’s ID and the metadata from Salesforce.

Also, if the flows failed, we design the flow to fail gracefully; as, we trigger email notifications actions after every major action in the SharePoint and SQL being made, so if the previous action failed and the flow gets terminated, we get an email with the details of the Library/Company and Document Sets/Contracts.

The overall Goal for the Client was to see the information from Salesforce being replicated to Power BI as Power BI reports are central to how key users conduct their work.

The Power BI reports act as a the main intranet jumping-off page, for all efforts, based on the given Contract.

Adding SQL Server as the source for Power BI reports helps greatly, as the sync time is far lower than for other data sources. This was key to achieving near-real time replication from Salesforce all the way into the Power BI reports.

The Reports in Power BI show the metadata along with all the key Clients and Contract and document information.

We are replicating into SQL the links to the Companies, Contracts, and Documents in the SQL table that points to the Library, Document Set, and Documents in the SharePoint site respectively, so when they are clicked, it redirects to that object in SharePoint. The libraries, document sets, folders and documents are literally one-click away.

While implementing the solution, we overcame four obstacles-

1. Creating a Library and Document Set from the same flow.

Initially we planned on using a single flow to manage creating and updating the Libraries and Document Sets, but it became difficult with creating and updating both. So, we decided to use different flows for Libraires and Document Sets.

2. Creating a Document Set and Library from a Remote Event Receiver (RER) instead of using the Flow with the listdata.svc method.

Using the listdata.svc method has its limitations, so we had to switch to using q RER (Remote Event Receiver) to create libraries and Document Sets.

With using a RER, we came up with a solution to use Intermediate lists for both Libraries and Document Sets i.e., Companies and Contracts.

3. Bulk updates on Companies in Salesforce

Another curveball came when the client confirmed that they had bulk updates on Companies over a certain period. So, we had to enhance the flow for Libraries where it can handle one and many updates in a single instance.

This was caused by large scale bulk uploads, which was unanticipated, given how Contract management was anticipated.

4. Using SQL Server as source for Power BI dashboard report

As we know, repeated creation of new connections in Power BI is a tedious task, and creating a Flow to connect each library to a Power BI dashboard would be difficult and time-consuming as there were thousands of libraries, so we chose to use SQL Server as the database for Power BI which would get the data from several tables and display it in dashboard.

In short, the capabilities of Power BI, Power Platform and SharePoint can be combined to create real time enterprise systems that save time daily for key users, and enable no-code/low-code rapid development to bring value to enterprises and delight end users.