Sugar Integration Without Last Modified Dates

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.

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






Ways To Change Sugar UI

When you use Studio you can only build user interfaces of a predetermined layout, with predetermined elements. To go beyond the standard involves coding, but what options do you have?

I’m no expert but I have worked with a number of approaches which I had described below. As you can see, these are very much a work in progress.

1. Amend vardefs files

You can edit these directly. I will add an example later.

2. Use Logic Hook with after_ui_frame

This will add code after the UI has been loaded into the browser. You can’t just add text as I have done as there is no way of knowing where it will arrive in the page. It would be better to set the inner HTML, or add a javascript file, or style sheet.

I haven’t explored these but you can see that this does give you the scope to change or add to the UI

In the following example I have added code to the  file custom/modules/Accounts/logic_hooks.php

And included ExtraUIStuff.php in the same directory, as follows.


3. Add new Smarty files

This is the most flexible. I will add a section here to describe what is needed and what can be achieved.

See the following link, until I can test and add my own code.

More to follow…..


Debugging Web Services

Debugging normal  PHP web applications is pretty straight forward. I use Eclipse but almost all IDEs have some means of setting break points and seeing the value of variables. I know some people put print or log statements in their code but this is much more time consuming than just setting a breakpoint and looking at the variables there, and maybe steping forward to see how the script runs.

I would be interested to hear if others usually use IDEs like Eclipse and debug in this way. I would hate to work without it. I even use it to check a newly written script or one that I have taken over responsibility for. Stepping through the processing can help you spot problems very easily.

Now all that is ok for a simple script or web application, but what about the server end of a web service? Normally the server runs in response to a client call. The client request may be quite complex, so how do you debug the server side?

Well my answer is to log the client request as it is received by the server as follows. Add this code to the very top of your server entry point, then have the client make its request. The server will log the request then carry on to produce a result (or not if there is some bug you want to fix).

To debug the server side, replace the above code with the following.

That’s it!. Now you can debug the server side without needing a client to make a request. The server entry point thinks it is getting a request from a client, but really it is just coming from the log file.

Now all this might seem trivial or obvious, but it took me a while to work this out when I had my first web service server to debug. Suddenly I’m just debugging a stand alone script rather than a server than depends on an external client request.

I would be very interested to see if this is useful to anyone.