Ben Hamilton

Category Archives: SugarCRM

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

Also posted in SQL | | 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 […]

Also posted in *nix, SQL | | Tagged , | Comments Off on Tracking down long running queries in SQL

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.

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

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

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

Also posted in How to | | 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.

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

Create demo data for SugarCRM databases

Generate records with Tidbit for various Sugar modules.

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

| 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.

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

Why keeping data clean in a database is important

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’

Also posted in ACT!, SQL | | Tagged | Comments Off on Why keeping data clean in a database is important

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

Also posted in General, How to | | 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 […]

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

2013-11-04 Link Roundup

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

Also posted in General | | Tagged , , | Comments Off on 2013-11-04 Link Roundup

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: floor(divide(subtract(daysUntil(today()),daysUntil($birthdate)),365.242)) If you use […]

Also posted in How to | | Tagged , | Comments Off on SugarCRM how to calculate a persons age

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: equal($fieldname_c,”value_1″) However today I need to make this particular field visible if the field it is dependant on is one […]

Also posted in How to | | Tagged , | Comments Off on SugarCRM field visibility dependant on multiple values

Resolution of SugarCRM Not Sending Notifications

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.

| Tagged , | Comments Off on Resolution of SugarCRM Not Sending Notifications