Recalculating Calculated Fields en mass ( update )

bsoremsugar —  August 22, 2013 — 6 Comments

One of the slightlly annoying parts of Sugar Logic for many users is that Sugar Logic calculations are only executed on record save. So if you have calculations based off of data outside the record ( such as a rollup value ) or if you want to populate a calculated field on a record that was last touched before the calculation was added, you’ll need to save the record.

There’s two ways to do this. If you want to have something a bit more automated and don’t mind writing some code, you could write a script like this to go thru all the records…

<?php

$bean = BeanFactory::newBean('BeanName');
foreach ( $bean->get_full_list() as $record ) {
	$focus = BeanFactory::getBean('BeanName',$record->id);
	$focus->save();
}

But what if you don’t want to write a script for this?

I came across this blog post from the folks at EnableIT the other day, which talked about a nice way to use the Mass Update functionality to do the same thing. Here’s how they said to do it…

Go to the relevant module which contains the calculated field you are wanting to update. Using the Search panel (either Basic or Advanced) filters, search for the records you want to update.

Use the Select All option on the Seletion options dropdown and then select Mass Update from the More Actions menu.

You will be presented with the Mass Update panel for that module. Without selecting any of the available fields to update, simply click the Update button.
You will be asked whether you really want to update the selected records. Click Yes.

What this will do is update (in essence, Edit and Save) each of the records, causing the calculated fields to populate.

It should be noted that if you have other fields in the system which have calculations which call on these fields which have just updated, they will automatically repopulate without the need to mass update in the other module.

Thanks Mike for this great tip!

6 responses to Recalculating Calculated Fields en mass ( update )

  1. 

    That’s a great tip, but folks trying to update a module with 10,000 records will probably find performance issues. Or more likely, it-don’t-work issues and a PHP error/white screen.

    For larger-data situations like that, I’ve always gone directly to the database and wrote SQL to update the target fields in whatever way. That “fixes” the existing records, and of course Sugar Logic handles it from that point forward.

    • 

      @matthewpoer:disqus agreed that you shouldn’t do this presently for updating large datasets; using an offline script is a better choice. We are working internally on fixing Mass Update in a future version to improve this so that these jobs can run in the background asynchronously.

      I would caution against going right against the bare DB unless you have a great reason, just because you will not engage any Sugar Logic, Logic Hooks, or Workflows you may have as a part of the update.

      • 

        True dat, I’d love to see Mass Update use that Job Scheduler Queue or something similar int he future, update 500 records at a time every 15 minutes. Would be tricky to convey that to the user as he does the update, though.

    • 

      You could use a tool like Talend to ‘touch’ all module records via the REST/SOAP interface. The design for this would be quite trivial and could run in the background.

  2. 
    Cédric Mourizard August 22, 2013 at 2:31 pm

    You can also use the good page produce by Blake Robertson @blak3r : https://github.com/blak3r/sugarcrm-update-calculated-fields

    He keeps the focus on some specific attributes like update_date_modified, update_modified_by or tracker_visibility which are a big plus in this kind of mass action

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s