Category Archives: SugarCRM
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 […]
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.
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 […]
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 […]
Every user of a database is responsible for ‘gardening’ the database, removing the ‘weeds’, watering and maintaining it. Otherwise this sort of thing occurs. Dad gets OfficeMax mail addressed ‘Daughter Killed In Car Crash’
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 […]
A great metaphor, using cups of water, that explains Google, Apple, Microsoft, Amazon and the dance between them. There is no private cloud, no public cloud, just hybrid, and 68% of customers will change vendor. Good explanation of a ‘partitioned’ approach and an ‘iterative’ approach using dice to help explain the concepts. Read this to understand how to […]
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: floor(divide(subtract(daysUntil(today()),daysUntil($birthdate)),365.242)) If you use […]
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: equal($fieldname_c,”value_1″) However today I need to make this particular field visible if the field it is dependant on is one […]
One of my clients had their SugarCRM notifications stop working. After a lot of testing, it turns out that if I set the FROM name to be the same as the FROM address it started working again.