locked
How to get actual Sum of Money field when base currency is different using fetch aggregate function for Sum in a Plugin RRS feed

  • Question

  • Hi All,

    I have a requirement to get the sum of the total amount field of all related Invoices to an Order record.

    for that I am using the fetch aggregation for Sum in a plugin.

    When I add/delete/update an Invoice with total amount, this plugin getting trigger.

    This is working fine and triggering as I expected but problem is , Order Currency and base currency are different.It is giving me the result after converting it in exchange rate.I don't want to get it in converted way, I need the original value before converting it.

    How can I get this?

    Example :-

    I have two currencies in system 

    GBP and Euro.

    GBP is the base currency of the organisation and Euro is custom currency and having the Exchange rate of 1.1600.

    I have an Order in CRM which is using Custom currency Euro .I want to put total amount sum of all related Invoice to the total amount field of Order. for that I have wrote one plugin and triggering perfectly.

    Order have 3 invoices in Euro Currency with value

    1000,500 and 700 euro in their respected total Amount field.

    But I am getting the result 1896.51 while updating invoice instead of 2200 (1000+500+700).

    this is equal to the sum/exchange rate (2200/1.16), I don't want this result, i want 2200.

    is there any way to resolve it?

    any help much appreciated.

     

    Wednesday, April 17, 2013 7:53 AM

All replies

  • Hi,

    Try use amount_base field in your fetchxml for total and see if you are getting correct result.


    Contact Me
    Follow me on Twitter
    My Facebook Page
    Microsoft Dynamics CRM 2011 Application Design
    Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    Wednesday, April 17, 2013 8:05 AM
    Moderator
  • Hi Mahender Pal,

    Thanks for Reply.I tried with that also but it is giving me the same result.

    Wednesday, April 17, 2013 9:22 AM
  • When using fetch xml aggregation via Sum Microsoft automatically uses the base currency field. This also happens in dashboards, reports, etc when using Sum & cannot be changed.

    Its because you might have a mixture of currencies in the regular currency field such as Euro & USD and therefore it just does not make sense to apply the sum aggregate across this field. Hence Microsoft default this to the base currency field. 

    You could look at adding a separate decimal field  which holds a copy of the regular currency field value that you could sum across but you would need to be very careful that any related Invoices & Orders you have could only be in 1 currency. And even then would need to handle how to display the figure to the user & indicate its currency.


    John Grace (North52) Simplifying CRM & xRM development
    How? Check out the Free Standard Edition of N52 Formula Manager at www.north52.com

    Wednesday, April 17, 2013 9:49 AM
  • Thanks John and Mahender Pal for your reply.

    I tried to get rid of this issue, but it seems to me that it is CRM default behavior while working with fetch xml aggregation function.

    To resolve this issue ,I believe that I should multiply the Currency exchange rate with my output. Hence I can prevent it to be happen.

    is there anything  else to resolve this..?

    Cheers..!!!

    Sunday, April 21, 2013 7:42 AM
  • After multiplying this result with exchange rate. 

    it is not giving the correct value after the decimal point due do some differences.

    any help much appreciated...

     

    Monday, April 22, 2013 11:57 AM