Script to update calculated fields

If you look at the List View, there is an option for "Recalculate Values".

That is part of the massupdate actions and the corresponding code can be found in 

clients/base/views/massupdate/massupdate.js

The action executes the 

updateCalcFields: function 

which as you can see from the code is just a mass save:

    /**
     * Called to allow admins to resave records and update thier calculated fields.
     */
    updateCalcFields: function() {
        this.hideAll();
        this.save(true);
    },

In other words, to trigger a calculated field to update all you need to do is save the record.

The down side is all your Modified by and Modified Dates will change on every one of those records, which is not necessarily something one would want to do when modified date is important for reporting purposes.

But it is the cleanest and safest way to do this.

FrancescaS

Thanks Francesca Shiekh
Indeed I forgot and should have mentioned the desire is to not change the modified by or date.

I remember somebody on the old community mentioning this was possible and shared insights for the method and maybe even the script (was it Blake?) but can't find it.

Have a customer with more than 300k of records in one module, so ideally this script could then be run in background or only run overnight on a scheduler.

Ideally the Sugar product team could look into adding this much needed feature for the greater good?

Is it a single calculated field or do you need to update all calculated fields? In any case you could write a scheduler that executes the data change on the database without updating the modified by date. But as soon as you going to work with beans the modified date will be changed.

Come to think of it, back in v6.x it was possible to set 

   $bean->update_date_modified = false;

   $bean->update_modified_by = false;

before saving the bean to preserve the modified by and modified date.

I think that still works. You would need to test it.

In that case you could add a checkbox to track which records were reprocessed.

Then write a nightly scheduler to retrieve the beans for each of the non processed records via SugarQuery (excluding those where your checkbox tracking field is set).
For each bean found set those two variables above to keep the modified date/modified by from being modified and set the tracking checkbox, then save.
The save action would perform the calculation, the checkbox would track that you processed that record so you can excluded from the rest round in your SugarQuery.

Once you are done reprocessing them all, you can remove the checkbox field.

The other option is as Jeroen Somhorst suggested, instead of using the bean, reproduce the formula's calculations in code and save the results using a SQL statement which does not trigger any additional logic.

Francesca

Hi Tony,

Let me take it up with engineering and see where we are with this request.

Keep you posted.

rafa

Contact Faye. They have a recalculation Module Loadable Package. You configure the module, how often to recalculate, and whether or not to update the date_modified and modified_user_id

Hi Rafael Fernandes
It would be awesome if you got this one sorted, many will vote for you to be next CEO for SugarCRM

Hi Jeff Bickart,

is this package "free" ?

Best regards,

Fred

Hey Tony,

Thanks but no thanks, our CEO is doing an amazing job, I just don't fit there ..

But I did get a feedback from engineering about this.

They've done some research and tried few approaches already, however, this is a complex and expensive operation for our Bean save.

We as product team, have to think of all possibilities and ensure quality code for you all to deliver and do your own thing.

Engineering is considering a GA solution but is not for near-term.

For now, discussing internally, leveraging a custom solution by updating the database directly through scheduler and SugarQuery is your best bet at the moment.

Hope this helps.

no, it is not free. I do not know the cost