FetchXML for multiple entities

[bouillons]

Hi,

I'm having trouble with the FetchXML syntax to retrieve data from multiple linked custom entities. I'm looking for entity A (staffmember) that is linked 1-N to entity B (institution) and entity B is linked 1-1 to entity C (pharmacy). The FetchXML doesn't fail, but I can't seem to show any attribute from entity C.

I must be overlooking something obvious. Below is my code:

{% if user %}
{% assign contactid = user.contactid %}
{% assign fullname = user.fullname %}
<p>Welcome - {{fullname}}</p>

{% fetchxml staffmembers %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="b2b_staffmember">
    <attribute name="b2b_staffmemberid"></attribute>
    <attribute name="b2b_staffmemberfullname"></attribute>
    <attribute name="b2b_internalreference"></attribute>
    <attribute name="b2b_institution"></attribute>
    <order attribute="b2b_staffmemberfullname" descending="false"></order>
    <filter type="and">
      <condition attribute="b2b_contact" operator="eq" uitype="contact" value="{{contactid}}"></condition>
    </filter>
    <link-entity name="b2b_institution" from="b2b_institutionid" to="b2b_institution" alias="institution" link-type="inner">
      <attribute name="b2b_institutionid"></attribute>
      <attribute name="b2b_institutionname"></attribute>
      <attribute name="b2b_immatriculation"></attribute>
      <attribute name="b2b_b2bpharmacy"></attribute>
    </link-entity>
  </entity>
    <link-entity name="oms_pharmacy" from="oms_pharmacyid" to="b2b_b2bpharmacy" alias="pharmacy" link-type="inner">
      <attribute name="oms_pharmacyid"></attribute>
      <attribute name="oms_posid"></attribute>
      <attribute name="oms_posnamefr"></attribute>
    </link-entity>
</fetch>
{% endfetchxml %}

{% if staffmembers.results.entities.size > 0 %}
<b>Your institutions: v7</b>
 {% for staffmember in staffmembers.results.entities %}
  <p>Name:<b>{{staffmember.b2b_staffmemberfullname}}</b></p>
  <p>Reference:<b>{{staffmember.b2b_internalreference}}</b></p>
  <p>Institution:<b>{{staffmember['institution.b2b_institutionname']}}</b></p>
  <p>Your Pharmacy:<b>{{staffmember['pharmacy.oms_posid']}} - {{staffmember['pharmacy.oms_posnamefr']}}</b></p>
  <p>Immatriculation Institution:<b>{{staffmember['institution.b2b_immatriculation']}}</b></p>
 {% endfor %}
{% else %}
<p></p><h2>You're not attached to an institution</h2>
{% endif %}
{% endif %}

The result contains data from the staffmember and the institution, but not of the pharmacy linked to the institution:

[ragavanrajan]

Hi @bouillons 

Can you try the FetchXML builder from the XRM toolbox? 

https://www.xrmtoolbox.com/plugins/Cinteros.Xrm.FetchXmlBuilder/ 

Try playing around with different combinations and see if that works for you. 

Hope it helps. 
------------

If you like this post, give it a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users to find it.

[hardikv · Accepted solution]

Hi @bouillons 

I have checked the fetchxml and found one issue in it.

You should add the link entity before "</entity>" tag instead of after "</entity>" tag.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="b2b_staffmember">
    <attribute name="b2b_staffmemberid"></attribute>
    <attribute name="b2b_staffmemberfullname"></attribute>
    <attribute name="b2b_internalreference"></attribute>
    <attribute name="b2b_institution"></attribute>
    <order attribute="b2b_staffmemberfullname" descending="false"></order>
    <filter type="and">
      <condition attribute="b2b_contact" operator="eq" uitype="contact" value="{{contactid}}"></condition>
    </filter>
    <link-entity name="b2b_institution" from="b2b_institutionid" to="b2b_institution" alias="institution" link-type="inner">
      <attribute name="b2b_institutionid"></attribute>
      <attribute name="b2b_institutionname"></attribute>
      <attribute name="b2b_immatriculation"></attribute>
      <attribute name="b2b_b2bpharmacy"></attribute>
    </link-entity>
   <link-entity name="oms_pharmacy" from="oms_pharmacyid" to="b2b_b2bpharmacy" alias="pharmacy" link-type="inner">
      <attribute name="oms_pharmacyid"></attribute>
      <attribute name="oms_posid"></attribute>
      <attribute name="oms_posnamefr"></attribute>
    </link-entity>
  </entity>
</fetch>

--------------------------
If you like this post, give a Thumbs up. Where it solved your query, Mark as a Solution so it can help other people!

[bouillons · Accepted solution]

Hi, thanks for setting me on the right path, in fact it now works after I put the link-entity inside the other link-entity.

{% fetchxml staffmembers %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="b2b_staffmember">
    <attribute name="b2b_staffmemberid"></attribute>
    <attribute name="b2b_staffmemberfullname"></attribute>
    <attribute name="createdon"></attribute>
    <attribute name="b2b_internalreference"></attribute>
    <attribute name="b2b_institution"></attribute>
    <order attribute="b2b_staffmemberfullname" descending="false"></order>
    <filter type="and">
      <condition attribute="b2b_contact" operator="eq" uitype="contact" value="{{contactid}}"></condition>
    </filter>
    <link-entity name="b2b_institution" from="b2b_institutionid" to="b2b_institution" alias="institution" link-type="inner">
      <attribute name="b2b_institutionid"></attribute>
      <attribute name="b2b_institutionname"></attribute>
      <attribute name="b2b_immatriculation"></attribute>
      <attribute name="b2b_b2bpharmacy"></attribute>
      <link-entity name="oms_pharmacy" from="oms_pharmacyid" to="b2b_b2bpharmacy" alias="pharmacy" link-type="inner">
        <attribute name="oms_pharmacyid"></attribute>
        <attribute name="oms_posid"></attribute>
        <attribute name="oms_posnamefr"></attribute>
      </link-entity>
    </link-entity>
  </entity>
</fetch>
{% endfetchxml %}

[hardikv]

Hi @bouillons 

Did that help to solve your issue? If yes, Can you mark as the answer so it helps other users?