Calculate the total from multiple records in a related module?

Hello, I'm using Sugar Enterprise 9.1 (Cloud). I'm trying to create a calculated field that will give a cumulative total of a decimal field in a related module. 

Attendance (Customized from the Calls Module) is used to track attendance. Points are given for certain situations, no call no show is 2 points, unscheduled sick is 1 point, late is 0.5 point, etc. 

I created a custom module called Personnel with a field named Cumulative Points. Employees receive warnings at certain point levels, so I'd like to create workflows for when the cumulative total reached 2, 4, 6, 8, and 10 points, but I can't figure out how to calculate the total of all Points in related records in the Attendance module. I tried rollupsum, but I can't figure it out. All of the examples I have looked at are based on dates or other multiple criteria. I'm simply trying to get a cumulative total. Any help is greatly appreciated. 

Parents
  • Hi Kristen Dougherty,

    rollupSum() or rollupConditionalSum() seem to be the most fitting functions for the goal you have described.

    I hope this helps!

  • Hi Patrick, 

    Do you know if I can expand on this using the last 12 months as criteria for the calculation? Ultimately, points reset after a rolling 12 month period, so maybe I can build that in to the rollupsum calculation? 

    Thanks in advance,

    Kristen

  • Hi Kristen Dougherty,

    Please forgive as I think I am missing the meaning of the follow-up question. There are two details that come to mind as I consider your question.

    1. Calculations only and always run at the time the record is saved/updated, and whenever a related record in the related module identified in the formula is saved/updated, so if you wish to update past records, you will be need to make a change to a relevant record or use the listview option to recalculate values.

    2. Since the calculation reruns whenever a related record is saved, the field will update whenever the data this field is calculating changes in those related records.

  • Hi,

    The way I get the calculations to update is to create a process definition that changes a field in the module based on the related module being created or changed. This works just fine to update the records automatically. 


    I think I understand what you're saying but is it possible to add criteria to the rollup calculation like an IF/THEN? For example, if I mark the attendance records that are older than 12 months as "inactive" can records with the criteria of status= inactive be excluded from the sum? How would I accomplish that? 

    Thank you...

Reply
  • Hi,

    The way I get the calculations to update is to create a process definition that changes a field in the module based on the related module being created or changed. This works just fine to update the records automatically. 


    I think I understand what you're saying but is it possible to add criteria to the rollup calculation like an IF/THEN? For example, if I mark the attendance records that are older than 12 months as "inactive" can records with the criteria of status= inactive be excluded from the sum? How would I accomplish that? 

    Thank you...

Children
  • Kristen Dougherty,

    That, I believe, is the intended functionality of the rollupConditionalSum() function.

  • So I've been trying to figure this out all day. the rollupSum calculation worked perfectly. I am trying the rollupConditionalSum and it is not calculating anything now. 

    I created a field called twelvemonthpoints_c. It is a dropdown field with "Count" or "Expired" as the values. If the field is "Count" I want the points to sum. If it is "Expired" I do not want them summed. 

    The calculated field is "Cumulative Points" and it is in the Contacts module. The formula I'm using is:

    rollupConditionalSum($calls_parent,"points2_c","twelvemonthpoints_c","Count")

    Do you know what I am doing wrong?

  • I actually figured it out. I created a duplicate relationship. Instead of $calls_parent, I should have been using $contacts_calls_1. It's working as expected now.