Entity field restrictions on calculating totals

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!

One thought on “Entity field restrictions on calculating totals”

Add yours

M says:

Mar 15, 2012 at 6:41 PM

I just learned about the setSubmitMode attribute method whenever doing math with OOTB calculated fields. Hope it helps you or a passerby.

—
function AddADollar()
{
var TotalCostOfCampaign = Xrm.Page.getAttribute(‘totalactualcost’);
var Plus1 = Xrm.Page.getAttribute(‘new_onemore’);

if (TotalCostOfCampaign.getValue() > 0)
{
var RealTotal = TotalCostOfCampaign.getValue() + 1;
Plus1.setValue(RealTotal);
Plus1.setSubmitMode(‘always’); // <–This commits it to the database.
return true;
}else{
return false;
}
}
—

Regards,
M

LikeLike

Share this:

Like this:

Related

One thought on “Entity field restrictions on calculating totals”

Add yours

Leave a Reply Cancel reply