cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bouillons
Helper V
Helper V

FetchXML for multiple entities

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:

 

bouillons_0-1658767726246.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
hardikv
Memorable Member
Memorable Member

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!

View solution in original post

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 %}

View solution in original post

4 REPLIES 4
ragavanrajan
Super User
Super User

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
Memorable Member
Memorable Member

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!

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
Memorable Member
Memorable Member

Hi @bouillons 

 

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

Helpful resources

Announcements
Carousel Community Blog

Check out the Community Blog

Read all about the most recent blogs in the community!

Carousel News & Announcements

What's New in the Community?

Check out the latest News & Events in the community!

Users online (2,889)