Thread Details
  • State Verified Answer
  • Replies 8 replies
  • Answers 1 answer
  • Subscribers 369 subscribers
  • Views 3240 views
  • Users 0 members are here
Forum Actions

Calculated sum field with multiple conditions

Abigail Stryker

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
  • 0

    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

  • 0 in reply to Dmytro Chupylka

    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? 

  • 0 in reply to Abigail Stryker

    Logic Hook is the fastest solution in an execution perspective, but it fully requires development techniques.

    I like to say Logic Hook is pretty welcome for accomplishing procedures not supported by GUI configurations.

    Calculated field is not as faster as Logic Hook but it is very easy to implement, once you can do that through Studio. It is indicated for a solely calculation.

    Business process is indicated for calculations in a context of a well defined and implemented business process at SugarBPM, that means, if a field should be calculated somehow due some trigger which may be an ellapsed time or previously modified field or message either received or sent. In short words you will not configure an entire SugarBPM Process to update a single field.

    Kind regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
Reply
  • 0 in reply to Abigail Stryker

    Logic Hook is the fastest solution in an execution perspective, but it fully requires development techniques.

    I like to say Logic Hook is pretty welcome for accomplishing procedures not supported by GUI configurations.

    Calculated field is not as faster as Logic Hook but it is very easy to implement, once you can do that through Studio. It is indicated for a solely calculation.

    Business process is indicated for calculations in a context of a well defined and implemented business process at SugarBPM, that means, if a field should be calculated somehow due some trigger which may be an ellapsed time or previously modified field or message either received or sent. In short words you will not configure an entire SugarBPM Process to update a single field.

    Kind regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
Children
No Data