Calculated sum field with multiple conditions

I am looking to create a calculated field that will find the sum of fields on related records if they meet 2 separate conditions. I'm able to use the rollupConditionalSum to account for one of the conditions, but I can't figure out how to include an additional condition. 

For my specific scenario we have a module for all our orders. Each of those order records have related individual transaction records that house specific components of the sale (i.e. a discount, fee, payment, etc.). I am looking to create a single field that will sum up all the same transaction types that have a specific status. We currently have this formula that isn't quite doing what we want:

ifElse(
equal(related($sp_transactions_ord_orders,"transaction_status"),"Applied"),rollupConditionalSum($sp_transactions_ord_orders,"transaction_amount","transaction_account_types","Applied_Payment"),0)

The problem with the above formula is that it is just looking for the presence of any related transaction with status of "applied" and then summing all transactions of type "applied_payment". What I need is for it to only sum all the transaction amounts for records that have the type of "applied_payment" AND transaction status of "Applied". 

Any guidance you can offer would be greatly appreciated!

Thank you,

Abby

Parents
  • Hi Abby

    May I ask you why are you looking for the calculated field specifically?
    Why not configure a logic hook to the Transactions record that will update the parent's record field value, do you consider this option?
    How many Transactions are created per minute?

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

Reply
  • Hi Abby

    May I ask you why are you looking for the calculated field specifically?
    Why not configure a logic hook to the Transactions record that will update the parent's record field value, do you consider this option?
    How many Transactions are created per minute?

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

Children
  • Great question! To answer your specific question, right now we would expect less than 100 transactions created or updated a minute. We are trying to keep an eye towards the future though to structure our database for scaling if we do grow.

    We are fairly new to Sugar (been in Sugar for about a year), so are still learning the capabilities and best practices. From your perspective, what situations would you utilize a logic hook vs. a calculated field vs. a business process in Sugar?