One way or another, eventually you will get a request to change the default amount calculations as built in CRM. This could be either by adding a tax percentage (after all, all provinces or states use a fixed tax rate expressed as a percentage), or to handle some other kind of discount by a specific category of customers. Another common example is to apply tax at the Opportunity or Quote level rather than doing it for each product line added. After all, who wants to do the same task 10, 15 or even 20 times when you can do it just once?

If the approach is by using JScript, it may seem like a trivial task. Retrieve the values filled in by users, put your formulas in, an voila… almost perfect. Except, some fields do not retrain the value you pushed in. Even though the calculation on the screen looks correct, when you close and save your form, the totals are being re-calculated by the system, and your formulas are ignored.

In order to identify these cases, you can look at the MSDN documentation. Following is a sub-set that should help you with any calculations you need to override on the Opportunity, Quote and Invoice. The URLs are pointing to the original extended documentation on MSDN.

Opportunity

http://msdn.microsoft.com/en-us/library/gg328229.aspx

DisplayName

SchemaName

Type

IsValidForUpdate

Account

AccountId

Lookup

FALSE

Actual Revenue

ActualValue

Money

TRUE

Actual Revenue (Base)

ActualValue_Base

Money

FALSE

Contact

ContactId

Lookup

FALSE

Opportunity Discount Amount

DiscountAmount

Money

TRUE

Opportunity Discount Amount (Base)

DiscountAmount_Base

Money

FALSE

Opportunity Discount (%)

DiscountPercentage

Decimal

TRUE

Est. Revenue

EstimatedValue

Money

TRUE

Est. Revenue (Base)

EstimatedValue_Base

Money

FALSE

Exchange Rate

ExchangeRate

Decimal

FALSE

Freight Amount

FreightAmount

Money

TRUE

Freight Amount (Base)

FreightAmount_Base

Money

FALSE

Revenue

IsRevenueSystemCalculated

Boolean

TRUE

Opportunity

OpportunityId

Uniqueidentifier

FALSE

Price List

PriceLevelId

Lookup

TRUE

Pricing Error

PricingErrorCode

Picklist

TRUE

Total Amount

TotalAmount

Money

FALSE

Total Amount (Base)

TotalAmount_Base

Money

FALSE

Total Pre-Freight Amount

TotalAmountLessFreight

Money

FALSE

Total Pre-Freight Amount (Base)

TotalAmountLessFreight

_Base

Money

FALSE

Total Discount Amount

TotalDiscountAmount

Money

FALSE

Total Discount Amount (Base)

TotalDiscountAmount_Base

Money

FALSE

Total Detail Amount

TotalLineItemAmount

Money

FALSE

Total Detail Amount (Base)

TotalLineItemAmount_Base

Money

FALSE

Total Line Item Discount Amount

TotalLineItemDiscountAmount

Money

FALSE

Total Line Item Discount Amount (Base)

TotalLineItemDiscountAmount

_Base

Money

FALSE

Total Tax

TotalTax

Money

FALSE

Total Tax (Base)

TotalTax_Base

Money

FALSE

Currency

TransactionCurrencyId

Lookup

TRUE

NOTE: On the Opportunity, it may seem misleading, but the Discount (%) and Discount fields work independent of each other. By that I mean that you can add a Discount (%) and a Discount, and they will both be subtracted from Product Totals.

Quote

http://msdn.microsoft.com/en-us/library/gg309335.aspx

DisplayName

SchemaName

Type

IsValidForUpdate

Potential Customer

CustomerId

Customer

TRUE

Quote Discount Amount

DiscountAmount

Money

TRUE

Quote Discount Amount (Base)

DiscountAmount_Base

Money

FALSE

Quote Discount (%)

DiscountPercentage

Decimal

TRUE

Exchange Rate

ExchangeRate

Decimal

FALSE

Freight Amount

FreightAmount

Money

TRUE

Freight Amount (Base)

FreightAmount_Base

Money

FALSE

Name

Name

String

TRUE

Opportunity

OpportunityId

Lookup

TRUE

Payment Terms

PaymentTermsCode

Picklist

TRUE

Price List

PriceLevelId

Lookup

TRUE

Total Amount

TotalAmount

Money

FALSE

Total Amount (Base)

TotalAmount_Base

Money

FALSE

Total Pre-Freight Amount

TotalAmountLessFreight

Money

FALSE

Total Pre-Freight Amount (Base)

TotalAmountLessFreight_Base

Money

FALSE

Total Discount Amount

TotalDiscountAmount

Money

FALSE

Total Discount Amount (Base)

TotalDiscountAmount_Base

Money

FALSE

Total Detail Amount

TotalLineItemAmount

Money

FALSE

Total Detail Amount (Base)

TotalLineItemAmount_Base

Money

FALSE

Total Line Item Discount Amount

TotalLineItemDiscountAmount

Money

FALSE

Total Line Item Discount Amount (Base)

TotalLineItemDiscountAmount

_Base

Money

FALSE

Total Tax

TotalTax

Money

FALSE

Total Tax (Base)

TotalTax_Base

Money

FALSE

Currency

TransactionCurrencyId

Lookup

TRUE

 

Invoice

http://msdn.microsoft.com/en-us/library/gg309547.aspx

DisplayName

SchemaName

Type

IsValidForUpdate

Account

AccountId

Lookup

FALSE

Customer

CustomerId

Customer

TRUE

Invoice Discount Amount

DiscountAmount

Money

TRUE

Invoice Discount Amount (Base)

DiscountAmount_Base

Money

FALSE

Invoice Discount (%)

DiscountPercentage

Decimal

TRUE

Exchange Rate

ExchangeRate

Decimal

FALSE

Freight Amount

FreightAmount

Money

TRUE

Freight Amount (Base)

FreightAmount_Base

Money

FALSE

Invoice

InvoiceId

Uniqueidentifier

FALSE

Invoice ID

InvoiceNumber

String

FALSE

Payment Terms

PaymentTermsCode

Picklist

TRUE

Price List

PriceLevelId

Lookup

TRUE

Pricing Error

PricingErrorCode

Picklist

TRUE

Priority

PriorityCode

Picklist

TRUE

Total Amount

TotalAmount

Money

FALSE

Total Amount (Base)

TotalAmount_Base

Money

FALSE

Total Pre-Freight Amount

TotalAmountLessFreight

Money

FALSE

Total Pre-Freight Amount (Base)

TotalAmountLessFreight

_Base

Money

FALSE

Total Discount Amount

TotalDiscountAmount

Money

FALSE

Total Discount Amount (Base)

TotalDiscountAmount_Base

Money

FALSE

Total Detail Amount

TotalLineItemAmount

Money

FALSE

Total Detail Amount (Base)

TotalLineItemAmount_Base

Money

FALSE

Total Line Item Discount Amount

TotalLineItemDiscountAmount

Money

FALSE

Total Line Item Discount Amount (Base)

TotalLineItemDiscountAmount

_Base

Money

FALSE

Total Tax

TotalTax

Money

FALSE

Total Tax (Base)

TotalTax_Base

Money

FALSE

Currency

TransactionCurrencyId

Lookup

FALSE

 

Of course, you could write a plugin to do this, but if you need a quick and dirty solution, maybe for a presentation, or when going for a more agile approach, JScript is your friend.

Also, you will find that, for some fields, you will end-up hiding the original field, and creating your custom field to store the calculated values. When you do so, do not forget to hide the original and add your custom field in all the related views. The last thing you want is your invoice showing a price, but when you look at all the invoices for that month, to show a different amount in the view. Same goes for reporting, make sure that when you have a calculated field, you use that in the report.

Enjoy!

Advertisements