Script to update calculated fields

Hi everyone,

Can anyone share a script to update calculated fields for a particular module please?

Thank you in advance

  • 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
    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? Kissing heart

  • 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  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

    SugarCRM | Principal Developer Advocate

  • 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
    It would be awesome if you got this one sorted, many will vote for you to be next CEO for SugarCRM Blush

  • Hi ,

    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 Slight smile..

    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.

    SugarCRM | Principal Developer Advocate

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