Details about this thread
  • State Not Answered
  • Replies 9 replies
  • Subscribers 313 subscribers
  • Views 2790 views
  • Users 0 members are here

Sum values in related modules

Bob Bundy

Hello -

I've seen several threads/discussions about retrieving field values from related modules but not this - a calculation/summation of a field in a related module.

We created a custom module that tracks things customers purchased from us. One of these things is software licenses. A customer might buy 5 licenses today. Last year, they bought 10 and the year before, they bought 15.  That means they have a total of 30 licenses. The list of "things" they bought from us could be substantial.

We want to put a calculated field on the Account Record View Layout that shows the total number of licenses this account owns. We don't want our staff to have to manually page through all the things and add up the number of licenses.

So I'm wondering if there is a formula that can access "license" records in the "Purchases" module where licenses are not zero, sum them, and return them to a field in the Accounts module? 

I haven't seen this specific request although I've seen some similar threads but not been able to find anything that works.

Thank you very much!

Bob

  • 0

    Would the Purchases and Purchase Line Items Modules not solve this for you ?  We are testing those modules at present.

  • 0 in reply to Greg Barrass

    Might be an error on my part - Enterprise and Professional do not have the Purchases and Purchase Line Item modules as far as I can tell.

  • 0

    There are a few ways to do this but the simplest way is take advantage of the various roll up functions when you create a calculated field formula.

  • 0

    Hello Bob
    Add a new calculated field to Accounts by Studio and set for this field formula based on rollupConditionalSum (if you need to take into account status or type of licenses) or rollupSum (if licenses should be summarised with no any conditions)

    I hope it helps you.
    Let me know if you need more help.

    Regards,
    Mykola


    Integroscrm.com
    We make work in Sugar
    more convenient and efficient

  • 0

    Hi Bob,

    To achieve this, you can use a formula in Studio for the Calculated Field in the Accounts module. 

    Your formula will be something similar to the below.

    rollupConditionalSum($accounts_customrelationship,"caclculatedField","productType",createList(License))

    Note: In this formula replace accounts_customrelationship with your accounts to custom module relationship.

    Calculate Field with the custom field name in the Accounts module.

    productType is the field name in the custom module which gives the type of your product.

    License is the dropdown value in the productType field in the custom module.

    Please update respective field names and relationship names in the formula in your instance. 

    I hope this helps you. Thank you :)

  • 0 in reply to Mykola Bulava

    Thank you Mykola!

    I will try and report on the results.

  • 0 in reply to Ramya Katram

    Thank you Poojitha.  I will try this and report back on the results.

  • 0 in reply to Mykola Bulava

    Mykola -

    Your solution worked well. Thank you for pointing me in the right direction.

  • 0 in reply to Bob Bundy

    Poojitha -

    Thank you. I used the rollupSum and it worked well. I have a message out to my team on whether we need to condition it and use rollupConditionalSum. I appreciate your help!