SugarCRM – Update Calculated Fields Nightly

sugarcrmdevelopers —  February 22, 2013

SugarCRM has some powerful calculated fields called Sugar Logic. One limitation though is they are only calculated when a field is saved. I wanted a simple way to display the number of days since X occurred.

So, I created a very simple script which can be run from the Scheduler every night. It basically just iterates through all the records for a user configurable module type and forces the calculation to update. Best of all it does this without changing the modified date, modified by or creating a tracker entry.

This script could also be used one time to just initially seed all the calculated values for a module. The suggestion from the admin guide is to do a mass update but that’ll cause modified dates, etc to change.

The code is hosted on github here: https://github.com/blak3r/sugarcrm-update-calculated-fields

Potential Applications:

  1. Days since lead was last contacted.
  2. Days since order added to system.
  3. Days since a support case was entered.
  4. (There’s lots)

My original application was to keep track of how long it was taking our company to ship orders once we had all the information from a customer.

  1. I created a workflow rule such that when orderstage == Production Ready, I would set the date field “productionready_date”.
  2. I then created a calculated field called “dayssinceproduction_ready” and set the formula to something like abs(daysSince($production_ready_date))
  3. Installed the script on my github site and created a scheduler job to run at 3am every night.

We now use the field as a dashlet on our order status tab and it’s used in some reports to be able to look back over the past year and see how well the production team is doing at getting orders out and to be able to do analysis on the orders that took a long time to identify ways to speed them up.