rollupConditionalSum and multiple conditions

Hi,

I have created two custom modules which we feed data into from our on-prem systems via the Sugar API.
Obviously rollupConditionalSum only allows one condition, but I need 2 conditions for the calculation I want

I found an article explaining new functions can be created by using a custom php file.
We are using the hosted version of Sugar so I have no idea if I can access the file system to create a new php file?

Cut-down table structure below:

ad_admin_details
id
FeesBilled = rollupConditionalSum($ad_admin_details_ad_transaction_details,"amount_c","category_c","Fees Billed")
FeesBilledInLast2Years = ?????? category_c = 'Fees Billed' AND transDate_c > NOW() - INTERVAL 2 YEAR

ad_transaction_details
id
AdminDetailsId_c
transdate_c
category_c
amount_c

We are currently on Sugar 10.1

Thanks,
Angus

  • Hi  ,

    Introducing a new Sugar Logic function is unnecessary here and also would not directly solve your requirements for the issue. Sugar Logic formulas are recalculated on saving a record, so for a dynamic requirement of 'was this event within the past 2 years?', you would have to resave all the records each day to perform that evaluation. 

    Our Upsert Calculated Fields plugin was design to address this use case so that you can use Sugar Logic evaluations in a dynamic fashion to get accurate reporting.

    As for the rollupConditionalSum formula, there is no need to change anything with that formula. When you have multiple conditions you want to perform a rollup calculation on, my recommended practice is to create a new field in the child module that performs that evaluation. For your example, your ad_transaction_details module would have a field created that looks like the following:

    ifElse(
        and(
            isInList(
                $category_c,
                createList(
                    "Fees Billed"
                )
            ),
            greaterThan(
                daysUntil(
                    $transdate_c
                ),
                -731
            )
        ),
        "2-Year Billing",
        ""
    )

    Then, your rollupConditionalSum formula would be based on that new field where the value is "2-Year Billing".

    Lastly, you mention you are using a hosted version of Sugar. If you are hosted by Sugar, you should be on version 14.1 or 14.2. 10.1 is over 4 years old and well outside Sugar's supported release cycle. As long as you are on a supported release, our plugin will work. If you are on 10.1 and hosted by someone other than Sugar, a custom-developed solution may be your best option if upgrading to a supported release is not feasible in the near future.

    Chris

  • Thanks Chris.

    That certainly got me on the right path to achieving what I want.

    I created the calculated amount on my transaction module, and then used that new value in all my calculations up the heariarchy

    Sugar version is 14.1, no idea why I typed 10.1...... :D

    For other people reviewing this question, a screenshot of my formula on the transacton module.
    I then created a calculated field on each of the modules in the hierarchy up to the level I needed the consolidated value on.

    The data I am using is sync'd in once a day from an external system, so I think I can "touch" the required records.
    But I'll keep your plug-in in mind if that can't be achieved.

    Thanks,
    Angus.

    PS: It's slightly irritating that Sugar rips out the indenting... 

  • Hi Angus,
    I have yet to find a solution other than creating a calculated field on the child record... Either to filter or to pre-calculate the amount. That is very frustrating indeed :) 

    Regarding indenting, I agree. I've found that working in an external editor such as Notepad++ and using actual tabs (not spaces), then pasting in the formula editor keeps the tabs after saving. 

    I know Notepadd++ is not a "serious" IDE, but hey, I'm only a functional analyst ;) 

    Cheers,
    Damien

    Damien Pochon

    CRM & Digital consultant @ ITS4U Group