XrmServiceToolkit.Soap.Fetch with a linked entity query

May 10, 2012 at 11:05 PM
Edited May 10, 2012 at 11:08 PM

Fetch works great (most of the time) but today I wanted the Account ID and Account Name and all I had was the Contact ID.  So I did a join using the following query but I got back really wierd results.  For example, the Account ID had a value of "accountidaccountxxxxxx-xxxx-xxxx-xxxxxxxx" and the Account Name had a value of "nameaccountTHEACCOUNTNAME".  So it appears that it is adding a prefix to the value.  It does not do this if
you are not linking entities in your query.

 

 var fetchXml = new MyStringBuilder();
    fetchXml.append("<fetch mapping='logical'>");
    fetchXml.append("<entity name='contact'>");
    fetchXml.append("<filter type='and'>");
    fetchXml.append("<condition attribute='contactid' operator='eq' value='" + contactID + "' />");
    fetchXml.append("</filter>");
    fetchXml.append("<link-entity name='account' from='accountid' to='parentcustomerid' link-type='inner' alias='account'>");
    fetchXml.append("<attribute name='accountid' />");
    fetchXml.append("<attribute name='name' />");
    fetchXml.append("</link-entity>");
    fetchXml.append("</entity>");
    fetchXml.append("</fetch>");

Coordinator
May 12, 2012 at 11:31 AM

Hey Gary.

When you want to get account id and account name using fetch, the target entity should be account instead of contact. At some stage, you can say "retrieve contact information with the related account informaiton", but mainly you are focusing on the contact inforamtion.

Second, the easy and accurate way to generate fetch xml in CRM 2011 is to use Advanced Find

 This is the fetch XML i used to get the account information using the contact id. Hope this helps.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <link-entity name="contact" from="parentcustomerid" to="accountid" alias="aa">
      <filter type="and">
        <condition attribute="contactid" operator="eq" uiname="Jaimie Ji" uitype="contact" value="{CD4888D5-7F3B-E111-95CD-000C294FB247}" />
      </filter>
    </link-entity>
  </entity>
</fetch>
May 13, 2012 at 2:24 AM
Thank you so much for the reply!  Were you able to fetch using an aggregate query?

Sent from my Samsung Captivate(tm) on AT&T

jaimieji <notifications@codeplex.com> wrote:

From: jaimieji

Hey Gary.

When you want to get account id and account name using fetch, the target entity should be account instead of contact. At some stage, you can say "retrieve contact information with the related account informaiton", but mainly you are focusing on the contact inforamtion.

Second, the easy and accurate way to generate fetch xml in CRM 2011 is to use Advanced Find

This is the fetch XML i used to get the account information using the contact id. Hope this helps.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <link-entity name="contact" from="parentcustomerid" to="accountid" alias="aa">
      <filter type="and">
        <condition attribute="contactid" operator="eq" uiname="Jaimie Ji" uitype="contact" value="{CD4888D5-7F3B-E111-95CD-000C294FB247}" />
      </filter>
    </link-entity>
  </entity>
</fetch>
May 13, 2012 at 9:22 PM

I thought about this again and I am not so sure that is correct. I have been using fetch XML and CRM for many years and I have always been able to not only get the primary entity attribute field values but also the field values of the linked entities regardless of which entity is the primary entity. Both of our queries should have returned the same information and they both do. It seems to only be an issue with the aliased linked tables because it is returning the correct values, the toolkit is just not adding the values to the JSON correctly. It is adding extra information as part of the actual value. Your example is cleaner but I think they should both work. In your example, what would happen if you also wanted the contact’s full name also?

It also does this when you do an aggregate query like the following.

var fetchXml = new MyStringBuilder();

fetchXml.append("<fetch mapping='logical' aggregate='true'>");

fetchXml.append("<entity name='invoice' >");

fetchXml.append("<attribute name='invoiceid' aggregate='count' alias='count' />");

fetchXml.append("<filter>");

fetchXml.append("<condition attribute='rsi_accountid' operator='eq' value='" + accountID + "' />");

fetchXml.append("</filter>");

fetchXml.append("</entity>");

fetchXml.append("</fetch>");

The advanced find method is nice but the Stunnware FetchXML Wizard is a lot better and easier to use I think. I use it for all of the queries I have written for server side plugin usage and they all work exactly as expected and some of them are crazy complicated.

Is it possible that there is just a minor bug in the way that the toolkit is adding the values to the JSON?

Either way, great toolkit. I love it and it has saved me a ton of work. I normally do not like using other people’s code in my own because then I have to support it down the road. I am only using the toolkit because I needed a way to use fetch XML queries in JavaScript from a web page that is not a CRM form and I could never figure out a way to get a reference to CrmEncodeDecode.CrmXmlEncode when the JavaScript was in a web resource and not inside an entity form. I added a reference to the ClientGlobalContext.js.aspx and even though the reference was good, I still got the error that CrmEncodeDecode was undefined. So after dealing with that issue for 2 days, I found the toolkit and have been using it ever since and it works great. I am just having issues whenever there are linked entities involved.

Gary Rasmussen

Direct: 208-946-4364
Toll Free: 877-476-2586

www.rocktonsoftware.com

Description: rocktonHOR.png

Work Simpler & Easier

From: jaimieji [email removed]
Sent: Saturday, May 12, 2012 3:32 AM
To: Gary Rasmussen
Subject: Re: XrmServiceToolkit.Soap.Fetch with a linked entity query [xrmservicetoolkit:355377]

From: jaimieji

Hey Gary.

When you want to get account id and account name using fetch, the target entity should be account instead of contact. At some stage, you can say "retrieve contact information with the related account informaiton", but mainly you are focusing on the contact inforamtion.

Second, the easy and accurate way to generate fetch xml in CRM 2011 is to use Advanced Find

This is the fetch XML i used to get the account information using the contact id. Hope this helps.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <link-entity name="contact" from="parentcustomerid" to="accountid" alias="aa">
      <filter type="and">
        <condition attribute="contactid" operator="eq" uiname="Jaimie Ji" uitype="contact" value="{CD4888D5-7F3B-E111-95CD-000C294FB247}" />
      </filter>
    </link-entity>
  </entity>
</fetch>
Coordinator
May 14, 2012 at 8:58 AM

Hey Gary.

Thanks for your support. I will do more testing and i do feel it could be a bug in the toolkit. Will keep you updated.

Best Regards,

Jaimie