You’re offline. This is a read only version of the page.
I am trying to Update Quote Total from the Sum of Quote detail Prices using FetchXml Aggregate.
Suppose my Quote's Transaction currency is EURO and Organization Base currency is USD.
I noticed that Fetxchml aggregate is always returning the total in USD (standard behavior) or personal preference. So if I try to update the Quote Total(EURO) its always ending up in wrong value. But is there a way to return the total in EURO and update it back in EURO so as to ensure its always correct. Below is my code:
Entity parent = (Entity)Context.PreEntityImages["PreImage"];
decimal Total = FetchResult(parent.Id, Service);
Entity quote = new Entity("quote");
quote.Id = parent.Id;
quote["fpig_quotegrossprofit"] = new Money (Total);
Service.Update(quote);
private static decimal FetchResult(Guid parent, IOrganizationService service)
{
string quoteref = parent.ToString();
string value_sum = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' aggregate='true'>
<entity name='quotedetail'>
<attribute name='fpig_grossprofit' alias='totalamount_sum' aggregate='sum'/>
<filter type='and'>
<condition attribute='quoteid' operator='eq' uitype='quote' value = '{0}' />
</filter>
</entity>
</fetch>", quoteref);
decimal aggregate2 = 0;
EntityCollection value_sum_result = (EntityCollection)service.RetrieveMultiple(new FetchExpression(value_sum));
foreach (var c in value_sum_result.Entities)
{
AliasedValue a = (AliasedValue)c["totalamount_sum"];
if (a.Value != null)
{
aggregate2 = ((Money)a.Value).Value;
}
}
return aggregate2;
}
}
*This post is locked for comments
Thanks. If you found my answers relevant and proper, feel free to tick verify answer.
Thanks & Regards,
Yadnyesh Kuvalekar
the field at Quote level is still a currency field. the one at the quote detail is not significant for us at the moment, and is used for only calculation purposes -that's why I changed to decimal. However if we need to change that back to money, I will look at your suggestion.
Okay. How roll up field won't help you as you wanted to sum up currency amount from quote detail record to its parent records. Hence Roll up was the ideal solution. However if you always wanted to have latest value, then instead of writing plugin for aggregating the amount, you should write a plugin to calculate roll-up value manually.
Below is the MSDN link:
[View:https://msdn.microsoft.com/en-in/library/dn817863.aspx:750:50]
Also, gross profit is currency field in real world, and hence setting its type to decimal is not ideal solution.
Thanks for your reply
Roll up fields wont help us as the field should have the latest value all the time, as its used for validation.
I resolved this issue by changing the type of the quotedetail field (grossprofit) from Money to decimal.
Now the Fetchxml will always return the aggregate value same as in transaction currency
Is your issue resolved?
Hi Sameera,
Which CRM version you are using? If it is above 2015 update 1, then roll up field is the ideal solution to your requirement.
technet.microsoft.com/.../dn832162.aspx
Thanks & Regards,
Yadnyesh Kuvalekar
Tick verify answer if this resolves your problem.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (