ERP Sync API Strategy

Overview

The goal of this document is to provide a high-level strategy for syncing data between your ERP system and the CIMcloud platform.  The main areas of this project are:

  • Determine which tables and fields in your ERP system will be needed on CIMcloud’s platform
  • Map those tables and fields to corresponding tables and fields in CIMcloud’s system
  • Decide which API endpoints will need to be used to sync data between the two systems
  • Build an application that does the following:
    • Pulls data from your ERP system and pushes it to the CIMcloud API
    • Pulls data from the CIMcloud API and imports it into your ERP system

General Concept of API / ERP Sync Tool

Table and Field Mappings

Determine Table and Field Mappings

The first step of this project is to determine which tables from your ERP system will be needed on the CIMcloud platform, and then which fields from those tables you will need. 

For example, if you have product information you want to make available on CIMcloud’s platform, you would need to determine what table(s) store your product information in your ERP system, and then what table(s) that information needs to map to in CIMcloud.  The table below shows an example of how you could define these mappings.

ERP Table CIMcloud Table
CI_Item products
CI_ItemAdditional products
AR_Customer accounts

Once you’ve mapped the tables, you will then need to map the individual fields from each table.

You will need to do this for each table that will sync from your ERP system to CIMcloud, and you will also need to do this for transactional data in CIMcloud (i.e. orders) that will need to sync into your ERP system.

Example document:

Tables / Data Entities (API Methods)

These are the typical / core data entities.  Others may be involved (depending on final scope).

  • Products
  • Inventory Levels
  • Pricing
  • Warehouses
  • Customers
    • Accounts
    • Addresses
    • Contacts?
  • History / Tracking
    • Orders
    • Order Lines
    • Vaulted Cards?
    • Shipments
    • Invoices
    • Invoice Lines
    • Payments
    • Payments to Invoices
    • Credits
    • Credits to Invoices

Determine API Endpoints

Once you’ve defined the table mappings, you’ll need to determine which API endpoints will be used for each mapping.

ERP Table CIMcloud Table API Endpoint
CI_Item products /Products/
CI_ItemAdditional products /Products/
AR_Customer accounts /Accounts/

Build the Sync Application

Once you have planned out all of the data mappings and which endpoints you’ll use for each mapping, you will need to build the application that will handle syncing data between your ERP system and the CIMcloud system.

One method for running this application is to run it as a service on the same server as your ERP system, and have the application run in a constant loop looking for data to sync.

Here is a high level overview of what you’ll need to do within the application:

To Web: ERP → CIMcloud

General Process / Steps

  • Track Data Changes (Adds / Edits)
    • Method #1: reliable add / last modify date field on records
    • Method #2: built in change tracking in ERP
    • Method #3: SQL triggers to queue table (table + record key)
  • Pull Record(s) and Transform (single table, multiple record)
    • With Query and/or Code / Custom Functions
  • Send to API
  • API Receives and Confirms
  • API Drops to Message Queue
  • Automated Process Pulls from Queue to DB
  • Logs to Logging DB (Retries if Needed)
  • Handle deleted records
    • Same concept as above – track when a record is deleted from the ERP, send the deleted record to the API to delete/disable
    • Methods for deleting via API
      • Hard deletes (actually remove record, generally not done)
      • Soft deletes (disable)
        • This is (generally) an update setting the status = 0
        • This is the recommended approach

Application Functionality

  1. Sync data from your ERP system to CIMcloud
    1. For each table in your ERP system that you want to sync to CIMcloud
      1. Query the table and look for changes to any data since the last time it was synced
        1. You will need some method of change tracking.
          1. Method #1: reliable add / last modified date field on records
          2. Method #2: built in change tracking in ERP
          3. Method #3: SQL triggers to queue table (table + record key)
        2. You will also need some method of tracking the last time a record was synced. i.e. you could use a last synced date on the record.
        3. You cannot just sync all records all the time.
      2. For each record that has changed, you will build a JSON object in the format specified by the SWAGGER documentation.
      3. Make a call to the CIMcloud API endpoint to POST/PUT the data
      4. Track the last time the sync ran for the table, so that you can control how often the sync runs for each table. For example, you may want to only sync product data every 5 minutes. This will help reduce resource usage like CPU and network.

From Web: CIMcloud → ERP

General Process / Steps

  • Pull from CIMcloud API
  • Auto-Posts as Exported in the CIMcloud database
  • Import to ERP
  • Post Fate back to CIMcloud API
    • I.e. erp_import_status = success/failed, erp_import_error = {error message preventing order from importing successfully}
  • Monitoring / Alarming
    • Based on exported & import status fields
    • Color
      • Tracks delays in imports
    • Fate
      • Tracks errors during imports

Application Functionality

  1. Sync data from CIMcloud to your ERP system (i.e. orders)
    1. Similar to #1, for each type of data you want to sync from CIMcloud to your ERP
      1. Make a call to the CIMcloud API endpoint to look for new records
        1. If you get data back from the API, you’ll need to parse the response and format it as needed so that you can import the data into your ERP system
      2. You will want to have some method to limit how often this hits the CIMcloud API looking for updates. Either use thread sleeping or a last run datetime so that the process only runs every X minutes (2 minutes, 5 minutes, whatever works best for your use case) looking for updates.

Related Articles