Introduction

Sage CRM is integration-ready straight out of the box. It can integrate quickly with Sage BMS products, as well as other external applications through secure, standards-based web services and APIs.

In this article I want to discuss the different approaches that can be taken by a developer creating an integration with an external system.

From when Sage CRM was first developed the product has always been open to integration and extension. Thanks to its open architecture and web services interface, Sage CRM enables easy integration with other business applications and on-demand services. This capability is very important to your business and for the delivery of better business management.

Examples of Integration

There are many business drivers that encourage an organization to develop integrations with other systems. One such driver is the ideal that there should be only a one time data capture of any item of business information. The information that the business needs should be captured at it's point of origin (whether the Sales department, Finance Team, or support desk) and then have that data automatically propagated to all relevant fields throughout the Sage front and back-office environments without having to wait for it to be re-keyed and re-validated as it passes from one system to the next.

Sage CRM has a range of integration SDKs (Software Development Kits) which can be used to address customer specific integration requirements. The Sage CRM SDK supports an open web services API, a COM programming interface, the creation of .NET application extensions and of course Client Side scripting including REST based Ajax calls. The Database is open with a published application data model.

An integration with an external system will vary dependent on the business need. Integrations range from loosely coupled or attached systems to tightly coupled portals and portlets. A portal style integration is where the Sage CRM interface becomes the User Interface for the other system. A portlet approach is where CRM's data is embedded within another application.

Many businesses need to integrate with third party applications and their own legacy systems. These systems range from BMS (Sage 50, Sage 100cloud, Sage 200cloud etc), and other systems such as Event Planning and Human Resource Management software. This integration can also be with systems designed to allow greater flexibility for the workforce such as a Mobile Offline Clients.

Existing Models of Integration within Sage CRM

Sage CRM offers a range of examples of how integration with an external system can be approached. There are examples for both core behaviour and integrations with other Sage ERP systems.

1. Outlook Integration
2. Exchange Integration
3. BMS Integration such as

• Sage 100cloud (Editions in US, France, Germany etc)
• Sage 200cloud (UK and Ireland)
• Sage 300cloud (Sold in across many territories)

Outlook Integration

Sage CRM offers to synchronize appointments, tasks and contacts with Outlook. This synchronization is configured by the System Administrator who can decide the direction and scope of data exchange.

Exchange

The aim is to ensure that calendar and contact details are synchronized between the two systems.

For example

When an appointment is created, edited, or deleted by a user in CRM, a corresponding appointment is automatically created, edited, or deleted in Exchange.
When an appointment is created, edited, or deleted by a user in Exchange, a corresponding appointment is automatically created, edited, or deleted in CRM.

Sage BMS Integration

Sage CRM provides out-of-the-box integration with leading Sage BMS and accounting products including Sage 300cloud, Sage 100cloud (US), Sage 200cloud (UK), Sage 100cloud (France, Spain and Germany). This means that companies that have previously invested in Sage BMS products can quickly and cost effectively leverage their back-office data and functionality within the front-office environment of Sage CRM.

With a Sage CRM and BMS integration, users can access their back office customer accounts, orders, quotes and product information in Sage CRM. If desired, users can configure the integration so that any changes made in Sage CRM are synchronised back to the accounting system. Users can also link orders with Sage CRM's sales force automation and can view back office orders in Sage CRM reports provided they are synchronised to Sage CRM.

Sage CRM APIs

The 3 Main tool set options in Sage CRM for the developer are:

• COM API
• .NET API
• Web Services (SOAP & REST)

COM API

The COM API options include the following:

• Internal Scripting (field level, table level, and workflow conditions etc)
• External Scripting which includes

• ASP pages (The main tool for application extension)

• Self Service COM API (Used for extending the system to public web sites)

• Windows Scripting

• Mail Manager scripts (Used for monitoring and processing inbound emails)

.NET API

The .NET API is designed to allow development partners to create richer and tighter integrations and extensions of the product.

The diagram below shows the general architecture of Sage CRM. Here you can see very clearly that the .NET API is an alternative and NOT a replacement for the COM API which is currently used to build application extensions with ASP pages.

Both the .NET API and the COM ASP API use the Meta Data based structure of Sage CRM in broadly similar ways. In the diagram below we can see that the properties for any screen and screen element (List, Column, Screen, Entry Field) is determined by definitions held in the Meta Data tables (prefixed "custom_").

Both APIs use objects and methods to obtain the definition of the screens from Meta Data and generate the HTML necessary for the output to the browser.

Development Using .NET and COM API

The development of application extensions using either ASP pages (with the COM API) or the .NET API shares certain features.

There is no middleware as Sage CRM will communicate directly with the external system. This is Direct Integration and there has to be 'foreign key' linkage between the systems.

The exchange of data between the systems is Event Driven. If a changes occurs within Sage CRM then it would immediately be posted in the external system and if the external system changes the data would be sent immediately to CRM. Table Level Scripts would be used to maintain data in the external system as the insert, update and delete events occur within Sage CRM.

Using either the .NET API or ASP pages an integration would fast to develop. The Sage CRM Meta Data and concept of blocks would allow the data remaining in the external system to be presented within the Sage CRM interface. For example we could show Account details held in an external database.

A development will become more complex when carrying out updates to the external system where the external systems business rules need to be followed to ensure data integrity. If these rules are not encapsulated in API objects then there maybe a need to call the actual screens from the external application. This maybe the requirement for areas such as inserting orders or quotes into an external ERP system.

Note:

An important thing to note is that the architecture requires the application extensions to be on same server as the CRM install. We need to have the ASP pages or the .Net assemblies on the same machine as Sage CRM. This will limit options for hosted or remote installations.

Web Services Interface

Sage CRM's SOAP based web service API enables developers to manipulate CRM records remotely with SOAP over HTTP using XML. It is possible to access a CRM server or a hosted system from a specified client machine (typically another server) in order to read, create, update, or delete records for each exposed entity, for example, Companies, People, Opportunities, Cases, Quotes and Orders.

It does not provide users with a GUI, which is the case with the .NET and COM APIs. Instead, Web Services share business logic, data, and processes through a programmatic interface across a network. Developers can add the web service to a GUI, such as a Web page or an executable program, to provide users with the required functionality.

Development Using Web Services

A developer is going to need to consider whether the exchange of data between the systems is to be event driven or will need to be based on regular polling of those systems.

The design for a Web Service based integration may require some type of middleware that acts as an integration hub. If it is not possible to detect insert, update and delete events within the internal coding points of the external application then changes to the data will need to be looked for by another component. This can be imagined as a service polling each application (Sage CRM and the external system) on a scheduled basis and checking whether any data has changed in each and then causing the synchronization to occur.

Where the external system is remote then access to the system will need to be via Web Services. The coding to insert, update and delete data may also make it easier to carry out using a separate integration hub.

Web Service integrations can be hybrid with part of the integration using .NET assemblies or ASP pages. This is very often the case where simple viewing of the data is needed and Sage CRM can make a direct connection to the external systems tables.

We are still presented with a problem if the external system has complex logic that needs to be followed to ensure business data integrity as inserts, updates and deletes occur. If this is the case we may need to use the actual screens from the external application. Ideally the external systems web services interface should take that logic into account when allowing insert, update and delete messages to be sent.

Note:

For Integration with Sage BMS systems Sage CRM has some internal changes which make it a highly BMS-aware system. It has an internal web services Integration Framework which makes Sage CRM and Sage BMS integrations easy to install and which delivers powerful out-of-the-box functionality. This is accomplished by web service calls that Sage CRM knows it can use with the Sage BMS systems. This allows for a simple direct interaction with those Sage systems.

External System APIs

The choice of which approach to use for the integration also depends on the API provided by the external system. Will the developers have access to a COM, .NET or Web Service Interface? How will application design be affected is only asynchronous Message Queuing is available?

Information Available

In its simplest form the integration of another system within the Sage CRM interface would allow data to be accessed when it was needed. It is a desirable objective to ensure that the most accurate and up-to-date customer data is available to front-office and back-office employees at all times.

When considering an integration we need to think about the entities in each system to be linked. For example how do we relate the data in the external system to the data inside CRM? Is there a structure in CRM that maps to a structure in the other application?

For this article I have imagined I have an external system that is used to record Supplier details and their contracts. For this example I want to synchronise the data from the external supplier database table with the Sage CRM company table.


If we consider the partial example above that associates an external suppliers table with the company table we need to have a link between the two systems. The supplier record approximates to the company record within CRM.

The associative link is possible through the use of a foreign key. We can use an additional field within Sage CRM to hold the external records ID. e.g. comp_supplierid. In the above example, the external system's supplier table's primary key (supplierid) has been added to CRM's company record as a foreign key (comp_supplierid).

If the value is null in the foreign key then the record is not linked and if the value is populated then the records are linked and synchronization could occur. The link would be on a 1 to 1 basis and so validation would needed to ensure that any key held was unique.

Integration and Synchronization

Having a foreign key reference within a Sage CRM table point to an external systems data allows for the real time access of that data. If we only need to view the external data then both the .NET API and the COM API (in ASP pages) support the ability to access the external data in real time.

In an integration it maybe desirable to allow synchronization of common data, so if a change is made in CRM it is changed in the external system. This is especially true in the integrations with Sage ERP systems that exist.

This requirement to move data between one system and the other is present where the connection between Sage CRM and the external system is not always available. This is the case for Outlook where the data is copied to allow separate and independent use of the data when the client machine is disconnected from the network.

Scope of Synchronization

If data is going to be exchanged between two systems we must consider whether synchronization based on changes occurring either to the Row or Field is possible.

In Sage CRM each Record that belongs to the system has 3 change fields

• xxxx_createddate
• xxxx_updateddate
• xxxx_timestamp

As a record is inserted the 3 fields are marked with the date and time of the insertion. As the record is changed using the Sage CRM user interface (or APIs) then the updateddate and the timestamp change to the date and time at which the changes are saved.

If the date of the last synchronization is known, it is then possible to find those records that have changed within Sage CRM since that date. The APIs including the Web Service Interface have easy to use mechanisms to establish this information.

But Sage CRM does not include a track record of changes to the field data. The partial exception to this is in tables that use a progress table to copy the changes that are made to data over time using workflow. This is only the case for four entities in the default system:

• Leads
• Solutions
• Cases
• Opportunities

Synchronization of data with Sage CRM tables would therefore become Row based. Because we can see only that the table row has changed it will mean the management of conflicts becomes very important.

Considerations for Synchronization

These are some of the questions that are useful to consider when planning a synchronization.

• Will the synchronization just be one way? Or will it support bi-directional changes?

• If the change happens in CRM will it be carried across into the external system?
• Will changes that take place in the external database be carried into CRM?

• What are the allowed changes to be carried out? Are Inserts, Updates and Deletes to be included?

• Insertion of records

• • If records are to be imported into Sage CRM from the external system will this be carried out automatically?
  • Will there be a need to carry out a check for duplicates in Sage CRM?

• • If records are to be promoted from Sage CRM to the external system will this be automatic?
  • Will there be a need to carry out a check for duplicates in the external system?

• Deletion of Records

• • If records are to be deleted in one system, should deletion be synchronised?
  • Should Sage CRM records be hard deleted or soft deleted (which is default behaviour)?

• How are Dates to be matched?
• How are different Data Lengths to be handled?

• If the data in one system is longer than the other will truncation of the data occur?
• Will the synchronization system allow for the automatic increase in the length of the fields?

• Sage CRM uses security territories to mark data (e.g. comp_secterr).

• How will new data be added to the system?
• Will the new data be added to a standard territory like "Worldwide" or "Root" or will it be added to the territory user of user who clicks the 'synchronise' button.

• How will a users different rights to Insert, Edit or Delete data be used in the synchronization of data.
• Will field level security policies be checked?

• How will data in CRM be marked for synchronisation?

• Will all changed records in the Sage CRM table be synchronised?
• Or will only a subset be used? An example of a subset of data used in synchronization is a Saved Search which is used to filter the data synchronized with the mobile App.

• Who wins in any synchronization?

• If a change has happened in Sage CRM and also in the external system since the last synchronization which system will wins?

• • Will changes in the external system take precedence over Sage CRM or will Sage CRM always win?
  • If there is a conflict how will that conflict be resolved?
  • Where conflicts do occur then there will be a need to create a log of the clashes.

• Where Web Services are to be used for the Synchronization you will need to consider security.

• For example will the Web Services be via HTTPS or HTTP?
• Will synchronization be limited to only a specific machine or range of machines?

• Will you use IP address or machine name to control valid clients.

• What application access rights are used to access Sage CRM?

• Will you use anonymous access, or require a username and password?
• How will authentication be managed?

Conclusion

Sage CRM provides excellent tools for the developer to create integrations with external systems. These tools are the APIs and the SDKs. The Sage CRM software itself is very easy to use and offers powerful facilities for connecting to external databases and modelling those external tables in meta data. The Meta Data approach allows the APIs to construct screens and system access to external data within Sage CRM. Sage CRM in turn is very open in design and the business data can be easily accessed by external applications. But the ease of use and power of the tools available can not remove the obligation of the developer to spend a considerable amount of effort in designing and planning any integration. No API can decide the processes and features that have to be built.

I hope that the questions and points raised in this article will allow you to feel confident about designing an integration with an external system, what ever API you choose to use.