Question Status

Suggested Answer
OHuetz asked a question on 9 Mar 2015 7:54 PM

Hi,

there are probably a lot of forum entries about this topic but I have not been able to fine a working solution so far. I want to replicate the working logic in the opportunity entity where CRM calculates the sum of all opportunity product line items from the opportunityproductsGrid in a form field. I have seen two possible solutions - I would like to understand how the existing solution actually works. I cannot identify the code that is responsible for the extraction and calculation of the Product Line Item Total fields in the opportunity form. You help is much appreciated!

Reply
Suggested Answer
Aileen Gusni responded on 9 Mar 2015 10:51 PM

Hi OHuetz..

The are two total in the Opportunity, one is Detail Amount, second one is the Total Amount. I explain the Detail Amount first, because it was determined by product line actually, and it will determine the Total Amount.

In your term, Detail Amount is Product Totals (I see from your Form)

*Detail Amount (Product Totals)

I know it's kind of confusion for this.

Because it does give different result from total Amount in the subgrid, that in fact it calculates after discount as well.

The logic is calculated the total extended amount of each product lines.

Based on the description:"Shows the sum of all existing and write-in products included on the opportunity, based on the specified price list and quantities."

So, let's say you have these products:

Product Name        Unit      Qty      Price per Unit         Total Amount       Total Discount   

==============================================================

USB Son* 16GB       PC         2               10                            20                          0

Smartphone            PC         1              550                         500                       100    

Wireless Router       PC         2               50                          100                          0

Smart Watch           PC         1              300                          300                        10    

Small Smart TV       PC         1                80                            80                        10

-----------------------------------------------------------------------------------------------------------

So the total amount will be                                               1000                      120

So in your Opportunity form, the Product Total will give you   880, not 1000

Because it was coming from 1000- 120 =  880

So, don't be confused for this :)

At first I was confused as well..

*Total Amount

While to calculate the Total Amount  it has other additional components as well, such as Additional Discount, Freight Amount,  and Tax as well.  

For the Tax is calculated by each line, so you will input Tax in the each product, but in fact the Tax wont affect the Product Total first, it will affect the Total Amount (not the detail amount I mentioned before the 880)

Again, to explain I let you know its description:

"Shows the total amount due, calculated as the sum of the products, discounts, freight, and taxes for the quote."

SO let's say you have scenario:

Sum of Products (Product Total) =     880

Then you decide to give your prospects additional discount in term of the whole prospect (you can say lump sum discount, in fact for each product you have given discounted product price), but you are so kind, or kind of hurry to close the deal, you decide to give additional 5% discount + Cashback (Additional value discount) = 10 for instance.

Then, you decide to ship or deliver using UPS/DHL, it would cost another 100, so just add it to the Form as Freight Amount.

Then, let's say you have total tax 6% from the Product Price (before discount) then you have to key in in the each product line, and let's say you get 6% from 1000 = 60, okay (just example, it can be various, it can depends as well of the VAT Rate, GST Rate, or even one product is not taxable, right, I just give an example)

So you will have:

Pre-freight amount = 826

And total amount = 986

 

From where?

Here is the detail for you.

 

 

Hope this can help you.

Thanks.

 

Reply
OHuetz responded on 10 Mar 2015 8:26 AM

Hi Aileen,

thank you very much for the detailed answer :-)

My question was more in regards to the Javascript scripts that derive the sum of product line items to display in the form fields.

Best

Oliver

Reply
Aileen Gusni responded on 10 Mar 2015 8:35 AM

Hi Oliver,

With that concept, you can build the javascript by calculate the extended amount of each product line.

SO in the opportunity form, you need to retrieve the Opportunity Product from this related Opportunity..Using OData..+ Aggregate XML

Or you can use this method:

unleashingcrm.wordpress.com/.../crm-2013-calculate-totals-from-subgrid

I would suggest you to use Plugin C# actually for this, easier..

Method:

parthimscrm.wordpress.com/.../calculate-sum-from-child-entity-using-fetch-aggregate-function-crm-2011plug-in

dynamicsofdynamicscrm.wordpress.com/.../aggregate-fetchxml-queries-for-dynamics-crm-20112013

Hope this helps.

Thanks.

Reply
Suggested Answer
Aileen Gusni responded on 9 Mar 2015 10:51 PM

Hi OHuetz..

The are two total in the Opportunity, one is Detail Amount, second one is the Total Amount. I explain the Detail Amount first, because it was determined by product line actually, and it will determine the Total Amount.

In your term, Detail Amount is Product Totals (I see from your Form)

*Detail Amount (Product Totals)

I know it's kind of confusion for this.

Because it does give different result from total Amount in the subgrid, that in fact it calculates after discount as well.

The logic is calculated the total extended amount of each product lines.

Based on the description:"Shows the sum of all existing and write-in products included on the opportunity, based on the specified price list and quantities."

So, let's say you have these products:

Product Name        Unit      Qty      Price per Unit         Total Amount       Total Discount   

==============================================================

USB Son* 16GB       PC         2               10                            20                          0

Smartphone            PC         1              550                         500                       100    

Wireless Router       PC         2               50                          100                          0

Smart Watch           PC         1              300                          300                        10    

Small Smart TV       PC         1                80                            80                        10

-----------------------------------------------------------------------------------------------------------

So the total amount will be                                               1000                      120

So in your Opportunity form, the Product Total will give you   880, not 1000

Because it was coming from 1000- 120 =  880

So, don't be confused for this :)

At first I was confused as well..

*Total Amount

While to calculate the Total Amount  it has other additional components as well, such as Additional Discount, Freight Amount,  and Tax as well.  

For the Tax is calculated by each line, so you will input Tax in the each product, but in fact the Tax wont affect the Product Total first, it will affect the Total Amount (not the detail amount I mentioned before the 880)

Again, to explain I let you know its description:

"Shows the total amount due, calculated as the sum of the products, discounts, freight, and taxes for the quote."

SO let's say you have scenario:

Sum of Products (Product Total) =     880

Then you decide to give your prospects additional discount in term of the whole prospect (you can say lump sum discount, in fact for each product you have given discounted product price), but you are so kind, or kind of hurry to close the deal, you decide to give additional 5% discount + Cashback (Additional value discount) = 10 for instance.

Then, you decide to ship or deliver using UPS/DHL, it would cost another 100, so just add it to the Form as Freight Amount.

Then, let's say you have total tax 6% from the Product Price (before discount) then you have to key in in the each product line, and let's say you get 6% from 1000 = 60, okay (just example, it can be various, it can depends as well of the VAT Rate, GST Rate, or even one product is not taxable, right, I just give an example)

So you will have:

Pre-freight amount = 826

And total amount = 986

 

From where?

Here is the detail for you.

 

 

Hope this can help you.

Thanks.

 

Reply