Ben Hamilton

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. select CONVERT_TZ(cases_audit.date_created,’+00:00′,’+10:00′) as ‘date_closed’, accounts.name as ‘account’, cases.case_number, cases.name […]

Posted in SQL, SugarCRM | Tagged , | Comments Off on When SugarCRM Case Closed

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; returns | Id | User | Host | db | Command | Time | State […]

Posted in *nix, SQL, SugarCRM | Tagged , | Comments Off on Tracking down long running queries in SQL

Location of the SugarCRM log file

By default it’s in the same base folder as the SugarCRM application files. But on occasion we’ll move it. Quickly find it’s location by looking in the config.php file. The two relevant lines are: ‘log_dir’ => ‘/var/log/sugarcrm/’, ‘log_file’ => ‘sugarcrm.log’, If you’re at the command line, then this makes it easy: grep ‘log_dir’ config.php

Posted in General | Tagged , , | Comments Off on Location of the SugarCRM log file

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 /var/www/html/custom/Extension/modules/Module_Name/Ext/Vardefs/ Of course, you’d want to check any existing files in the folder to make sure none of them already deal with that field.

Posted in How to, SugarCRM | Tagged , , , | Comments Off on Allow a Checkbox to be Mass Update-able in SugarCRM

How to backup SugarCRM from the command line

From the linux command line, these commands let you backup and restore a SugarCRM database. Update 2016-04-26: Just saw this SugarCRM KB Providing a Backup Without Sensitive Data which is really useful. Thanks Jared. Firstly, for a proper back up of SugarCRM you’ll need two files, one containing the application files, one containing the SQL database. […]

Posted in *nix, SQL | Tagged , , , , | Comments Off on How to backup SugarCRM from the command line

Find Duplicate Contacts in SugarCRM

Find duplicate contact records, by contact name, in SugarCRM using MySQL.

Posted in SQL | Tagged , , | Comments Off on Find Duplicate Contacts in SugarCRM

SQL Query ORDER BY last_name, then first_name

This snippet of MySQL code shows how to concatenate two fields into one column, also shows how to reverse the concatenation and order by last name then first name.

Posted in SQL | Tagged , | Comments Off on SQL Query ORDER BY last_name, then first_name

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. SELECT SUM(IF(field_name = […]

Posted in SugarCRM | Tagged , , , | Comments Off on MySQL – Counting how many rows have a particular value

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: ifElse(isBefore(today(),$bluecard_expiry_c),”Current”,”EXPIRED”) However, the problem here is that when a user of SugarCRM is editing the record, there is a known bug that means […]

Posted in How to, SugarCRM | Tagged , , | Comments Off on Sugar Logic bug with a workaround for date fields

How to change default of Inbound to Outbound for scheduling calls in SugarCRM

When scheduling a call, the default selection for the ‘Direction’ field is ‘Inbound’. Your team primarily performs outbound calls so you would like the default value to be changed.

Posted in SugarCRM | Tagged , , , | Comments Off on How to change default of Inbound to Outbound for scheduling calls in SugarCRM

SugarCRM screen formatting issue

Angel Magaña explains why, and how to fix SugarCRM screen formatting issue. Summary: it’s the cache folder permissions.

Posted in How to | Tagged , | Comments Off on SugarCRM screen formatting issue

Sql snippet to show just opportunities from current month

I’m needing to do a sql query to show just the opportunities from the current month, the sql snippet for the WHERE clause to do this is the following: WHERE MONTH(opportunities.date_closed) = MONTH(NOW()) and YEAR(opportunities.date_closed) = Year(now())

Posted in SQL | Tagged , , | Comments Off on Sql snippet to show just opportunities from current month

Create demo data for SugarCRM databases

Generate records with Tidbit for various Sugar modules.

Posted in SugarCRM | Tagged , | Comments Off on Create demo data for SugarCRM databases

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 […]

Posted in SugarCRM | Tagged , , , | Comments Off on How to convert UTC time to current timezone in MySQL

What to expect when upgrading from SugarCRM 6.5.x to 7.1.5

What to expect when upgrading from SugarCRM 6.5.x to 7.1.5.

Posted in SugarCRM | Tagged , | Comments Off on What to expect when upgrading from SugarCRM 6.5.x to 7.1.5

Remove thousand separator in Integers in SugarCRM

We want to record a ‘Year’ value on a record in SugarCRM, but we don’t have a day and month to do with it, you can’t use a field that is of a ‘date’ type as that requires a full date (year, month, day). You can use an ‘Integer’ field type tho. The downside is […]

Posted in How to | Tagged , , | Comments Off on Remove thousand separator in Integers in SugarCRM

Find duplicate records in SugarCRM

Find duplicate records in SugarCRM version 6.5 and version 7.1.

Posted in How to | Tagged , | Comments Off on Find duplicate records in SugarCRM

Using a ternary expression to check if a value is null

Simple test to see if a field is null. I’m using this in Talend to determine if I copy the value into a new note field. You could use a length test, but if the field is a date, then you need to do it this way. row1.field1 != null ? do_this_if_true : do_this_if_false

Posted in How to | Tagged , , | Comments Off on Using a ternary expression to check if a value is null

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: abs(subtract(daysUntil($date1),daysUntil($date2))) and […]

Posted in General, How to, SugarCRM | Tagged , , | Comments Off on Sugar logic to calculate days an opportunity has been open

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 […]

Posted in SugarCRM | Tagged , , | Comments Off on MySQL snippet for past and next 6 Months