Leverage Zoho API with On-Premise Systems

Last Updated 10/28/2022

INTRODUCTION

Hybrid IT systems combine on-premise data centers with cloud technologies. Various organizations have to focus on adopting cloud computing as part of their IT strategy and take advantage of cloud resources and services that are cost effective and bring added value to their business. Interdependency with legacy systems and cost of migration may lead to a partial migration to the cloud and the paradigm of hybrid IT. With a good data integration in place companies can take advantage of hybrid IT systems and gain cloud computing added value fast while mitigating costs. Zoho CRM is one example of a cloud-based resource that can be integrated with existing on-premise systems and provide the added value related to a web-based platform.

Zoho REST APIs allow developers to build apps using any programming language. One useful application is to leverage Zoho API and build VBA modules to connect MS Office systems with Zoho.

SCENARIOS

Data Processing

Working with data in MS Office applications like Excel or Access using VBA has some advantages over Zoho and Deluge. Large data sets, fast data entry or advanced algorithms implemented in VBA are few reasons for implementing a solution to import Zoho data into on-premise applications.

Data Migration

Zoho CRM is a cloud software product that eases the pressure of maintaining and updating systems, allowing key stakeholders to focus their time, effort and money into fulfilling their core business strategies. Zoho offers data migration wizards to import data from CRM systems like Salesforce, MS Dynamics, Maximizer CRM and others. Custom data migration solutions from on-premise systems can be implemented using Zoho APIs with gradual data migration and data synchronization.

KEY ELEMENTS

Last Modified Time

Zoho API allows to filter records based on Modified Date. Each module in Zoho CRM has a Modified Date field that is used to filter records. This allows an API request to retrieve the records modified after a certain date and time.

The following VBA code returns first 200 accounts modified after 1/5/2021 11:42:40 AM:

     Dim sUri As String
     Dim http As Object
     Dim Response As String

     sUri = "https://www.zohoapis.com/crm/v2/Accounts&page=1"

     Set http = CreateObject("MSXML2.XMLHTTP")

     http.Open "GET", sUri, False
     http.SetRequestHeader "Authorization", "Zoho-oauthtoken " & [Auth Code Goes Here]
     http.SetRequestHeader "If-Modified-Since", "2021-01-05T11:42:40-07:00"
     http.send (sUri)

    Response = http.responseText
 

Last Modified Time is a very useful module field when it comes to data synchronization and it helps select all records modified after a certain date and time based on an input parameter sent from the client. On-Premise systems can rerun data loading procedures using old input parameters to capture year to date or month to date records and synchronize data sets.

It is a good practice to capture Last Modified Time for each record and save most recent value to on-premise database and use it as a starting point in time for next synchronization to avoid extra processing.

Identifiers

Zoho CRM modules have a unique identifier that can be very useful while integrating with on-premises applications. Record ID for a record can be found at the end of the record URL. For example, Leads module record URL looks like this:

     https://crm.zoho.com/crm/.../tab/Leads/2361554000016943219

Record identifier is highlighted in bold and it appears after the last forward slash sign.

On-premise databases can use a column with a unique index to store record identifiers from Zoho. API responses return record identifiers with every call. Setting up columns for Zoho record identifiers should be done early during developemnt phase for every table involved in data synchronization.

Figure 1. Linking Zoho Identifiers with on-premise table

TAKEAWAYS

Zoho API provides the core functionality to create a robust data integration solution.

• Consider gradual data migration and synchronize your modules
• Implement data synchronization using Last Modified Time field from Zoho modules
• Use Zoho identifiers to update on-premise tables and maintain records integrity