FetchXML question

Sep 3, 2012 at 9:48 AM

Hello guys,

I have a fetchXML query like this:

        "<fetch mapping='logical' aggregate='true'>" +
	        "<entity name='quotedetail'>" +
		        "<attribute name='extendedamount' alias='sumExtendedAmount' aggregate='sum'/>" +
                "<filter type='and'>" +
			        "<condition attribute='quotedetailid' operator='in'>" +
				        "<value>" + stringIds + "</value>" +
			        "</condition>" +
		        "</filter>" +
	        "</entity>" +
        "</fetch>"

which works great with XrmSvcToolkit.fetch, but it's returning the amount in the Base Currency when I parse it like this:  

quoteLinesTotalAmount = result.entities[0]['sumExtendedAmount'].Value;

This complicates things when the Currency of the record is different because I have to deal with the exchange rate etc.   I'd like to get the amount in the Currency of the record, not the Base Currency... Is it possible?

thanks!~

Coordinator
Nov 15, 2012 at 2:28 AM

Hey,

The behaviour is by design. You're running aggregate over the currency field and CRM has no choice but to convert all values to base currency. How else would you deal with the situation where some quotes are in dollars, some in euros and others - in pounds?

If you retrieve a single record then you get back extended values that include information about transaction currency.

HTH
George

Nov 16, 2012 at 6:52 AM

Thanks George, that makes sense! 

All the best with everything,

Nate

Jun 19, 2013 at 3:06 PM
Hi Guys, I am trying to execute a very similar query to this but the execution just fails completely. I've tried executing the FetchXML standalone and I get the following error:

Unable to cast object of type 'System.String' to type 'Microsoft.Xrm.Sdk.Money'.

The query I'm using is here:

<fetch mapping='logical' aggregate='true'>
<entity name='new_entityid'>
<attribute name='new_fieldvalue' alias='Total' aggregate='sum'/>
<filter type='and'>
<condition attribute='new_entityid' operator='eq' value='GUID' />
</filter>
</entity>
</fetch>

Any help you can offer would be amazing as this is driving me mental.
Coordinator
Jun 19, 2013 at 3:36 PM
@UberMunchkin, the error message you posted is probably coming from your C# code. By looking at the FetchXML query, I don't see anything wrong, except that you might have a typo with your entity name.

BTW, this seems to be a whole new topic.
Jun 19, 2013 at 3:39 PM
Hmm, not using C#, I'm using Javascript.

It's very frustrating as there is no other useful information provided by the error. Thanks for responding though.
Coordinator
Jun 19, 2013 at 3:41 PM
@UberMunchkin, this is strange, can you run the same query if you use Stunnware Tools?
Jun 19, 2013 at 3:47 PM
Yes, it executes fine in the Stunnware tools.
Coordinator
Jun 19, 2013 at 5:08 PM
@UberMunchkin, that's very strange. JavaScript should never report a type of 'System.String' or 'Microsoft.Xrm.Sdk.Money'.
Coordinator
Jun 19, 2013 at 10:00 PM

Howdy,

Based on the error message I’d say error is definitely coming from the server-side. If I did not know better I’d say that the field new_fieldvalue is not money type.

This speculation aside, I would try to run Fiddler2, run query through Stunnware then through client code and pick up the differences in trace.

Thanks

George

Jun 24, 2013 at 9:50 AM
Interestingly enough the error turned out to be naming.

The Fetch XML executed just fine in the Stunnware Tools but blew up every time I tried to run it using XrmSvcToolkit. So on a whim I changed this line:

<attribute name='new_fieldvalue' alias='Total' aggregate='sum'/>

to this:

<attribute name='new_fieldvalue' alias='new_fieldvalue_Total' aggregate='sum'/>

and it worked just fine. It looks like something in the XrmSvcToolkit or one of it's dependencies doesn't like you using the alias 'Total' in aggregate queries.

Cheers for the help folks.
Coordinator
Jun 24, 2013 at 11:30 AM

Hmmm, interesting… I think the problem could be related to the endpoints in use as Stunnware tools use CRM 4 endpoint, AFAIK. I don’t see any other reason why it would work differently.