Sage ERP/CRM  Integration and Synchronization with Office 365 and SharePoint

Sage ERP/CRM can be integrated with Microsoft Office 365, SharePoint, Exchange, Dynamics and 150+ more data sources using the Layer2 Cloud Connector in just minutes codeless, with no-touch, and bi-directional. You will find more supported systems and applications here.​​​​

Available on-premise or online, Sage 300 ERP (formerly Sage ERP Accpac) is a comprehensive business management solution designed to keep your total cost of ownership low by supporting multiple technologies, databases, and operating systems.

Sage 300 ERP is a Windows based range of ERP software, available with a variety of database backends. It has the following modules and features: Multi-Company and Global Operations Management, Customer Relationship Management, Intelligence Reporting, Accounts Payable, Accounts Receivable, Alerts and Alerts Server, Fixed Asset Management, General Ledger, Project and Job Costing, Return Material Authorization, Transaction Analysis and Optional Field Creator, US & Canadian Payroll, Inventory Control, Purchase Orders, Sales Order Entry, and Intercompany Transactions. It is multi-user, multi-currency and multi-language and available in five languages: English, Spanish, French and Chinese (Simplified and Traditional). Sage ERP/CRM data queries can be connected and synchronized codeless with almost any external systems, on-premises or in the cloud, using the Layer2 Cloud Connector.

Sage ERP/CRM Data Integration and Synchronization - Features and Benefits 

Integrating Sage ERP / CRM data via the the Layer2 Cloud Connector has the following features and benefits:

• Many systems and apps can be connected out-of-the-box: Often requested data providers like ODBC, OLEDB, OData, Exchange, SharePoint, Office 365, XML, RSS, SOAP etc. are included in the distribution package, vendor specific ADO.NET data providers like for SQL Server, Oracle, mySQL etc. can be also used, also as well as 3rd party data providers e.g. for Google, Twitter and Facebook. On-premises and cloud-based systems and apps are supported. Please find your specific data source or destination here.
• Implementation can be done quickly, risk-free and codeless: No programming or tools required. Setup in a few minutes guided by a wizard. Sample connection templates included. Just adapt your connection string and data query.
• No touch integration: The connection does not require any customization, changes or installation on Sage or the external data source side, e.g. Office 365or SharePoint.
• Very flexible sync options: One-way or two-way synchronization supported, insert/update/delete operations can be blocked, if required. Scheduled update in background, or on-demand only.
• Simple and reliable architecture: A connection is established only for data synchronization – not for general usage by end-users.
• Support for all features in both connected systems: All Sage features provided for connected data sets. All features are available for the external systems as well, e.g. in case of SharePoint / Office 365: views, search, metadata, change notifications, workflows etc.
• Best performance: Fast synchronization, supports large datasets. No bulk-import - data changes are processed carefully. Users directly work with cached data sets in for best user experience.
• Highest Security: No need to open your local network for access from outside. Secure communication, e.g. SSL/HTTPS (depending on data provider). No 3rd party or clouds involved. The Layer2 Cloud Connector runs as a Windows service on a Windows server or client in your secure internal network and directly connects both ends for sync.

Sage ERP/CRM Specific Configuration Settings

​In the Layer2 Cloud Connector the data source or destination entity must be configured as follows to connect to Sage ERP (based on SQL Server in this specific case).

 Fig.: Sage 300 ERP / SQL Server sample connection in the Layer2 Cloud Connector

Please note the following about settings, features and possible issues.

• Please select the SQLClient Data Provider from the list of installed providers. If you can't find, please install from Microsoft.
• You can make use of any connection strings for SQL Server as usual, e.g.:
  Data Source=myServer; User Id=myUser; Password=myPassword.
  You will find more information about SQL Server connection strings here.
• In case of integrated authentication (trusted connection) please make sure that the current user has appropriate database access rights. Also take care about access rights of the Layer2 Cloud Connector Windows Service. Best to use basic authentication with a specific user.
• You can make use of all SQL queries your data provider supports. You can also use stored procedures that returns records, exec myStoredProcedure().
• You can make use of aliases in query to force auto-mapping in the Cloud Connector, e.g. select customername as title, to auto-map to a SharePoint title column.
• The primary key is normally set automatically, if exists in table.
• Bi-directional sync is generally supported. You query must be updatable for this (note that most joins are not). You also need write access rights for this.

Do you have any issues to connect? Please contact [email protected] for next steps.

Step-by-Step Intros For Specific Sage ERP/CRM Integration Scenarios

​You will find more information about specific scenarios here:

• How to connect and sync SQL Server data queries with Microsoft SharePoint or Office 365 for better collaboration.
• Connect and sync Sage ERP / CRM data queries with Dynamics CRM via OData
• Push SQL Server data to Microsoft Exchange for mobile and offline access on any device (BYOD)

Please also note this product video at YouTube. Can't find the scenario you are looking for? Please contact [email protected] for help.

Sage ERP/CRM Data Integration - Known Issues and Workarounds

• Date formats:  In case of SharePoint connections Sage dates must be converted to the data type SharePoint expects for date fields. This is accomplished using the SQL Convert function as shown below where the AccPac field is PAYMNTASOF date.
  CONVERT(datetime, CONVERT(VARCHAR(8), PAYMNTASOF))As PAYMNTASOF
• Field length: User fields such as REFERENCE, COMMENTS and DESCRIPTION can come over to SharePoint as fixed length, right padded with blanks. Creating a calculated field in SharePoint using the Trim function is one way to save page real estate on views, e.g. remove trailing and or leading blanks. TRIM can be combined with CLEAN to additionally remove undesirable characters as shown below. 
  myField=CLEAN(TRIM(REFERENCE))
• Illegal characters: If user input fields are used in SharePoint as part of an addressable field such as file name it is necessary to remove any illegal characters. This can be done in either the SQL statement within the SQL statement in the connection file as shown below or within a SharePoint workflow or both. The SQL fragment is shown below.
  COMMENT = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(COMMENT ,'?','_') ,'\','_') ,':','_') ,'"','_') ,'*','_') ,'&','_') ,'.','_') ,'#','_') ,'/','_')

Ready to go next steps?