Details about this thread
  • State Not Answered
  • Replies 5 replies
  • Subscribers 293 subscribers
  • Views 3550 views
  • Users 0 members are here
Related content from around SugarClub

Best Practice for Updating Calculated Field Values that Contain Related Data

Chad Hutchins
What are the recommended best practices for initializing/updating calculated field values that include a related field?

When you first create a calculated field, until you edit/save a record, the calculated field values will not be populated. Here are the methods I know:

1) Use the "Mass Update" tool from any list view. For example, go to Contacts, Select All Contacts, then run a "Mass Update" on any field. This will trigger each record to be edited/saved and will update the calculated field record. You can "Mass Update" a field that is not being used on the module or you can create a custom field just for this. 

Problem: If you have lots of records in a Module you will run into the 1000 record list limit or the mass update will time out. 

2) Write a custom script that will programmatically go through each of your records using the SugarBean, and run a save. It also looks like there is a updateCalculatedFields method and updateRelatedCalcFields method in SugarBean that might be better to use than a full-save. 

Problem: Requires someone with Sugar coding experience to create a script. If you're using On-demand, the customization would need to be packaged up and installable. You may have unwanted logic hooks that are run.

Are there any other ways to do this that is reliable and can be executed by a Sugar admin user that does not require custom code?
Parents
  • 0
    if you have direct access to the database, you could set your calculated field with SQL.  This is my preferred method when I don't want Logic Hooks to fire.

    if you have an ETL tool, you could update the records from outside of Sugar.

    I've never tried it, but the Excel plugin might be able to trigger the calculation?
  • 0 in reply to Justin Kuehlthau
    Hi Chad,

    I have two more suggestions for you:

    One, similar to your #1, is to export (ideally through Reports) a list of IDs and other required fields (e.g. Name if you're in the Accounts module). Once you have the export file, you can use Import & Update, which will trigger the necessary save on these records to cause the calculation to run. The import function will batch the import into groups of 1000, unlike Mass Update which only allows 1000 at once. 

    Another option is to change the max_record_fetch_size which controls the selection of 1000 records. Add $sugar_config['max_record_fetch_size'] = '####'; to the config_override.php, changing #### to however many records you need to select. However, this can cause performance issues on the instance in question and is also not able to be modified for On-Demand. 

    I hope these help!
    Alex
  • 0 in reply to Alex Nassi

    Hi Alex,

    Can you clarify where the variable you suggest is documented please, as I can't find it?

    Many thanks

    .

    CRM Business Consultant

Reply Children
No Data