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

Calculated Field - Total Active MRR

Sam Hennessy

Hi Everyone,

We have a "Total Active MRR" field at the account level that calculates the MRR value of all active opportunities. This data was originally populated using Snowflake, but we now want to use SugarCRM's out-of-the-box (OOTB) features.

Definition: Total Active MRR is the total monthly recurring revenue from all active subscriptions on an account. It includes all opportunities where the sales stage is "Sales Closed Won," the MRR is greater than $0, and the contract end date is in the future.

After multiple attempts, this is the JSON formula I managed to get working. However, it only calculates correctly for some accounts, while many others show a value of 0 or remain blank even though they meet the criteria:

rollupConditionalSum(
"Opportunities",
"mrramount_c",
"equal($sales_stage, 'SalesClosedWon')",
"greaterThan(timestamp($pr_contract_end_date), timestamp(now()))"
)

Can anyone help me figure out why this might be happening and how to get it to calculate correctly for all accounts?

Thanks in advance!

Parents
  • 0 Sugar Employee Badge

    Hello  , 

    Thanks for your post. 

    Calculated fields in SugarCRM are updated when a record is saved. This means that to see the updated values, you would need to edit and save each account. You can achieve this through a mass update, (depending on the number of accounts in your instance).

    However, there's a caveat: the calculated value becomes static until the account is re-saved. Given that you have a contract end date, there's a risk that users might see outdated "Total Active MRR" values if the contract end date has passed but the record hasn't been re-saved.

    In this case, a calculated field might not be the best option. Instead, you might want to consider implementing a Scheduler to update this value. Here are some benefits:

    Daily Updates: You can set the Scheduler to run daily and update the "Total Active MRR" based on the current contract status.

    Improved Performance: This approach removes the processing load from the save() action on accounts and opportunities, enhancing performance when records are saved.


    Schedulers are an out-of-the-box (OOTB) feature in SugarCRM, but you'll need some development experience to set them up.

    Hope this helps and let us know your thoughts. 



    Cheers, 

    André 

  • 0 in reply to Andre Serrazina
    Hi André,

    Thanks for your quick response.

    I assumed it would be a static field and tested it by changing some fields at the account level. After saving the record, the correct MRR value still did not populate; it stayed at 0.

    For example, this account has 2 opportunities with sales closed won, contract end dates in the future, and MRR values. Even though all criteria are met, the MRR value remains 0.

    Thanks for suggesting another approach. I hope I can make it work!

    Regards,

    Sam

  • 0 Sugar Employee Badge in reply to Sam Hennessy

    Hello  , 

    If you still want to explore the calculated field approach, you’ll need to correct your formula. Here’s how you can do it:

    As indicated in the formula hint picker, the rollupConditionalSum should look like this:

    rollupConditionalSum(Relate link, String field, String conditionField, List conditionalValues)

    Taking your example, it should be something like this:

    rollupConditionalSum($opportunities, "mrramount_c", "sales_stage", createList("Closed Won"))

    This formula should work, but it doesn’t account for the date. To include the date, you could create a new calculated field in opportunities to check if the status is "Closed Won" and the contract is active. Then, use this new field in the rollupConditionalSum formula instead of the sales_stage. 

    However, keep in mind that this approach has a similar limitation: the value won't update automatically when the contract expires; it will only update when the opportunity is re-saved.

    Let me know your thoughts.

    Many thanks, 

    André 

Reply
  • 0 Sugar Employee Badge in reply to Sam Hennessy

    Hello  , 

    If you still want to explore the calculated field approach, you’ll need to correct your formula. Here’s how you can do it:

    As indicated in the formula hint picker, the rollupConditionalSum should look like this:

    rollupConditionalSum(Relate link, String field, String conditionField, List conditionalValues)

    Taking your example, it should be something like this:

    rollupConditionalSum($opportunities, "mrramount_c", "sales_stage", createList("Closed Won"))

    This formula should work, but it doesn’t account for the date. To include the date, you could create a new calculated field in opportunities to check if the status is "Closed Won" and the contract is active. Then, use this new field in the rollupConditionalSum formula instead of the sales_stage. 

    However, keep in mind that this approach has a similar limitation: the value won't update automatically when the contract expires; it will only update when the opportunity is re-saved.

    Let me know your thoughts.

    Many thanks, 

    André 

Children