MySQL Quick Hit: Counting Records

Within the context of our technical work, we sometimes just need a simple number to guide us along to help answer our questions.

For example, we may be curious about the number of records that were created per day. Or, perhaps we want to know how many were modified, etc. Filters and reports within Sugar can help us get some answers, but we can also do some nifty things by way of SQL queries. 

Let us take a look at the following query:

select distinct(date(date_entered)), count(*) from opportunities

where deleted = '0'

group by date(date_entered);

The above will generate output similar to the following:

Notice that the results provide us an easy to read snapshot of the data we are seeking. Obtaining similar numbers for other criteria is a simple matter of changing the field being analyzed by the DISTINCT() function and GROUP BY clause. Below is an example of a modified version that would give us the number of records that were modified per day, rather than entered as in the original example.

select distinct(date(date_modified)), count(*) from opportunities

where deleted = '0'

group by date(date_modified);

May this help simplify your work.

SugarCRM: Label Placement

Sometimes things happen. Allow me to illustrate.

For a number of years, there has been a little known option within Sidecar view metadata that allowed one to define the placement of field labels relative to the data. You can see the setting defined in line 173 of the snippet pictured below:

Setting the value to false causes the labels to display to the left of the data, as illustrated in the image that follows:

Why would one consider using this feature?

The option has some limitations, but it is helpful for reducing the amount of white space visible to the user. This, in turn, makes the view more compact and allows for more data to be displayed within a given area of the screen. It is also applicable to all users, by default.

Additionally, it is also possible to set the label placement on a per panel or module basis, allowing more granular control over the placement of labels. An example of a per panel configuration is demonstrated below:

Regardless of the manner in which one wishes to implement this feature, it always requires manual intervention, given that the labelsOnTop attribute could not be set through Studio.

The manner in which label placement is defined changed with the release of Sugar 10. 

MySQL Troubleshooting: Error 1118 Row Size Too Large

A while back I found myself attempting to correct an issue relating to the restore of a MySQL database backup. The problem at hand was the result of a very large table that would halt the process with the following: 

Error 1118 Row Size Too Large.

The source of the error was a table that contained numerous fields, tallying in the hundreds. While the database contained this table with a large row size, it did not present any particular issues executing common operations such as INSERTs, UPDATEs or DELETEs. Problems were only present if one attempted to restore the database.

Following various attempts to correct the problem, the solution eventually revealed itself. As it turns out, in some versions of MySQL, a server parameter that enforces certain rules upon creating tables is enabled by default. This validation examines the row size of each table as it is being created and if it exceeds the row size limit for the server, an error 1118 occurs, causing the restore process to halt.

The server setting in question is:

innodb_strict_mode

The setting is either defined as on or off and only affects tables utilizing the InnoDB engine. 

Setting the parameter to off (innodb_strict_mode = 0) and restarting the server allows the restore process to complete. It is worth noting that turning off the setting merely changes the behavior of the server such that it reports row size violations as warnings instead of errors, but does not alter the ability of the server to support more fields per table. This latter point is important because attempts to add additional columns to a table with a large row size will also cause the referenced error.

More information on the setting is found at the following page:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_strict_mode

 

SugarCRM Customization: CSS and Label Tricks

A few weeks ago, a colleague and I were posed an interesting customization scenario pertaining to an easily overlooked area of Sugar. Before we dive into the details of the scenario and corresponding solution, let us review some of the background.

Have you ever wondered where the abbreviations and icon colors for the various modules within your Sugar instance are defined? 

Here are a couple of examples of their use:

The abbreviations and accompanying icon color are automatically defined for each default module that is part of Sugar. Taking a closer look at the abbreviations, you will notice that there is pattern to the value, as it uses the first two letters of the corresponding module name. However, there are also exceptions, where specific letters are used to define the abbreviation, usually to avoid conflicts or more clearly define the abbreviation. 

Custom modules that are added to a given instance follow the same conventions and abbreviations and icon colors are automatically assigned to them too. But how are exceptions handled for custom modules? Or for that matter, perhaps we wish to change one of the default abbreviations or icon colors. How would we go about applying such a change?

SugarCRM Customization: Sending Emails - New and Improved

It seems like an eternity since I posted examples on programmatically sending emails through Sugar.

As some of you might have already correctly surmised, a number of changes have occurred to Sugar since those blog entries were written. One of the major additions to Sugar since then has been SugarBPM, which itself includes an email template engine.

Let us revisit the original problem of programmatically sending emails and examine how we can accomplish the same via SugarBPM. This time around, we will assume our intention is to send an email about an Account record.

We will first need to create the email template containing the content to be sent. Said template can be static or, if desired, may also include references to specific fields from the target module, to be parsed at send time. For our example, we may wish to make an email template that reads as follows:

ACME Corporation
123 Main Street
Some City, CA 90000

...but rather than typing specific values, we would instead insert references to the corresponding fields that contain the values we want from the Accounts module -- our Target Module. That would make the template much more flexible.

Note that the process of creating such a template for SugarBPM differs than that for a standard email template. To create an email template for SugarBPM, select Process Email Templates from the Sugar navigation bar and then click Create. Further information pertaining to this process can be found here:

https://support.sugarcrm.com/Documentation/Sugar_Versions/9.0/Ent/Administration_Guide/SugarBPM/Process_Email_Templates/index.html

SugarCRM: Exceptions

A number of years ago I read a funny comment about computers which read as follows:

"The problem with computers is that they do what you tell them to do, not what you want them to do."

The point being that computers do not think, they just follow instructions. As such, programmers have to be mindful of the instructions given and anticipate problems. While it is not possible to account for every possible scenario that may generate a problem, we do want to accommodate common scenarios and capture problems as exceptions, allowing our code to fail gracefully and providing feedback to the user in the form of an error message.

Within the Sugar framework, it is possible to easily implement such exceptions and provide feedback to the user.

An exception class, named SugarApiException, is provided out-of-box and can be implemented into your PHP customizations as illustrated by the following pseudo-code:



For the above example, the Sugar API would return a response code of 422, with the body of the error message being the string defined in the code (i.e. 'You are missing a parameter required for properly invoking this endpoint.').

Of course, this would be more useful if we had a selection of response codes and errors. The list of available options is defined within the previously mentioned SugarApiException class, found in the following file:

./include/api/SugarApiException.php

Here is the definition for the example used in the pseudo-code, where you will see the response code and error title:

/**
 * One of the required parameters for the request is missing.
 */
class SugarApiExceptionMissingParameter extends SugarApiException
{
    public $httpCode = 422;
    public $errorLabel = 'missing_parameter';
    public $messageLabel = 'EXCEPTION_MISSING_PARAMTER';
}

Hopefully this will help save you time implementing a process for handling errors!

SugarCRM 101: Authentication

Did you know that Sugar supports 3 different mechanisms for authenticating a user upon them attempting to access the application? 

The authentication options are:

Sugar

LDAP

SAML

The Sugar option refers to the standard username/password combination an administrator can configure within Sugar, as described here:

https://support.sugarcrm.com/Documentation/Sugar_Versions/8.3/Ent/Administration_Guide/Users/#Regular_and_Admin_Users

If your organization maintains an LDAP capable server, such as Microsoft ActiveDirectory (AD), it is possible to utilize the AD user list to authenticate users attempting to access Sugar. Further details on configuring this option can be found here:

https://support.sugarcrm.com/Knowledge_Base/Password_Management/Configuring_LDAP_Authentication_Using_Active_Directory/

The third option, SAML, is similar in nature to LDAP in that the user list is maintained outside of Sugar, but access to Sugar can be obtained by providing a set of valid SAML credentials. Greater detail on using this option is given in this example:

https://support.sugarcrm.com/Knowledge_Base/Password_Management/Configuring_SSO_With_Okta/

These options provide a flexible authentication model for Sugar, but the options aren't mutually exclusive and their interaction can sometimes cause a bit of confusion.

For example, it is possible to configure a Sugar instance to use SAML and at the same time allow a user to access the instance by either providing SAML or standard Sugar credentials. It is also possible to configure Sugar such that the LDAP/SAML option is only applicable to certain users.

To alleviate some of that confusion, I have created the below flowchart to describe the process used by Sugar, taking into account the various authentication options that can be configured. 

Figure 1.1 - Authentication Process

I hope this helps answer some of your questions on authentication and Sugar.

SugarCRM DevOps: Compression

Question: Which would download faster, a 3.5 MB sized file or one approximately 500 KB in size?

It is not a trick question. Without a doubt, our expectation would be that the smaller sized file would download faster, regardless of the speed of our network connection to the server. But how can we reduce the size of payload if we cannot trim the actual contents? A quick and easy way to reduce the size would be to compress the content to be sent in the response.

If we apply this principle to Sugar, the compression of payloads sent from the web server to the browser would result in a snappier user experience, due to faster retrieval of data and other necessary components. As a general rule of thumb, we should always implement a compression methodology in order to help us achieve the best experience possible.

But how can one determine if compression is enabled and being utilized by the web server hosting a Sugar instance?

The image in Figure 1.1 helps us answer that question.

Figure 1.1: Example ListView response

To answer our question we must examine the web requests exchanged between our web browser and the web server. This can be easily accomplished within Chrome by accessing the Developer Tools and reviewing the contents of the Network tab, as illustrated in Figure 1.1.

SugarCRM: More Customization Tips

In a previous post on the subject of customizations we explored some general tips and advice that help us avoid a variety of problems. We will continue this discussion, but this time around we will focus on JavaScript. More specifically, we will focus on the manner in which one can handle responses to requests made to the Sugar REST v10 API.

One may have a need to send such a request to:

• Read an existing record
• Update a record
• Load a collection 

The Sidecar framework provides mechanisms for performing all of the above, such as beanObject.fetch(). However, the process for handling responses these methods generate is not readily obvious. Quite often, there is a tendency to rely on raw JavaScript to address such needs, for example, interacting with the xhr object as such:

object.xhr.done()

Rather than using raw JavaScript, one should leverage the parameters that can be defined as part of the method. In the case of .fetch(), it would look similar to the following:


As illustrated in the code, an anonymous function is defined for the success: parameter. This is the code that will be automatically executed upon a response being received and is capable of interacting with the JSON response -- found in the data variable. 

Note that a similar approach can also be used to define an error anonymous. 

SugarCRM: Related Data Interactions

Often times a customization we are working on requires us to interact with related data, usually records that are children of another. For example, a customization might require us to determine if a given Account record has 1 or more related Contacts. Or for that matter, we may need the collection of ID values that represent all the Calls linked to a Contact.

In either example, the path most often followed could be described with the following pseudo-code:

1. Instantiate the parent SugarBean object (e.g. Account/Contact record object)
2. Load the corresponding relationship to access the related data objects
3. Retrieve the related SugarBean objects representing the related objects

See below for a PHP snippet illustrating the above:



Line 12 in the above example would effectively create an array of SugarBean objects representing the linked Contacts. From that point forward, determining whether or not the Account has any linked Contacts becomes a simple matter of checking the size of the array. 

For the linked Calls example, the code would be very similar, except we would load the 'calls' relationship and the ID values we want would be in the SugarBean object array that results from executing the getBeans() method.

Now, all of the above would function just fine, but let us consider a few things about this approach.

SugarCRM: Customization Tips

One of the tasks we regularly perform within the Technical Account Management team at SugarCRM is review customizations, especially code level modifications. Two of the main objectives behind such reviews are: 

• Identify areas of opportunity (customizations that can be further refined or optimized)
• Catalog potential pitfalls one may encounter were the instance upgraded

A challenge posed by such reviews is that any given instance can include customizations that touch a wide variety of the framework features and in different ways. Despite this, certain patterns emerge that point at common habits that require some level of attention and are applied while customizing Sugar. The more common behaviors are discussed in this post in hopes they will help you avoid some frustrations.

1. Incomplete WHERE clauses. SugarQuery is the preferred mechanism for querying the Sugar database programmatically. However, executing an ad-hoc query is sometimes unavoidable. For such scenarios, always remember that Sugar performs soft deletes of its data. Thus, when attempting to interact with "active" records in the database, one must add the deleted = 0 condition to the WHERE clause, or run the risk of working with dirty data. This requirement spans across nearly all tables in the Sugar database, including the relationship tables such as accounts_contacts. Below is an example of its use:

SELECT * FROM contacts WHERE first_name = 'Angel' AND deleted = 0;

2. Using database platform specific features. Expanding on the previous point, the use of raw SQL queries is sometimes unavoidable. Should you find yourself in such a scenario, bear in mind the intended scope of your customization. If you intend for your customization to reach a large number of Sugar instances, avoid using database platform specific features as doing so will automatically cause your customization to only work on a single database platform. 

For example, should we rely on the database engine to generate a GUID for us, MySQL allows us to use the UUID(), whereas MS-SQL uses NEWID(). Herein is the value of generating such a value via PHP or JavaScript, instead of at the database level, given that leveraging the database engine to generate the value would effectively bind our customization to that database platform.

Elasticsearch: Heed the Warnings

A few posts on this blog have talked about different topologies one can use to deploy Sugar, either for development purposes or production use. Perhaps the most important point in those posts is the matter of ensuring that one should not expose the Elasticsearch server to any other machine besides the one where the web server is running.

Because Elasticsearch data can be read without any sort of user authentication, exposing an Elasticsearch server means one is allowing potentially malicious users to view sensitive data. Despite this risk, it is not uncommon to hear of Sugar implementations that are configured in ways where Elasticsearch can be directly accessed via the internet or local network. 

A few days ago, the dangers of such a configuration were further highlighted by ransomware makers. Reports have recently surfaced that data from exposed Elasticsearch servers is indeed being compromised and held hostage.

This threat may leave some Sugar administrators wondering about the impact this could have on their Sugar implementations and data. 

SugarCRM Reference: Analyzing a Record page

As a continuation of a prior post, below is a diagram depicting the various components used on the record page within Sugar 7.

Not unlike other areas of Sugar 7, a variety of layouts and views are used to render this screen. In a follow-up post we will take a closer look at specific areas of this same diagram as there is more info to be had.

SugarCRM: Working with Sugar 7 Sessions

One of the changes that was introduced in Sugar 7 relates to the manner in which user sessions are managed. 

In older versions of Sugar, the length of a user session was determined by a PHP parameter that controls the lifetime of a PHP session. For all intents and purposes, a Sugar session was equivalent to a PHP session and deleting the latter would in turn cause your Sugar session to also be destroyed.

For the Sugar 7 release, this process was changed and a Sugar user session is now primarily controlled by means of a series of OAuth tokens. Those of you that have worked with the REST v10 API should be familiar with the topic, but even if you have not, the information contained herein should still be of help. 

A common question that is asked in relation to sessions in Sugar 7 is: how does one change the lifetime of an OAuth token? 

By default, the access_token has a lifespan of 1 hour and the refresh_token, used to obtain a new access_token, lives for 2 weeks.

A brief description of the manner in which the tokens are used follows...

SugarCRM Troubleshooting: Still Loading...

Some months back I posted an article that spoke to a problem that caused Sugar to get stuck at a "Loading..." message. As discussed in that post, the culprit usually revolves around the rewrite module or its configuration.

In recent months, however, some Sugar customers reported puzzling situations where the Loading... problem could not be resolved through corrections to the rewrite configuration. Adding to the bewilderment was the point that the configuration of these environments seemed to be in order. For example, PHP versions and configuration seemed to line up with recommendations provided by SugarCRM. 

Eventually, after much effort from several folks at SugarCRM, the source of the problem was finally revealed. It turns out that late last year, the JSMin PHP extension was modified and updated to version 2.0.1. The changes in this new version of JSMin introduced an incompatibility for Sugar, which in turn causes the Loading... problem. Note this is usually accompanied by errors in the JavaScript console.

If you are encountering the Loading... issue and have already completed the steps to verify and/or correct the potential rewrite issues, double check your version of the JSMin extension. You can check the version by taking a look at the JSMin section of the output from phpinfo().

Should you have version 2.x of JSMin installed, you can correct the problem by:

• Downgrading your version of JSMin to 1.1. Please note that if you are installing via PECL, you will need to specify the version you wish to install or it will install the latest version (2.x). To ensure it installs version 1.1, use the following command: pecl install jsmin-1.1.0

• Disable the JSMin extension. Sugar does not require this extension for proper operation. The JSMin extension is used in a limited capacity as it helps expedite the process that minifies JavaScript code used throughout Sugar. Its absence will not cause that process to fail, only reduce the speed at which it completes, thus, it is perfectly safe to disable it.

Hope this helps.

SugarCRM: Bulk API Explained

Recently I have found myself in various conversations revolving around the topic of the Bulk API and its capabilities. These conversations have helped highlight various misunderstandings relating to its functionality, which I believe are the result of a lack of documentation on the subject. Hopefully this post will help clarify some of the more common themes.

To begin with, let us discuss what the Bulk API is not.

It is not a separate interface to Sugar. It is merely another endpoint of the existing REST v10 API. In short, the moniker Bulk API is a misnomer, as the term is just referring to a component of the REST v10 API, not an alternative to it.

Quite regularly I also hear of attempts to use the Bulk API to perform mass insertions of data. For example, migrating hundreds of thousands of records from another system and into Sugar. While the Bulk API can help expedite the process, it is not specifically designed for that task and its benefits for that type of work are limited. 

What is the purpose of the Bulk API endpoint?

The Bulk API endpoint aims at helping developers reduce the quantity of web requests sent to the REST v10 API. By reducing the number of requests necessary to complete a programmatic task, the time to completion can also be reduced.

The following example helps demonstrate its value quite well.

SugarCRM Quick Hit: SugarBean and JavaScript

Sometimes in the course of customization work we may be performing within Sugar we may have a need to load one or more records from a module. 

For example, we may need to load an entry from the Calls module. Or, as demonstrated in a recent example I posted, we may need a set of records from the Leads module.

The Sidecar framework in Sugar 7 makes this process quite easy via the app.data namespace. Here are a few examples that should help.

To load a single record from a given module:

var myRecord = app.data.createBean('Leads', {id:<record_id>});
myRecord.fetch();

Loading multiple records from a given module:

var myRecords = app.data.createBeanCollection('Leads');
myRecords.fetch();

Note the use of the fetch() method in both examples. It is used to actually instruct Backbone to execute the necessary calls to request the desired data via the Sugar REST v10 API. 

In the case of the first example, the JSON object passed as the second parameter ({id:<record_id>}) allows us to specify the ID of the record we wish to retrieve. However, in the second example, you will notice we are not specifying a similar property. 

When executed, the second example retrieves the first page of the same set of records that would otherwise be displayed to the current user should the user instead access the ListView for the same module. By default, that would be the first 20 records accessible to the current user, ordered by date_modified, descending.

But what if we wanted greater control over which records or fields are retrieved? That brings us back to the fetch() method mentioned earlier.

The fetch() method allows us to specify a number of additional parameters, including: filter, fields, success and others.

Here is an example of where the fields and filter parameters are defined and used to retrieve only the id and status fields for those leads created within the current month.

var leads = app.data.createBeanCollection('Leads');
leads.fetch({
        fields: ['id', 'status'],
        filter:[{'date_entered':{'$dateRange':'this_month'}}],
    });

Hope that helps!

SugarCRM Customization: Lead Conversion Stats Chart

It is always cool to find new ways to visualize data stored in SugarCRM.

For this customization, I focused on exposing data representing the number of Leads converted versus those that were not converted within the given month, commonly referred to as the Lead Conversion Rate. To make this data more readily apparent, I created a custom pie chart, which doubles in helping us see the power of the SugarCRM 7.x framework.

Although the conversion percentages are not demonstrated, I hope it will help you gather some further insights into your Sugar data or build more elaborate charts.

Here is an image based on some sample data:

Feel free to peruse or use the code, found here:

https://gist.github.com/elchele/2ab378da3b3b4cae22c3

To apply the chart, be sure to follow the instructions given at the bottom of the page at the above link.

SugarCRM Reference: Dissecting the NavBar

A common challenge for Sugar developers is often times less about the development of a solution, but instead, finding the starting point to apply it. For example, should we wish to customize something in the subpanels area, do we modify the subpanels layout controller, or the subpanel-list metadata? Similar questions apply to various areas of Sugar.

To alleviate this problem, it is helpful to dissect the various sections that make up a typical page in Sugar and determine which components are used to construct it. In this case, we will focus on the NavBar, found at the top of Sugar.

The diagram below indicates the names of the various Sidecar components used to make the NavBar:

NavBar Analysis

I hope it helps and stay tuned for future diagrams!

SugarCRM Customization: Custom Upload Directories

I always enjoy finding gems within SugarCRM, little known features that end up opening up a number of doors for further and even more intriguing customizations. A while back, a discussion with a colleague led me to one such discovery.

Those of you making use of documents and other file attachments in Sugar might find it of help to know that said attachments can be stored on Amazon S3 storage in place of the default upload directory. Support for S3 is built-in functionality and can be enabled via some configuration changes.

More intriguing, however, is the fact that this functionality leverages one of those gems I reference and you can take advantage of it as well. The ability to upload attachments to an S3 bucket is accomplished through the extension of the UploadStream class found in Sugar 6.6 and higher.

But if S3 support does not appeal to you, perhaps you might be interested in a slightly different extension of UploadStream.

One of the main problems of the default behavior for attachments in Sugar is that all files end up in the same folder (i.e. <sugar>/upload). Over time, the contents of this folder can become rather difficult to manage. To simplify things, we could organize its contents based on the creation date of the database record associated with the file. For example, MyFile.doc, added to Sugar on April 27, 2015 would be stored as <sugar>/upload/2015/04/27/<SomeGUID>. This approach makes the functionality much more scalable and is easily implemented.

Here is the code that allows us to do that: