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 […]
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 […]
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
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.
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 ﬁles, one containing the application ﬁles, one containing the SQL database. […]
Find duplicate contact records, by contact name, in SugarCRM using MySQL.
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.
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 = […]
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 […]
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())
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 […]
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 […]
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
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 […]
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 […]