{"id":1481,"date":"2023-05-23T06:05:09","date_gmt":"2023-05-23T06:05:09","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=1481"},"modified":"2024-04-26T11:56:11","modified_gmt":"2024-04-26T11:56:11","slug":"salesforce-replication-to-sharepoint-and-sql-server","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/salesforce-replication-to-sharepoint-and-sql-server\/","title":{"rendered":"Salesforce Replication to SharePoint and SQL Server"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_65 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 eztoc-toggle-hide-by-default' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/salesforce-replication-to-sharepoint-and-sql-server\/#While_implementing_the_solution_we_overcame_four_obstacles\" title=\"While implementing the solution, we overcame four obstacles-\">While implementing the solution, we overcame four obstacles-<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/salesforce-replication-to-sharepoint-and-sql-server\/#1_Creating_a_Library_and_Document_Set_from_the_same_flow\" title=\"1. Creating a Library and Document Set from the same flow.\">1. Creating a Library and Document Set from the same flow.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/salesforce-replication-to-sharepoint-and-sql-server\/#2_Creating_a_Document_Set_and_Library_from_a_Remote_Event_Receiver_RER_instead_of_using_the_Flow_with_the_listdatasvc_method\" title=\"2. Creating a Document Set and Library from a Remote Event Receiver (RER) instead of using the Flow with the listdata.svc method.\">2. Creating a Document Set and Library from a Remote Event Receiver (RER) instead of using the Flow with the listdata.svc method.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/salesforce-replication-to-sharepoint-and-sql-server\/#3_Bulk_updates_on_Companies_in_Salesforce\" title=\"3. Bulk updates on Companies in Salesforce\">3. Bulk updates on Companies in Salesforce<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/salesforce-replication-to-sharepoint-and-sql-server\/#4_Using_SQL_Server_as_source_for_Power_BI_dashboard_report\" title=\"4. Using SQL Server as source for Power BI dashboard report\">4. Using SQL Server as source for Power BI dashboard report<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<p>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.<\/p>\n<p>As part of the existing process, users would create objects; in this case Companies, Contracts, Stock Tickers, Contract Teams, as objects in Salesforce.<\/p>\n<p>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\u00a0<strong><a href=\"https:\/\/reality-tech.com\/power-bi-interactive-dashboard-development-features-benefits\/\" target=\"_blank\" rel=\"noopener\" aria-label=\"Power BI dashboard - open in a new tab\" data-uw-rm-ext-link=\"\">Power BI dashboard<\/a><\/strong>.<\/p>\n<p><strong>The image below shows the flow of information-<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1485 size-full\" src=\"https:\/\/poiseddevelopers.com\/reality-tech\/wp-content\/uploads\/2024\/03\/img-01-11.webp\" alt=\"img-01\" width=\"750\" height=\"478\" srcset=\"https:\/\/poiseddevelopers.com\/reality-tech\/wp-content\/uploads\/2024\/03\/img-01-11.webp 750w, https:\/\/poiseddevelopers.com\/reality-tech\/wp-content\/uploads\/2024\/03\/img-01-11-300x191.webp 300w\" sizes=\"auto, (max-width: 750px) 100vw, 750px\" \/><\/p>\n<p>Here\u2019s 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,<\/p>\n<p><strong>SharePoint Lists for managing contracts, deals, metadata and associations, as replicated out of Salesforce:<\/strong><\/p>\n<ul>\n<li><strong>Companies List \u2013\u00a0<\/strong>Used to maintain the number of Libraries in the SharePoint site<\/li>\n<li><strong>Contracts List<\/strong>\u00a0\u2013 Used to maintain the number of Document Sets in Libraries<\/li>\n<li><strong>Intermediate Contracts List<\/strong>\u00a0\u2013 Used to create Document Set when a Contract is created in Salesforce.<\/li>\n<li><strong>Intermediate Companies List<\/strong>\u00a0\u2013 Used to create Library when a Company is created in Salesforce.<\/li>\n<\/ul>\n<p><strong>SQL Tables used to store Company\/Contract and associated metadata:<\/strong><\/p>\n<ul>\n<li><strong>Companies<\/strong>\u00a0\u2013 Used to store information of Libraries in SharePoint site<\/li>\n<li><strong>Contracts<\/strong>\u00a0\u2013 Used to store information related to Contracts and Companies it resides in, and SharePoint details as well<\/li>\n<li><strong>Contracts Item<\/strong>\u00a0\u2013 Used to store information related to folders\/documents created in Document Sets i.e., Contracts in SharePoint<\/li>\n<\/ul>\n<p><strong>For a New Company created or an existing one updated in Salesforce<\/strong>, we create a new Library in real-time, or update the exiting one in the SharePoint site.<\/p>\n<p><strong>The Companies flow gets triggered, with specific actions performed in sequence:<\/strong><\/p>\n<ul>\n<li>Check if the Company is already added to the SQL table,<\/li>\n<li>If\u00a0<strong>yes<\/strong>, 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\u2019s name is changed, it is done on both the SQL table as well as in the SharePoint library.<\/li>\n<li>If\u00a0<strong>no<\/strong>, we add an entry to the Intermediate Companies List. Once the entry is added a Remote Event Receiver (RER) we write is triggered.<\/li>\n<\/ul>\n<p><strong>The RER performs the following steps-<\/strong><\/p>\n<ul>\n<li>Creates a Library in the SharePoint from an existing template<\/li>\n<li>Updates the name of the Library as provided in the List and adds the content type and columns needed<\/li>\n<li>Creates an entry in the Companies Table in SQL with the Library\u2019s GUID<\/li>\n<\/ul>\n<p><strong>For a New Contract created or an existing one updated in Salesforce,<\/strong>\u00a0we are creating a Document Set in the Library or updating an existing one in SharePoint site.<\/p>\n<p>The Contracts flow gets triggered, here are the actions performed in sequence-<\/p>\n<ul>\n<li>Check whether the Contract is already added to the SQL table,<\/li>\n<li>If\u00a0<strong>yes<\/strong>, 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\u2019s ID and make any changes using our automation. If the metadata is updated or Company\u2019s name is changed, it is done both the SQL table as well as in the SharePoint Document Set.<\/li>\n<li>If\u00a0<strong>no<\/strong>, we add an entry to the Intermediate Contracts List. Once the entry is added, the RER is triggered.<\/li>\n<\/ul>\n<p><strong>The RER performs the following steps-<\/strong><\/p>\n<ul>\n<li>Creates a Document Set in the SharePoint Library from an existing template<\/li>\n<li>Updates the name of the SharePoint Document Set and updates the metadata properties into columns as well<\/li>\n<li>Creates an entry in the Contracts Table in SQL with the Library\u2019s GUID, Document Set\u2019s ID and the metadata from Salesforce.<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>The Power BI reports act as a the main intranet jumping-off page, for all efforts, based on the given Contract.<\/p>\n<p>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.<\/p>\n<p>The Reports in Power BI show the metadata along with all the key Clients and Contract and document information.<\/p>\n<p>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.<\/p>\n<div style=\"background-color: white; box-shadow: 0 0 10px whitesmoke; padding: 20px; width: 800px;\">\n<h4 style=\"color: black;\">Additional Read<\/h4>\n<p><a style=\"color: #1f6799; text-decoration: none;\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/salesforce-replication-to-sharepoint-and-sql-server\/\" target=\"_blank\" rel=\"noopener\">Salesforce replication into SharePoint and SQL<\/a><\/p>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"While_implementing_the_solution_we_overcame_four_obstacles\"><\/span>While implementing the solution, we overcame four obstacles-<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"1_Creating_a_Library_and_Document_Set_from_the_same_flow\"><\/span><strong>1. Creating a Library and Document Set from the same flow.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_Creating_a_Document_Set_and_Library_from_a_Remote_Event_Receiver_RER_instead_of_using_the_Flow_with_the_listdatasvc_method\"><\/span><strong>2. Creating a Document Set and Library from a Remote Event Receiver (RER) instead of using the Flow with the listdata.svc method.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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.<\/p>\n<p>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.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_Bulk_updates_on_Companies_in_Salesforce\"><\/span><strong>3. Bulk updates on Companies in Salesforce<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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.<\/p>\n<p>This was caused by large scale bulk uploads, which was unanticipated, given how Contract management was anticipated.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"4_Using_SQL_Server_as_source_for_Power_BI_dashboard_report\"><\/span><strong>4. Using SQL Server as source for Power BI dashboard report<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>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.<\/p>\n<div style=\"background-color: white; box-shadow: 0 0 10px whitesmoke; padding: 20px; border-radius: 5px; display: flex; justify-content: space-around;\">\n<div class=\"content\">\n<h4><b>Start Your Project in a Click<\/b><\/h4>\n<p style=\"width: 85%;\">Our technology and wide delivery footprint have created billions of dollars in value for clients globally and are widely recognized by industry professionals and analysts.<\/p>\n<\/div>\n<div style=\"display: flex; align-items: center;\">\n<div class=\"content-btn\" style=\"padding: 8px; border: 1px solid #009a00; width: 238px; cursor: pointer; transition: background-color 0.3s;\"><a style=\"color: #009a00;\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/contact-us\/\" target=\"_blank\" rel=\"noopener\">Let&#8217;s connect and brainstrom!<\/a><\/div>\n<\/div>\n<\/div>\n<p>In short, the capabilities of\u00a0<strong><a href=\"https:\/\/reality-tech.com\/solutions\/power-bi\/\" target=\"_blank\" rel=\"noopener\" aria-label=\"Power BI - open in a new tab\" data-uw-rm-ext-link=\"\">Power BI<\/a><\/strong>,\u00a0<strong><a href=\"https:\/\/reality-tech.com\/solutions\/power-platform\/\" target=\"_blank\" rel=\"noopener\" aria-label=\"Power Platform - open in a new tab\" data-uw-rm-ext-link=\"\">Power Platform<\/a><\/strong>\u00a0and\u00a0<strong><a href=\"https:\/\/reality-tech.com\/capabilities\/sharepoint-online-and-office-365\/\" target=\"_blank\" rel=\"noopener\" aria-label=\"SharePoint - open in a new tab\" data-uw-rm-ext-link=\"\">SharePoint<\/a><\/strong>\u00a0can 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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":10,"featured_media":1483,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[32],"tags":[],"class_list":["post-1481","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sharepoint"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1481","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1481"}],"version-history":[{"count":8,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1481\/revisions"}],"predecessor-version":[{"id":2855,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1481\/revisions\/2855"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1483"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1481"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1481"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1481"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}