When SugarCRM Case Closed

Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed.
Thus, the query below will return the date the case was transitioned to ‘Closed’ provided the cases.status field is being audited.

  CONVERT_TZ(cases_audit.date_created,'+00:00','+10:00') as 'date_closed',
  accounts.name as 'account',
  cases.name as 'case_id'
  join cases on cases.id = cases_audit.parent_id
  join accounts on accounts.id = cases.account_id
  cases_audit.field_name = "status"
  and cases_audit.after_value_string = "Closed"
order by
  accounts.name asc,
  cases_audit.date_created desc;

Note, you might get more then one row returned per case if the case was re-opened, also my code above assumes a timezone of UTC+10

I happen to be in the UTC+10 timezone :).

Tracking down long running queries in SQL

I’ve had a client whose Linux Server (CentOS) gradually gets slower and slower, then falls over.

By logging into the MySQL command line, we were able to see that there were some long running queries that never end.

show processlist;


| Id | User | Host | db | Command | Time | State | Info |
| 2322386 | ssgadmin | | sugarcrm_ssg | Query | 2202 | Sending data | SELECT IFNULL(hr_humanresources.last_name,’’) hr_humanresources_last_name ,l3_cstm.verification_c l3 |

The key part here is that hr_humanresources_last_name has an underscore between the table name when it should be a period i.e. hr_humanresources.last_name. Knowing the table name was enough to tell me it was caused by something to do with SugarCRM that’s running on that server. But we don’t yet know what action caused these queries.

Knowing it was 2202 seconds ago from the time we ran the query we are able to pinpoint a time the action occurred. Looking through the SugarCRM log files did turn up that the error was often caused by a single user, but didn’t show up anything to help us figure out exactly what was the causal issue. Talking with that user, getting them to do what they’d done at that time didn’t turn up anything, the error wasn’t reproducing on demand.

So I turned to looking in the tracker table in the SugarCRM MySQL database for entries around the time of the error. Turns out that there is a Dashlet being loaded, that Dashlet uses a Report.

Each time I load the Dashlet or run the Report I get a corresponding long running query turn up. We’ve found our culprit, and we’re now able to recreate the report.

Allow a Checkbox to be Mass Update-able in SugarCRM

Here’s how to enable a checkbox to be updated enmasse.

First create a new extvardef.php file containing the following:

Then put that file into at the following location


Of course, you’d want to check any existing files in the folder to make sure none of them already deal with that field.

MySQL – Counting how many rows have a particular value

Working on a SugarCRM dashboard today, and I needed to count how many records had one value and how many records had a second value. I then wanted to know what percentage the first value was of the total. So there’s a bit of MySQL code that helps make this easy to do.

  SUM(IF(field_name = 'value_one',1,0)) AS 'Value One',
  SUM(IF(field_name = 'value_two',1,0)) AS 'Value Two'
FROM table_name

What this does is counts up how many times value_one and value_two appear in field_name in the table table_name.

You can then take this a step further, if you wanted to, and I did…

you can use a line like this within your query:

This gives you the percentage that value_one is of (value_one + value_two).

Hat tip to this stackoverflow answer by eisberg.

Edit: 2014-05-26-21h53m put the final code snippet into a ‘Gist’ on GitHub.

Sugar Logic bug with a workaround for date fields

A client is recording the expiry dates of staff blue cards in their SugarCRM database.

So I used the following sugar logic to show if the blue card has expired or not:

However, the problem here is that when a user of SugarCRM is editing the record, there is a known bug that means it will display “EXPIRED” regardless of if the date is in the past or future.

The workaround for this bug (thanks Anthony) is to wrap the date field in a date(toString($datefield)) bit of code, this transform the original sugar logic into this:


It is essentially force-casting the field to a date field for the javascript-side while passing the formula validator in Studio by first casting the date-type field to a string type.

Which works a treat.

How to convert UTC time to current timezone in MySQL

Using SugarCRM (this client is using Enterprise 6.5.15) and we want the last two hundred items in the tracker table. The problem is that the `tracker`.`date_modified` field is in UTC time, not the local time zone, which is +10:00.

The solution is to use the CONVERT_TZ MySQL command, as shown in the example below. It will convert between timezones for you.

SELECT `users`.`user_name` AS "User",
`tracker`.`module_name` AS "Module",
`tracker`.`item_summary` AS "Summary",
`tracker`.`action` AS "Action",
CONVERT_TZ(`tracker`.`date_modified`,'+00:00','+10:00') AS "Modified"
FROM `users`
INNER JOIN `tracker` ON
`users`.`id` = `tracker`.`user_id`
WHERE `tracker`.`deleted` = 0
ORDER BY `tracker`.`date_modified` DESC

Sugar logic to calculate days an opportunity has been open

We want to find out how many days an Opportunity in SugarCRM has been open, using a calculated custom integer field in SugarCRM Studio. I’m assuming you’ve already created a new custom integer field, likely called something similar to “Days open”.

The sugar logic to calculate the difference between two dates is this:


and the sugar logic to do an if then else is:


The first test we’ll do is if the sales stage is “Closed Lost” or “Closed Won”, if it’s true, we’ll calculate the days between the create date and the expected close date. If the sales stage is something other than “Closed Lost” or “Closed Won” then we’ll calculate the days between the create date and today.

This makes our pseudo sugar logic formula look like this:

		equal($sales_stage,“Closed Lost”),
		equal($sales_stage,”Closed Won”)

Which, when we update it with the real sugar logic parts becomes this:

		equal($sales_stage,"Closed Lost"),
		equal($sales_stage,"Closed Won")

Now, removing all the spaces we get this:

ifElse(or(equal($sales_stage,"Closed Lost"),equal($sales_stage,"Closed Won")),abs(subtract(daysUntil($date_entered),daysUntil($date_closed))),abs(subtract(daysUntil($date_entered),daysUntil(today()))))

This post was written in response to a question on LinkedIn.

MySQL snippet for past and next 6 Months

Spent some time today working with DATEDIFF, DATE_ADD and all kinds of horrid looking code before realising that to do a MySQL query to get records where the date is in the past or the next x months all I need is the following.

WHERE t_position_cstm.review_date_c <= DATE_ADD(DATE(now()), INTERVAL 6 MONTH)

You can change the 6 to whatever appropriate value you have, and you can change MONTH to a variety of other values, for me the two I used today was MONTH and DAY.

I’m using this on a SugarCRM MySQL database with a custom module of Position (the actual table name is t_position).

2013-11-04 Link Roundup


photo credit: Rak Tia via photopin cc

SugarCRM how to calculate a persons age

In SugarCRM we oft record a persons birth date (in the field birthdate). Sometimes it’s useful to know how old this person is. So we create a new field age_c (note: this can be of field type Integer or Decimal) and then using this formula we can see how old they are:


small_4956707000If you use a Decimal field type you can remove the floor() portion of the formula so that it doesn’t round it down. This means you will see just how old they are to a decimal point. Personally I tend to prefer just knowing that they are 21 or 42 rather than 21.45 or 42.01.

photo credit: Fouquier ॐ via photopin cc

SugarCRM field visibility dependant on multiple values

I’ve often got cause to make fields in SugarCRM (Professional and Corporate editions) to only appear based on the value of another field. We can do this easily using SugarLogic in Studio using this ‘dependant’ formula:


However today I need to make this particular field visible if the field it is dependant on is one of a few values. Thusly a different ‘dependant formula is required:


Hat tip to Anton in Sugar Support for pointing to the ‘isInList()‘ instead of trying to build something with nested ifElse()’s.