Details about this thread
  • State Verified Answer
  • Replies 8 replies
  • Subscribers 314 subscribers
  • Views 3611 views
  • Users 0 members are here
Related content from around SugarClub

How can I use the rollupCurrencySum and only obtain certain records?

Jeff Bickart
Here is our use case:

We have a custom field with a year field.

Accounts has a 1 to Many relationship with the custom module

I want this

rollupCurrencySum($mymodule_accounts, "amount") 

where

equal(related($mymodule_acounts, "year"), "2014")?

how can I limit my rollupCurrencySum to only this year?
  • 0 Sugar Employee Badge
    Hi Jeff,

    Conditional rollUp functions are not currently possible in Sugar. This is requested in enhancement request #59450. The request can be reviewed apart from this case here.

    However, this can be done with a simple logic hook. For an introduction to creating a simple logic hook, please review our knowledge base article on the subject at:

    Creating a Simple Logic Hook

    To build upon the information provided in that article with an example pertaining to the logic in your question, I will discuss how to make a simple logic hook that, when an Opportunity is changed to Status: Closed Won, will automatically assess all the Opportunities related to the parent account, looking for Closed Won sales status and then including only those that meet the condition in a roll up in an integer field on the account.

    This example hook would be defined in the file system at:
    custom/modules/Opportunities/logic_hooks.php

    with the following line:
    $hook_array['before_save'][] = Array(1, 'rollupsum_won_oppts', 'custom/modules/Opportunities/custom_hooks/rollupsum_won_oppts.php','rollupsum_won_oppts_class', 'rollupsum_won_oppts_method');

    The content of custom/modules/Opportunities/custom_hooks/rollupsum_won_oppts.php would then be:

    <?php
    class rollupsum_won_oppts_class {
    function rollupsum_won_oppts_method($bean, $event, $arguments) {
    if ($bean->sales_status == 'Closed Won') {
    $account = BeanFactory::getBean('Accounts',$bean->account_id);
    $account->closed_opportunities_c = 0;
    $account->load_relationship('opportunities');
    foreach ($account->opportunities->getBeans() as $opportunity) {
    if ($opportunity->sales_status == 'Closed Won') {
    $account->closed_opportunities_c = $account->closed_opportunities_c + 1;
    }
    }
    $account->save();
    }
    }
    }
    ?>

    This example can hopefully be reworked to apply to your custom module and the condition field of your choosing.

    Regards,
    Patrick McQueen
  • 0 in reply to Patrick McQueen
    Thank you for identifying the bug number so that we can track the progress.
  • 0 in reply to Patrick McQueen
    A workaround is to add a calculated field where you do the if satatment equal(related($mymodule_acounts, "year"), "2014") and then you do the roll up sum on new calulated field insted.
  • 0 in reply to Patrick McQueen
    Hi Jeff,

    Maybe you can create your own Expression and take inspiration of code from expression "maxRelatedDate".


    --Cédric

  • 0 in reply to Patrick McQueen
    Has anyone ever figured this out? I'm trying to get this done myself. Seems like it should be a pretty easy bit of code, I just can't find the proper syntax.

    I have a module of payment history and each of those records has a category (e.g. "Monthly" or "Tax" and so forth). I simply want to sum the numbers in those records and display those totals in corresponding fields in a parent module. I know this can be easily done for ALL the records using a rollUp calculated field, but how do you make it work on individual categories? I imagine the logic hook just requires a simple "WHERE" statement on the retrieve.

    I have gotten this code to print on a pure PHP page. For example, I created a little test database with categories of payment records. To pull all the "Monthly" records and add them together, I establish a link to the database with the $databaselink variable and then I just do this:

    <?php
    $foreachallmath = mysqli_query($databaselink, "SELECT id, nameofrecord, SUM(valueinrecord), category FROM mathandadding WHERE category = 'Monthly'");

    foreach($foreachallmath as $foreachtotals){ 
      echo $foreachtotals['SUM(valueinrecord)'];
    }
    ?>

    Granted, that's a little simpler than what I need to do, since I have to pull the numbers based on a relationship. So frustrating. The code is so simple, yet I can't understand why this is is so darned difficult to translate to a SugarCRM syntax.

    I checked the code for actual rollup functions in calculated fields (since we are simply attempting a rollup with a condition) here:
    \include\Expressions\Expression\Numeric\SumRelatedExpression.php

    But I'm still kind of lost as to what that code is doing.
  • 0 in reply to Patrick McQueen

    Hello Patrick:

    How are you? Do you know if this is fixed? There isn't any update on the bug...

    Thanks.

    Kind regards.

    Nikol.

  • 0 Sugar Employee Badge in reply to Nikol Ricaño

    Hi Nikol,

    Conditional rollup is possible in the product using the following function:

    Function: rollupConditionalSum
    Description: Returns the sum of the values of $field in records related by $link where $conditionField contains something from $conditionalValues.
    Parameters: Relate $link, String $field, String $conditionField, List $conditionalValues
    Return Type: Number

    I hope this helps!

  • 0 Sugar Employee Badge in reply to Patrick McQueen

    Patrick,

    This is great. Can you share a simple example of using it? Say I have a related field called invoice and I only want a rollupsum of invoices that has a status of "Closed".