What is the formula to multiply 1 fields value by another?

Hi Again

The field now works correctly when creating new RLIs but hasn't calculated against all existing ones, is there a way to force all the existing records to update the field?

Many thanks

Hi Jaymie Raymond,

You can use the 'Recalculate Values' option in the list view to update any selected records. 

Chris

Hi Chris

I can only do it for 1000 records at a time it there a way to update say 100,000 at once?,

Also the calculation seems to work for all currencies except if the product is in £. Do I need to add more to the formula? 

Many thanks again for all your help!

Hi Jaymie Raymond,

Mass actions in Sugar are limited to 1,000 records at a time if you are hosted in SugarCloud. If you are hosted outside of that environment, you can change a configuration parameter at the code level to allow for more records to be updated at once.

With regards to the calculation, please provide the exact formula you are using in the RLI module. Additionally, please answer the following questions:

1. What is your default currency?
2. If there are any custom fields in your calculation, what field types were those fields were created under in Studio?

Chris

Hi Chris 

Cheers again!

We are cloud based so I guess stuck with 1000 per time. Our products are a mix of GBP/EUR/USD/KRW with GBP the default.

The formula I have for the field is:

multiply($quantity,$discount_price)

The quantity field is populated by whichever product is selected, the unit price field uses the below formula.

ifElse(equal($prime_location_c,0),
ifElse(and(isNumeric($discount_price),greaterThan($discount_price, 0)),
add(ifElse(and(isNumeric($mark_up_amount_c),greaterThan($mark_up_amount_c,0)),
ifElse(equal($mark_up_select_c, true),
multiply($discount_price,divide($mark_up_amount_c,100)),$mark_up_amount_c)
,0),ifElse(isNumeric(toString($discount_price)),
currencySubtract(
$discount_price,
ifElse(equal($discount_select, "1"),
currencyMultiply($discount_price, currencyDivide($discount_amount, 100)),
ifElse(isNumeric(toString($discount_amount)),
ifElse(greaterThan(0, $discount_price), negate($discount_amount), $discount_amount), 0)
)
),
0
))
,0),
currencySubtract(
$discount_price,
ifElse(equal($discount_select, "1"),
currencyMultiply(
$discount_price,
currencyDivide($discount_amount, 100)
),
ifElse(isNumeric(toString($discount_amount)),
ifElse(greaterThan(0, $discount_price),
negate($discount_amount), $discount_amount
), 0
)
)
)
)

Hi Jaymie Raymond,

For the multiply formula you are using: as long as you have a numeric value populated for both the quantity & discount_price fields, you should be able get a valid value in your calculated field for the record regardless of currency.

As for the second formula you referenced, I'm not clear on how this is being utilized. When you mentioned the "unit price" field in your original post and your multiply formula is using "discount_price", I assumed that is the same thing since discount_price is called "Unit Price" by default in Sugar. However, if that is true, then your formula you listed above has self-references since it is using discount_price in multiple places in the calculation. If it is self-referencing in this fashion, I don't know how that would function as desired.

In addition, the formula is using isNumeric which is not a documented formula. While isNumeric is used in some out-of-the-box calculated fields (e.g. total_amount in RLIs), I cannot save a formula with isNumeric($discount_price) because of known bug # 77548. I'm not sure how you were able to create a field with this custom formula.

All this is to say that it may be best to reach out Sugar Support directly for further assistance as they can get a more complete picture of your implementation than I can since there are some elements I cannot reconstruct in a stock instance. 

Let me know if I can be of further assistance.

Hi Jaymie Raymond,

For the multiply formula you are using: as long as you have a numeric value populated for both the quantity & discount_price fields, you should be able get a valid value in your calculated field for the record regardless of currency.

As for the second formula you referenced, I'm not clear on how this is being utilized. When you mentioned the "unit price" field in your original post and your multiply formula is using "discount_price", I assumed that is the same thing since discount_price is called "Unit Price" by default in Sugar. However, if that is true, then your formula you listed above has self-references since it is using discount_price in multiple places in the calculation. If it is self-referencing in this fashion, I don't know how that would function as desired.

In addition, the formula is using isNumeric which is not a documented formula. While isNumeric is used in some out-of-the-box calculated fields (e.g. total_amount in RLIs), I cannot save a formula with isNumeric($discount_price) because of known bug # 77548. I'm not sure how you were able to create a field with this custom formula.

All this is to say that it may be best to reach out Sugar Support directly for further assistance as they can get a more complete picture of your implementation than I can since there are some elements I cannot reconstruct in a stock instance. 

Let me know if I can be of further assistance.