Modifying formula / adding dependency

Hi Chris, 

This the formula: 

ifElse(and(isNumeric($quantity), isNumeric($discount_price)),
ifElse(equal($quantity, 0),
$total_amount,
currencySubtract(
ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
ifElse(equal($discount_select, "1"),
currencyMultiply(
ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
currencyDivide($discount_amount, 100)
),
ifElse(greaterThan($quantity, 0),
ifElse(isNumeric(toString($discount_amount)),
ifElse(greaterThan(0, $discount_price),
negate($discount_amount), $discount_amount
), 0
),
ifElse(isNumeric(toString($discount_amount)), negate($discount_amount), 0))
)
)
), ""
)

Hi Ada,

I may be misunderstanding something then from your prior post. I took your post to mean that you getting the Calculated Amount (total_amount) value by doing the following:

1. Figuring out the per month price of the Unit Cost (e.g. $200,000 / 12)
2. Multiplying that per month price by the service duration

However, the formula you shared for total_amount appears to be the default formula. Do you have another field performing the calculation where it is showing the 584k Euro amount? If so, what formula are you using there?

Hi Chris, 

This is everything I am using to get NewARR: 

1. Unit Price

ifElse(
and(
equal($product_template_id, ""),
not(isNumeric($discount_price)),
not(equal($quantity, 0))
),
divide($likely_case, $quantity),
$discount_price
)

2. Service (tick box), Subscription Start Date, Service Duration 

--> QQ on this: is it possible to change Service Duration by default to 12 months?

3. Calculated Amount

ifElse(and(isNumeric($quantity), isNumeric($discount_price)),
ifElse(equal($quantity, 0),
$total_amount,
currencySubtract(
ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
ifElse(equal($discount_select, "1"),
currencyMultiply(
ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
currencyDivide($discount_amount, 100)
),
ifElse(greaterThan($quantity, 0),
ifElse(isNumeric(toString($discount_amount)),
ifElse(greaterThan(0, $discount_price),
negate($discount_amount), $discount_amount
), 0
),
ifElse(isNumeric(toString($discount_amount)), negate($discount_amount), 0))
)
)
), ""
)

4. NewARR

ifElse(contains($arr_yes_no_c,"Y"),ifElse(greaterThan(subtract($discount_price,$renewal_arr_c),0),subtract($discount_price,$renewal_arr_c),"0"),"")

I am testing this once more to see the currencies.

I was always wondering why writing no-code formulas is sometimes a topic starter in the developers' branch of the Club - DevClub - also I found people sometimes call writing formulas for Cal Fields "coding a formula".

Meeting the formula like above makes it clearer why - we can imagine how much time and effort it takes to write and validate implementation -  I do admire the great persistency of Sugar Admins in getting such monsters to work!

As for me, this is clearly the case that shows what trying to put more-or-less complex calculation logic into the single Calc Field may lead to - IMHO, even if to be lucky enough to configure "superformula" once, the Prefix (Polish) Notation of the Calc Fields may turn the result into hardly readable and non-easy understandable ...code?.

I know people that were facing the same challenge of implementing calculation logic for Sugar and they never regret employing other no-code techniques for setting up automation in Sugar - the OOTB SugarBPM to visualize workflows automation, and Logic Builder service to regularly draw and redraw calculations, set up and adjust algorithms implementations

I hope this makes sense

Dmytro Chupylka

To be honest, I am quite surprise on how SugarCRM approached calculated fields and formulas - almost as low-coding.

Also, I noticed that some out-of-the-box fields from SugarCRM are low-coded.

My experience with other CRM systems is much different, where a formula is a formula and has nothing to do with coding.

Anyway, I hope I am good for now with calculated fields :D

Chris Raffle

I spotted the issue - it's not currency exchange issue, it's an issue with Calculated Amount field - strange, considering that it's an out-of-the box field: 

ifElse(and(isNumeric($quantity), isNumeric($discount_price)),
ifElse(equal($quantity, 0),
$total_amount,
currencySubtract(
ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
ifElse(equal($discount_select, "1"),
currencyMultiply(
ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
currencyDivide($discount_amount, 100)
),
ifElse(greaterThan($quantity, 0),
ifElse(isNumeric(toString($discount_amount)),
ifElse(greaterThan(0, $discount_price),
negate($discount_amount), $discount_amount
), 0
),
ifElse(isNumeric(toString($discount_amount)), negate($discount_amount), 0))
)
)
), ""
)

And this is the formula to multiply for the Number of Months of the contract:

multiply(add(subtract(monthofyear($service_end_date),monthofyear($service_start_date)),multiply(subtract(year($service_end_date),year($service_start_date)),12)),0.083333333)

--> this is working in most cases, just on some opps is off.

What's more strange, is that the field seems to work on-and-off. On some of my opportunities it works 100% and on some it does not calculate the Calculated Amount correctly.

Example - correctly calculated (for privacy I hid the product name):

Example - incorrectly calculated:

Any idea why is that occurring only on some opps? Both New Business and Upsell?

Thanks!