SugarCRM 7 – Custom subpanels


This tutorial should hopefully help you to create a new subpanel under the Contacts module in Sugar using a custom link class and driven by SugarCRM 7′s new SugarQuery API.

1. Create a new link class

This should go into custom/modules/<YourModule>/YourNewLink.php and this class will act as the custom functionality that will build your link between the two records.

The argument $sugar_query is a new SugarQuery object, the details of which are documented here.  What you essentially need to do is extend this query with whatever join/filters you wish to add. This is done in the inner join I’ve specified.


Note: The inner join can get really complicated, so if you want a real working example, checkout modules/Emails/ArchivedEmailsLink.php and how the core sugar team use this. I can confirm however that this does work with custom joins.

Here is the getEmailsJoin to help you understand what you can actually produce via this custom join.

2. Add a new vardef entry for the link field.

For this example, I’m going to create the custom link on the contacts module. So this code goes in custom/Extension/modules/Contacts/Ext/Vardefs/your_field_name.php

3. Add the new link as a subpanel

This goes under custom/Extension/modules/Contacts/Ext/clients/base/layouts/subpanels/your_subpanel_name.php

4. Add the label

Under custom/Extension/modules/Contacts/Ext/Language/en_us.new_link.php

5. Quick Repair and Rebuild

That should hopefully get you started. Keep an eye on the sugarlogs while you’re debugging your queries. I also found using xdebug and SugarQueries compileSql function invaluable in figuring out what I needed to do to get a working INNER JOIN statement.

I’ve found this to be a surprisingly powerful solution, it means that if you need to show information related to a module that might be a few joins away, this allows you to create the links manually without having to create pointless related fields in-between the two.

SugarCRM 7 – Making Ajax Requests


SugarCRM has a pretty great API if you know how to poll it. Today I’m sharing two examples of where I’ve needed to poll SugarCRM’s API with some sample jQuery code.

A jQuery autocompleter

If you’re declaring an input box and wish to autocomplete it’s results based on the results from a particular module, this sample code should get you started.

Note: This leverages the existing SugarCRM API to populate this data. You can customise the filter however you want, just load up firebug/a web inspector and do a few sample requests from Sugar’s core fieldset if you need sample requests to work with.

This is really useful for leveraging existing endpoints, but what if you want to have Javascript call a custom piece of code you’ve written?

Call a custom API Endpoint via a jQuery ajax request.

Here’s a sample piece of Javascript that will grab some input fields and create your post request.

And here is the API code that would handle a post request with multiple arguments.

This would be placed in custom/modules/<YourModule>/<yourCustomApiFn.php>

SugarCRM 7 – Add a new action to a record


Odds are if you’re customising SugarCRM, you will at some point need to add functionality to the records page. This article shows you a framework for creating actions on the UI, the API entrypoints to run your code and how to link it all together using SugarCRM’s sidecar functionality.

1. Add the button

In custom/modules/<YourModule>/clients/base/views/record.php, you’ll need this to add the core button array.

In this file add in an entry at the bottom like:

Note what I’ve stored at array key 5. This is the custom button that will be rendered and the event button do_some_task will be called in the javacript file we’ll created. You can add this anywhere in the drop-down I just generally choose here.

2. Create the javascript hook to call the API

In custom/modules/<YourModule>/clients/base/views/record.js create a file like this.

 3. Build the API to act on the javascript file

In custom/modules/<YourModule>/base/api/doSomeTask.php create a file like so

Note: Make sure your DoSomeTask functon is uppercased not camel cased. I kept getting invalid argument count errors when it was camel-cased.

4. Language files for the button

Create a new language file at custom/Extension/modules/<Your_Module>/Ext/Language/en_us.doSomeTask.php with contents like

5. Quick Repair/Rebuild

This should hopefully merge your language files into your code and recompile your javascript into the front-end.

SugarCRM – Sugar powered by Salt!


Even the logos are creepily similar.

Even the logos are creepily similar.

SugarCRM can be a pain to setup, especially if you are managing many installs on different servers/environments. I’m a big fan of using salt to configure my servers. I rarely manage any servers directly anymore and generally run things through my salt master. This guide should hopefully get you up and running with a production ready setup of SugarCRM that you can re-use over and over. If you are looking for a decent primer on saltstack, this video will give you a really useful introduction to what salt can really do. I’m barely scratching the surface.


  1. Install salt on the server you with to act as the salt-master(the provisioner). DigitalOcean is great and cheap for this sort of non-mission critical task.
  2. Spin up a new server(which will be production), install/configure the salt-minion and point it at the master.
  3. Clone this repo to your salt-master or fork and create your own. You’ll need to do a bunch of customisations that you’ll want private.
  4. Do a grep -iRn ‘TODO’ sugar_salt/* and fill in all details and copy files where needed.
  5. Overwrite or merge this folder with your existing /srv/salt.
  6. Run a sudo salt-call -l debug state.highstate on the minion box you’ve setup. This will give you a ton of output and let you know if anything has gone wrong.


What this configuration gives you

  1. A set of core server essentially tools including git, vim, curl etc.
  2. Systat which I find essential for finding out what’s causing potential issues on servers.
  3. All of SugarCRMs LAMP requirements, including redis, elasticsearch, composer and apt-pinning to php 5.3.
  4. Correct php config including recommended increases to memory limit.
  5. Correct server permissions for your SugarCRM codebase.
  6. Ensures correct config files are always maintained.
  7. Ensures the correct htaccess files is always maintained.
  8. Ensures cache, upload directories exist with the correct permissions.


  1.  This configuration is tested on Ubuntu servers ONLY, specifically 12.04. If you intend to use this on something else please verify this works with a virtual machine before using on production machines. Salt plays really well with vagrant, in fact I actually manage all of my development, testing and production boxes using salt and this includes the development Vagrant boxes on my laptop. Specifically you’ll probably want to change the php.ini that might not play nice with your alternate OS of choice.
  2. One improvement that could be made is to in fact keep config.php and config_override.php in version control but have the take in environment variables that salt could maintain(or your server).
  3. This is actually a little different to how I generally configure my SugarCRM boxes with salt. This configuration is not as re-usable as it could be, so what I do is abstract everything that’s per server into their own sub-folders and everything that repeats per server into it’s own server. I haven’t shown this here for the sake of simplicity but is should be fairly straight forward.
  4. This repo was setup with SugarCRM 7 in mind. With the requirements specified here. Alter the configuration files based on the requirements of the version that’s applicable to you.

Any tips or improvements(I’m sure there’s plenty), feel free to comment.

Friday Fun – Offline 8tracks playlists using Grooveshark



A lot of people have been asking about how they might offline their favourite 8track tracks and I’ve come up with a rudimentary solution while I was doing some offline coding. On 8tracks I’ve found a great playlist series called Cold Machines created by user sowat on 8tracks and I wanted to offline my favourited tracks from these playlists as they’re great for coding to.

As there’s no direct way to do this it’s a several step process, but an pretty easy one at that.

  1. Use 8trackshelper to generate a plain-text copy of your favourite tracks, simply paste in your username.
  2. Then paste the plaintext list into groovylist’s plaintext importer.
  3. This will create a temporary playlist which you can add to an existing playlist or create a new one.
  4. Finally, download groovesquid and have it offline your newly created playlist.(Note: I could not get the Mac variant to run as it was damaged, I simply downloaded the jar file and ran ‘java -jar Groovesquid.jar’ via the command line to get it up and running)



The only minor complexity might be if you have more than 200 songs, you’ll need to split it up when pasting into groovylist but that’s pretty easy to do.

And that’s it.


Log Queries with MySQL Proxy

What is it?

Have you ever found yourself wanting live statistics of you mysql database, or a log of all the erroring queries. Well MySQL Proxy might be just what you’re looking for. Taken from the site:

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication.

Basically MySQL Proxy collects database queries and acts on them using scripts built in lua that can do some useful things. I find this handy when debugging SugarCRM as sometimes failed database queries get suppressed(especially when making API requests) and I needed a transparent way to check nothing has failed on the DB side.


This example I’m going to show you allows you to sit the proxy on 3306(instead of mysql), so you don’t need to alter any of the code point to your existing MySQL installation. If this isn’t what you want change your steps accordingly.

  • Basically in your my.cnf. Change your servers port to 3307(server only not the client). Then restart mysql.
  • Now install mysql-proxy using your regular package manager(it should even work on homebrew).
  • Then run this command:

If that default example lua script doesn’t exist, simply copy one of the files below and point proxy-lua-script to that.

Log any bad queries the database has to handle.

Log all queries with stats/warnings

Found here. Useful for spotting those slow/inefficient queries.

SugarCRM – Versioning Your Database

One issue that constantly re-occurs for me using SugarCRM is that certain knowledge is only stored in it’s database. So say you want to revert to a previous version of Sugar and wish to obtain the Studio customisations you’ve done at that version, say two days ago, well your just out of luck. This is pretty annoying considering most of your day to day changes will probably be in Studio and you have no real way to see those changes outside of Sugar’s “View History”, which leaves a lot to be desired mostly as again, it’s all in the DB. You’re effectively left to sharing a database dump between developers.

What I’ve done on my development environment is created a simple pre-commit git-hook(good explanation of those here) that essentially commits the database structure and the fields_meta_data table. This essentially means someone can get themselves up and running with a basic variant of my install pretty quickly including my studio changes, which are now all tracked in version control too. So put code resembling the below in your .git/hooks/pre-commit

Originally I had this creating a gzipped time-stamped version of sugar database dumps, but considering the SQL is small it makes much more sense to leverage the plain-text advantages git gives you, and simply track the changes through git rather than timestamped DB dumps.

Note: I only use this script on my local dev environment. This also isn’t version controlled either, so I don’t mind that my dev database password is stored in plaintext.

SugarCRM 7 – Set recordlist row colours based on row data


Colours, glorious colours!

The tutorial shows you how to do two useful things in SugarCRM 7. Firstly, how to call actions when rows get updated in SugarCRM’s recordlist and secondly, how to set information based on row data. The example I have here will essentially update the table colours based on a specific data value in each row. So say you have a drop-down with the values Red,Green,Orange and Yellow this will update the rows to the specified colour. The key call here is this.collection.on(‘data:sync:complete’), which essentially fires when the sidecar(read backbone), data sync is completed.

This code would go in custom/modules/<YourModule>/clients/base/views/recordlist/recordlist.js

One thing you might note is the setTimeout with a delay of 1 second. This is simply because the table won’t have rendered as soon as the sync has completed so a wait of a second was added to allow the browser to render the table, before iterating through the rows and setting the appropriate colours. I honestly just couldn’t find any documentation or code samples to see if an even fired when the table gets re-rendered, but I’ll update it here if I find a better solution.

Minimalist MySQL Reporting

Sometimes business folk can have crazy demands on the types of reports your system should produce and sometimes these can be really time consuming and painful to get right. Often I find the most flexible solution is to give these power-users access to tidier version of the raw data within a tool they’re comfortable playing with themselves.

To that end, Excel has a serviceable ODBC connector that costs $35 and will allow your power users to pull data in from MySQL and filter/play around to their hearts content. The problem is a) formatting the data in such a way to provide useful information to your users and b) opening up data access just enough to allow these reporting users into your system.

A) Formatting the data

The easiest way to format data in MySQL is to create a view, these are not difficult to setup. You simply need to wrap some simple syntax around the query you wish to run. So build the query that returns the ideal result set, then create the view. So for example:

One thing to note is that the ODBC connector won’t render BLOB data, so to handle this use

to convert it to a readable format.

B) Opening up data access

Step 1: By changing the bind-address from to in /etc/mysql/my.cnf this opens MySQL for remote connections. A better alternative to this could be to keep MySQL closed and tunnel the connections over SSH, this can be done with Putty on Windows or just run ssh -ND <local_port> user@remoteserver to setup a proxy.

Step 2: If you decide to open MySQL to the outside world I strongly recommend using a firewall to restrict access to the port. On ubuntu to open up the firewall to allow connections to port 3306 from your office ip just run this command.

Step 3: Now create the remote user.

Step 4: Granting access to only a view for users on your IP.

 Step 5: Simply point the ODBC Excel connector to your MySQL host and have it run the query

and the result set should populate in Excel.

One final key thing to note is that it’s a very dangerous thing to allow reporting users to hit live databases, they could really kill the system for live users. Ideally, you would do all of this on a replicated MySQL server that only allows read-only actions against it, like backups and reporting.

Manage background tasks with PHP-Resque and Supervisor

PHP-Resque, the PHP equivalent to Ruby’s really nice background process runner is a great way to execute long running PHP processes. Supervisor is a python-based process control system, essentially it’s a convenient way to manage custom system processes you need to handle. Combining the two to manage long running PHP jobs makes a lot of sense but it takes a little bit of work to get them talking.

Once you’ve gotten PHP-Resque installed on your project, you’ve added items to your queue and written the workers to handle the background tasks. You’ll need to plug PHP-Resque into supervisor. Here’s a sample config file I used to manage php resque queues within Yii projects but it should work with anything that’s using php-resque.


A few things to note.

  1. Firstly the APP_INCLUDE should include the set of classes you intent to use to handle the worker jobs. Meaning something that has a perform action that does something, as described here.
  2. You’ll notice the COUNT=1 argument being set in the resque environment. This is essential, if you fork more than one job using php-resque it’ll background both and supervisor will not be able to manage that process(and will continually try to spawn more processes till it gives up). If you wish to have multiple workers, use the numprocs argument.

And that’s it really.

Happy forking!