SugarCRM - Update Calculated Fields Nightly

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.

Alternate Strategies

An alternative to using custom fields would be to create a nightly task which just updates a fields. I prefer this approach for the following reasons…

1. I can add new calculated fields using Studio and not have to update code at all on the server. You can also have multiple calculated fields per module.
2. Since Sugar Logic is also applied in Javascript fields, if a date field is changed manually (instead of via a workflow rule), the calculated field updates in realtime while in the edit view and will be accurate on the detail view as well. Otherwise you’d need to create a on save logic hook to do that if nightly synchronization wasn’t enough.

Other alternative is to not calculate the days and just have a date field and design reports to only show records that are within a certain time range. This was the initial approach I used to take, I find that this makes creating reports a LOT harder. Also, if you want to calculate something like the average time an order was fulfilled you can’t do that with Sugar’s Reporting at this time. Plus it’s just easier in my opinion to look at an integer such as 14 vs. 2013-02-18. It also simplifies custom formatting of rows in my dashlets. It’s fairly easy to write some jquery code to highlight based on a number… having to parse dates and calculate them would be slower and more of a pain.