MySQL Quick Hit: Drop First Word

Here is a quick tidbit you might find helpful.

Recently I ran into a scenario where a number of records in a table named contacts had an undesired word at the beginning of the value in the last_name column. Here is an example:

Table: Contacts

first_name       last_name

Angel            Zyx Magaña

This complicated the searching of records based on last name because one had to use a wildcard or search for the preceding value in the last_name field, along with the desired value, hence the need to adjust the data. 

To adjust the data, it was necessary to execute an UPDATE query against the table in question. However, it was important to take into consideration the fact that there were some records in the table that did not have the offending value preceding the actual last name. The only real way to differentiate between valid and invalid entries was to assume that any record with more than 1 word in the last_name field required adjustment.

Side note: The caveat with this approach is that valid last name values that use 2 words, such as Mac Donald, would also be affected. However, the number of records meeting that criteria was very low and could easily be corrected manually at a later time.

Based on this strategy, our first step would be to figure out the word count on the field. Unfortunately, MySQL does not provide a function to do this, but a reasonable alternative would be to figure out if there if a space exists in the value of the field. A space in the field would equate to a separation between two or more words. For this, MySQL does provide a function, called LOCATE(). 

Given our main concern was to drop the first word in the field, i.e. anything before the first instance of a space, the LOCATE() function serves our purposes rather well. Next, our query had to instruct MySQL to update the last_name column with the value following the space, whether it was 1 or 4 additional words. Finally, we also had to limit it to records where a space actually existed between words. This latter part is accomplished by the WHERE clause in our final SQL query, demonstrated below:

UPDATE contacts SET last_name = TRIM(SUBSTR(last_name, LOCATE(' ', TRIM(last_name))))

WHERE LOCATE(' ', TRIM(last_name)) > 0

In case you are wondering about the use of the TRIM() function, it is to eliminate any preceding and trailing spaces in the value.

SugarCRM 101: Licensing and Vertical Markets

gnu.org

There are some subtle nuances to non-technical aspects of SugarCRM that have very meaningful implications, but are often not well understood. One such nuance relates to licensing.

Conversations relating to the topic usually resemble the following:

What is the per user cost of a SugarCRM license?

Can one resell a customized version of SugarCRM?

Is it possible to provide SugarCRM hosting services?

One of the first important points to bear in mind regarding these types of questions is that the answer can vary depending on the edition of SugarCRM in question. This is especially true for the question of licensing costs, as Community Edition is completely free, while Professional, Corporate, Enterprise and Ultimate all require payment on a per user, per month basis.

More importantly, many of these conversations tend to only revolve around equating open source to FREE (as in costs, not liberty). As discussed, it is true for Community Edition, but not for the other editions of SugarCRM -- although they too are open source. 

It is also equally important to note that open source is a programming philosophy that incorporates both the idea of making software available at no cost, as well as free to be shared. This latter part has significant ramifications on the answers to questions such as those posed earlier in this post and in turn, business models one might be considering that involve SugarCRM.

Perhaps the most overlooked matter of importance is the issue of whether or not one can customize and then resell SugarCRM. Usually this comes up within the context of the idea of creating a vertical version of SugarCRM. 

Not to get too sidetracked, but vertical refers to a version of a product that is highly tailored to fit the needs of a specific industry, such as real estate, healthcare, automobile dealerships, etc. A default SugarCRM install is said to be horizontal, as it is not specifically designed for any given industry, although its flexibility makes it a more than adequate starting point for many vertical solutions.

SugarCRM Cookbook: Adding Leads via SOAP

bvdservices.com

Back in 1996 when I first got my start in the CRM world, I remember how impressed I was by a new feature about to roll out in an upcoming release of our software. It was something called "web import," which allowed web site visitors to enter information on a standard web form and the submitted data would then appear in the CRM system without any further user intervention. It was the early days of leveraging the web as a lead generation tool and this feature was nothing short of magical.

Nowadays, it is not so magical and is a standard feature in many CRM packages. In SugarCRM, it manifests itself as something aptly named Web-To-Lead. Through this feature, one can create a web form to embed in a company web site, with the necessary behind the scenes magic that allows for the data to automatically enter the SugarCRM system as a lead.

The drawback of this automated approach is that the resulting web form usually requires some fine tuning in order to seamlessly incorporate it into a company web site. For example, colors, fonts and other cosmetic aspects will likely need some adjustment. If you are using a Content Management System (CMS) such as Drupal or Joomla!, other challenges are likely to present themselves in the process. 

But all this means is that one might need to use a different approach to get the job done and sure enough, SugarCRM provides another method for entering data, more specifically, an Application Programming Interface (API). Through it, one can communicate with SugarCRM to read or write data, among various other things. 

There are two built-in APIs for SugarCRM, one is SOAP based and the second uses REST. The finer details of each are beyond the scope of this post, but for our purposes, we will focus on SOAP and the point that both allow us to interact with SugarCRM in a much more controlled manner, using a wide range of programming languages common to the web world. 

Leveraging the API gives us greater control than the auto-generated form by allowing us full control over which fields are to be populated. Further to this, it allows us to access any part of SugarCRM, opening up the possibility of performing additional tasks at the time the lead enters the system, such as scheduling a follow up call, attaching a document or numerous other things. Lastly, it is also makes it possible for us to build the form using whichever method is already in place for creating the web site and simply tie it to the API -- it is automatically seamless.

So, how do we go about creating a lead via SOAP? Lets take a look.