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 6 to whatever appropriate value you have, and you can change MONTH to a variety of other values, for me the two I used today was MONTH and DAY.

I’m using this on a SugarCRM MySQL database with a custom module of Position (the actual table name is t_position).

2013-11-04 Link Roundup


photo credit: Rak Tia via photopin cc

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:


small_4956707000If you use a Decimal field type you can remove the floor() portion of the formula so that it doesn’t round it down. This means you will see just how old they are to a decimal point. Personally I tend to prefer just knowing that they are 21 or 42 rather than 21.45 or 42.01.

photo credit: Fouquier ॐ via photopin cc

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:


However today I need to make this particular field visible if the field it is dependant on is one of a few values. Thusly a different ‘dependant formula is required:


Hat tip to Anton in Sugar Support for pointing to the ‘isInList()‘ instead of trying to build something with nested ifElse()’s.

Importing Data Into SugarCRM

Working with SugarCRM (Pro, OnDemand) last week and discovered a wonderful thing!

SugarCRM has been designed to import record ID numbers from other systems. So long as the id numbers you’re importing are less than 36 characters, and are globally unique (not just unique to the entity, i.e. contacts, notes, accounts) you’re in business, you can simply import the legacy record ID into the Sugar ID field (see below on how to make it globally unique).

This is a great thing. Before knowing this, and with other systems, you need to create a custom field in each entity to store the legacy ID number, import the data into that entity, then export it with the new system’s ID number. You can then match up this new ID number with other data so that the relationships get maintained. This is discovery is for me a wonderful thing, others already know this as it was designed this way, but this will save me a lot of work.

One of the tips given to me by one of the SugarCRM support people was that if the ID isn’t globally unique, then simply suffix it (or prefix it) with something unique for that entity. For example, if Accounts and Contacts both have an ID of “ABCD-1234” you could suffix all the Accounts record ID’s with “_Acct” and the Contact record ID’s with “_Cont”. This effectively makes them globally unique. Do the same for any ID’s in other entities, for example, in the Notes table, any Contact ID’s would get suffixed the same way with “_Cont”.

Account Records Before:

  • ID, AccountName:
  • ABCD-1234, Doe Exports
  • ABCD-4321, Joes Toes

Contact Records Before:

  • ID, ContactName
  • ABCD-1234, Jane Doe
  • ABCD-4321, Joey Blowy

As you can see above, the ID’s are unique to the entity but not globally unique (and we do want them globally unique). By suffixing the ID’s we can make them globally unique as you see below:

Account Records After:

  • ID, AccountName
  • ABCD-1234_Acct, Doe Exports
  • ABCD-4321_Acct, Joes Toes

Contact Records After:

  • ID, ContactName
  • ABCD-1234_Cont, Jane Doe
  • ABCD-4321_Cont, Joey Blowy

This really does make it much easier import lots of data and keep the relationships between pieces of information. For example, Notes would then look like this:

Notes after:

  • ID, RelatedContact, RelatedAccount, Note
  • 123_Note, ABCD-1234_Cont, ABCD-1234_Acct, “Spoke with Jane re recent purchase.”
  • 456_Note, ABCD-4321_Cont, ABCD-4321_Acct, “Joey rang, wants to double his order.”

If only it were this easy importing into other CRM’s. Let me know if you have any questions about importing data into SugarCRM.

Getting ifttt to add to RTM

If that headline made sense to you, then you’re in the right place :)

ifttt recipe for adding a task to Remember The Milk
The ifttt recipe

The problem I had is that ifttt doesn’t by default integrate with RTM.

So I’ve come up with a way that creates a new RTM task for me whenever there is a new item in a particular RSS feed.

We create a ifttt recipe that:

  1. When there is a new item in the specified RSS feed.
  2. Will send me an email with a unique subject line.
  3. Our email program (GMail in my case) has a filter set to look for that unique subject line and then forward that email to our special RTM email address (see this page for help with the RTM email stuff).

I’ve used [RTMwithSOMEuniqueLETTERS] as my subject line so that nothing will accidentally happen.

RTM (Remember The Milk) and ifttt have a bunch of features that I wish the Sage ACT! or SugarCRM task lists would emulate (or buy), it’s this sort of automation that can become very handy.