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:

SugarCRM 101: Security

The topic of data visibility and security is an important one, especially within the context of systems such as SugarCRM (Sugar) which are used to store information about our customers/clients.

Within Sugar itself you will find a set of sophisticated security features that permit administrators to control which users can access or see certain records, and secondly, given the ability to see a record, which actions can be performed on said record. These rules apply across all modules within Sugar, including custom modules, and effectively represent the two primary levels of security found in the application.

Often times, the features used to control visibility are confused with those used to control the actions the user can take on a record they can see. It is important to note that each security level is handled separately. Below we will look at each level and the related features within Sugar that allow us to define our security policy.

Visibility:

As data is entered into Sugar, two assumptions are made about an entry. These assumptions in turn help us control who will be able to see the record.

The first assumption is that there is a record owner, applied via the Assigned To field found on all entries. The second assumption is that there is one or more team assigned to the record, defined via the Teams field.

By default, Sugar will set the Assigned To value to match the name of the currently logged in user at the time a record is entered. This in turn translates to the point that the user that creates a record will, by default, be able to see the record. Of course, that assumes the user does not change the Assigned To value, which they are free to do.

The second assumption relates to teams. Out-of-box behavior in Sugar is such that when a record is created, it is automatically assigned to at least one team. That team is called Global and includes all of the users in the system. Thus, if we assume the team assignment is not changed, all users would effectively have access to all new records.

Much like with the Assigned To value, a user is free to remove the Global team or assign additional teams to a record. This allows us to extend or restrict visibility of a record. For example, removing the Global team from a record would reduce visibility to only those users that are members of the team(s) that do remain assigned to the record.

Should you wish to change the default teams that are assigned to a record at creation time, you may do so by way of a user profile setting. More information on User Management can be found here: http://support.sugarcrm.com/02_Documentation/01_Sugar_Editions/01_Sugar_Ultimate/Sugar_Ultimate_7.2/Administration_Guide/03_Users/

Information on the creation and general management of Teams is found here: http://support.sugarcrm.com/02_Documentation/01_Sugar_Editions/04_Sugar_Professional/Sugar_Professional_7.5/Administration_Guide/031_Team_Management/

IMPORTANT: Do not delete the Global team as it may cause unexpected behavior.

Actions:

Now that we understand the manner in which we can control which users can see a given record, let us discuss the manner in which we control the actions they can perform on a record.

The actions a user can take upon a record they can see are defined via Security Roles. Security Roles are rules that can be used to restrict actions on a record or on a field. Once defined, an administrator applies said rules to a user by assigning one or more Security Roles to the user. 

IMPORTANT: Sugar does not automatically assign any Security Roles to any of the users. In other words, user actions on a record are not restricted in any way by default. As an administrator, you should take immediate action to define the appropriate Security Roles and Teams in order to enforce your data security policy before allowing users to access the system.

Through the use of Security Roles, an administrator can control which users can perform a variety of actions, including deleting or editing records. If you wish to apply more granular restrictions, Security Roles further permit field-level restrictions. Lastly, administrators can also use Security Roles to control which modules can be accessed by the users.

Detailed information on Role Management can be found here: https://support.sugarcrm.com/02_Documentation/01_Sugar_Editions/04_Sugar_Professional/Sugar_Professional_7.5/Administration_Guide/032_Role_Management/

Hopefully this helps clarify questions you may have had about the security model in Sugar.

SugarCRM Customization: Custom Blur Event

One of the ideas that I regularly try to convey when talking about the customization capabilities of the Sugar 7 framework relates to leveraging JavaScript wherever possible. This is especially true for projects where our objective is to upgrade heavily customized versions of Sugar up to version 7.x.

In the pre-7.x world, some customizations that can now be implemented natively via the Sugar 7.x framework used to require the use of SugarLogic functionality or inclusion of third party JavaScript libraries. The case of SugarLogic can be particularly problematic if one utilizes a large number of such customizations, as it might imply the execution of additional server side code. The greater the number of these type of customizations and the greater the potential to introduce performance problems. This is also true for logic hooks. For example, a hook that automatically converts the last name field to upper case upon a record being saved.

If we step back and analyze these customizations, we would likely find that some could be implemented via JavaScript. Doing so would in turn offload some of the server load to the client -- likely helping correct performance problems. The latter example of the logic hook upper casing the last name field is a good case in point. 

Suppose we wanted to convert such a customization to implement it through JavaScript instead of using a logic hook. How would we do that? 

The general idea would be to use a standard onBlur() event, tied to the last_name field of a module such as Contacts and then automatically updating the content of that same field when focus is lost.

As it turns out, the Sugar 7.x framework makes this a very easy task. The code for making such a customization is found below:

I hope you find it helpful!

MS-SQL Quick Hit: Listing Field Definitions

Some months back I wrote about some important performance gotchas pertaining to using SugarCRM on Microsoft SQL Server (MS-SQL). More recently I had a need to analyze a system to uncover the source of similar issues. 

While doing so, I came up with an SQL query that helped me quickly view all  the column names and their definitions, simplifying the process of identifying potentially problematic entries.

I share this query below in hopes it will help you at some point:

select c.name, t.name, ta.name, c.max_length, c.precision, c.scale, c.collation_name 

from sys.columns c

join sys.types t

on c.user_type_id = t.user_type_id

join sys.tables ta

on c.object_id = ta.object_id

Note that with some minor modifications you can get the query to list out the columns of a specific data type.