Select Page

SurgarCRM CE (Community Edition) is an open source client relation management software. Recently I had the… I am searching for the right word… is it pleasure, no; opportunity; no that isn’t it either. I was looking for information on how to use SugarCRM CE’s REST API to get contact information based on the contact’s e-mail address. I think the word is “painful” experience. But, eventually I was successful so I thought I would share my experience.

What is REST?

Basically it is a way of getting information from a web service. In the case of SugarCRM it is a way of sending predefined requests (such as login, or get_entry_list) to SugarCRM and have returned data (such as a login session id, or a list of data from the database). What makes it nice is that it uses JSON (JavaScript Object Notation) instead of XML. The REST requests are simply HTML POSTS sending JSON data to the SugarCRM REST API URL, and getting returned a JSON object with the data.

Now that everyone who is not a developer has left this article, lets continue.

Let me start by saying the SugarCRM CE documentation is woefully inadequate, compared to PHP documentation or WordPress documentation. This is probably due to a much smaller user community. You can do web searches, but you don’t find much. I am used to finding long threads of discussion with copious examples when searching on other topics, but with SugarCRM I found most threads consist of short discussions and links to the same example.

Here is the short version of what I wanted to do. Access the Sugar database, passing an email address, and getting returned the contact information of the user with that email address. Seems like something many people would want to do. So lets get started.

The first thing you have to do is to make a REST API call to log in to Sugar. Since we are going to be sending multiple API calls it makes sense to create a function for the actual REST call. We are coding this in PHP and to make an HTTP request in PHP we will use curl. Here is the function:

Don’t worry about understanding this code, treat it like a black box. All it is doing is making a HTML POST call to the URL, sending some POST data, and then returning the results back. The main thing you need to be concerned with is that your installation of PHP needs to have curl. Also the code above will not work if your REST URL uses https. Read the curl documentation if you need to use https.

Now we are ready for the first step. Logging in and getting a session ID back that we can use for subsequent REST calls.

First define the path to the REST API.

To login you need to have your admin account name and password, and the URL to the REST API interface. You can use any account name and password, but using an account that is an admin account will let us do more.

Note: if you go to the URL directly in a web browser it will return the class definitions and methods you can use. With Sugar CE the documentation is confusing, since it references SOAP errors even though this is a REST API. However it does describe the parameters so it is good to take a look at it.

So we set up a parameters array to hold the account information;

Now JSON encode that data and add it to the other post arguments:

Notice the “method.” It is one of the predefined Sugar REST API commands that you can make. So now make the REST request and get back the data:

If you run this code and do a print_r($results) you should see something like:

The key data item you need for further REST calls is the id value, so we get it and save it in a variable:

Not too bad so far. Now to make a REST call to search for the email address. Here is where it gets difficult. There is a REST method “get_entry_list” which you should go read about. Go ahead and look at the Sugar documentation on it. I will wait.

Notice that there is only one example and that the description of parameters says nothing about parameters being required or optional. Now look at your rest.php page. I can’t give you a link to that but the path should be like:

but with your path to your SugarCRM installation. Below is the part on “get_entry_list”:

Looking at this it seems that the selected_fields parameter is optional which implies all the others are required. But one of the few examples I found showed the following:

no query

Which worked fine, returning a data structure with all of the contacts. So I thought, great, lets try the same but with the selected_fields parameter set. So I changed it to:

This did not work! It still returned all fields. After much trial and error I added all parameters and actually got it to work.

This returned an array of data, below is an example of one of the records.

So now on to writing a query parameter to search by e-mail address. The REST API wants a syntactically correct SQL “where” statement, just without the “where keyword.” So looking at the data that was returned you would expect to be able to query on the field “email1.” Don’t bother trying. Take a look at the forum discussion at:

It turns out that email1 is a special “reference point,” not an actual field. So to be able to write correct “where” statements you pretty much need to look at the database itself. I opened phpMyAdmin and took a look at the contacts table. Examining the fields shows nothing relating to email addresses. But there are other email tables, so you will have to create a where statement using database table joins.

Here is where I got lucky. I did a search and found one example showing this query.

Of course there was no description of what actually is going on with the query. So looking at the tables in the query, there are two. They are email_addresses and email_addr_bean_rel. email_addresses is pretty straight forward. It contains fields for the email address, email address in all caps, and flags for opt_out, invalid_email, and deleted.

email_addr_bean_rel is a mapping table that links the email address to records in other tables, such as contacts, leads, users etc.

So using phpMyAdmin I converted the php query into actual SQL statements so I could see what was going on. I created an SQL query to match the php query.

This query worked and took about .05 seconds to run. Looking at the sub query I noticed that it was returning all records from the relation table regardless of which module was being related. The relationship table has fields for the bean_module (i.e., contacts, leads, etc.). So if the email address being looked for was related to multiple modules, then the sub query would return multiple records. I wondered if it would be more efficient for the sub query to return only those records in the contacts module. So I modified the query to be:

The query took about the same amount of time, but my data set is small. It might make a difference with a larger data set.

So below is the final code fragment used to return the contact based on searching for the email address:

Lessons learned

There are not a lot of good examples of using REST calls to interface with Sugar CE. The basic concepts of using the REST interface are pretty easy, but you need to understand the underlying data base structure to make good queries. And for anything complicated you should have a firm grasp of SQL, especially joins.

It would be nice if Sugar improved their documentation. I would suggest that they look at the WordPress codex and at the PHP documentations on the web. I especially like that the PHP documentation includes user submitted code.

So if you are trying to write REST code to do things with your Sugar CE data, best wishes. I hope this helped. Below are a few of the links that I found helpful:

  1. Being RESTful with SugarCRM – IBM – United States
  2. A PHP example demonstrating how to log in and retrieve a session key using cURL and the v4_1 REST API.
  3. Creating or Updating a Record