Logic Hooks: Lead History Transfer

Have you noticed what happens to history information associated with a lead when one converts that lead into a contact within SugarCRM? 

If not, give it a try. Enter a lead record named John Doe, add a Note under its History section. Next, convert the lead into a contact. Now, take a look at the History section on the new contact record for John Doe that was created via the conversion process and compare it to the History section on the lead record. You will notice that the Note entry is nowhere to be found on the contact record and instead, remains associated with the lead record.

This separation allows us to see which interactions were completed when the individual was a lead and which were completed after it was converted into a contact. In turn, it helps us identify processes and techniques that led us to converting it. 

All future activity relating to that individual would be recorded against the contact record, not the lead. However, many users find that the distinction between lead and contact history causes too much confusion.

One problem that is created by this separation of data is that looking at the History section of a contact does not give us a full picture of everything that has occurred over time, as it relates to the individual. One must toggle between the contact and lead record in order to gather that insight. 

How can we address this problem?

One school of thought would bring us to the conclusion that one should not use the Leads module at all and simply enter everyone as a contact. This would solve the History problem, but creates others, such as the inability to track lead conversion rates. 

A second approach would be to carry over the History data from the lead over to the contact record during the conversion. Doing so would solve the problem as all the History would appear on the contact record, but a facility for this does not exist in SugarCRM. Fortunately, it can be easily accomplished via a logic hook.

Let us take a look at the logic hook we would use.

SugarCRM Cookbook: Adding Related Records

Lets jump right into this one and begin by looking at a code snippet:

$rel_name = 'contacts';

$record_id = 'some_id_value';

$bean->load_relationship($rel_name);

$bean->$rel_name->add($record_id);

So what does it do?

Assuming we execute the above code within the scope of a before_save logic hook for Opportunities, the above code would add a linked contact to the opportunity being saved. 

While that example may have limited applications, the snippet of code does not. It can be reused within any custom PHP code you are writing for SugarCRM to link records together, all via the SugarCRM framework.

The advantage of this approach is that it is applicable to any module. In addition, and perhaps more importantly, it is upgrade-safe and eliminates the need for us to know about relationship tables, linking fields or any of the finer details that SugarCRM relies upon to establish relationships between records.

In order to see the power of this little snippet, we need to dissect it line-by-line and expand scope of our example.

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.