Integration with Sugar is very common where details from a remote system needs to be made available to Sugar users.
The usual way to transfer data is to ask the remote system for all records that were recently created or modified after a specific date and time. This means that only a small number of records needs to be imported each time the integration process runs.
Of course this depends on the remote system being able to provide recent data, either by direct database access or via web services. What do you do when the remote system doesn’t record the last modification date?
One answer is to (as I have implemented once) just copy all the records each time the integration runs. This has many disadvantages, including the extra band width and processing time, and the fact that last modification dates of the data in Sugar become meaningless.
There is a simple solution to this which involves setting up a new table to record the changes and additions. Ideally this is held in the remote system, but if you don’t have suitable access to it, you can hold the table with the integration process or within Sugar.
This table includes one record for each record in the remote system being integrated. The one table can handle records for any number of remotes system tables. It includes four columns as follows
Table name – name of the table in remote system
Primary key for the table in the remote system
A text field – see below
A modified date/time
The text field is a JSON string of an array of the remote systems record.
The process reads all records from the required tables in the remote system. For each it used the table name and primary key value to see if the record exists in our integration table. If not it creates one with the current date/time.
If it finds the record, it compares the JSON encoded array of values, with the equivalent from the just read remote system record. If they differ (i.e.. if any of the fields within the remote record have changed since the last time the integration process ran, the integration table is updated with the new JSAON value and the date/time is set.
The Sugar integration can use this integration table any time it likes, with SQL something like.
SELECT * FROM integrationTable WHERE table = 'Accounts' AND modDate >= '2013-07-27 12:23'
This approach does take more disk space but allow you to structure date with last modified date/time.
Another big advantage is in terms of creating web service API for others to access your system. You could create a single table like this, and make it the only database table accessible by your API. That would simplify the API and ensure that API methods didn’t have to work on your live tables, just the single integration table. Of course this only applies if the API are read only.
Another advantage is that in creating this single integration table, you could include details from more than one table. An example might be the you include the account name along with contact details.
I have implemented all the above in PHP, and applied it to a live Sugar database. I know Sugar records last modified date/time but I’m doing it as test and it proves to run very quickly. It reads lots but only inserts or updates a relatively small number.
I am interested to hear your opinions on this approach and would be happy to share my code. Any feedback would be welcome.
Update 10th July 2017
I just had another Sugar integration process that needed this approach. It worked very well, and was really the best approach where you don’t have a modified date. Instead of retaining the actual data, I created and saved a hash value, as the data was quite large