Link an entity that isn't the “Primary” entity

(0) Share

Report

Posted on by KrissWatts

I've been asked to create a report on some data from CRM using FetchXML. I have never used this language before so I think this may be an easy question.

I have the following code:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="bie_backbonus" enableprefiltering="1">
    <attribute name="bie_backbonuscontractno" />
    <attribute name="bie_backbonusnoteforletter" />
    <attribute name="bie_offdate" />
    <attribute name="bie_ondate" />
    <attribute name="bie_backbonuscontractversion" />
    <attribute name="bie_backbonuscontractname" />
    <attribute name="bie_backbonuscontactname" />
    <attribute name="bie_legacybackbonuscode" />
    <attribute name="owneridname" />
    <attribute name="createdon" />
<link-entity name="bie_backbonusproductgroup" from="bie_backbonuscontractno" to="bie_backbonusid" alias="pg" link-type="outer">
    <attribute name="bie_tier2percent" />
    <attribute name="bie_tier2value" />
    <attribute name="bie_tier3percent" />
    <attribute name="bie_productsubtype" />
    <attribute name="bie_tier1value" />
    <attribute name="bie_tier3value" />
    <attribute name="bie_producttype" />
    <attribute name="bie_tier1percent" />
 </link-entity>
<link-entity name="bie_producttype" from="bie_name" to="bie_producttype" alias="p" link-type="outer">
    <attribute name="bie_producttypenl" />
</link-entity>
  </entity>
</fetch>

I have tried to link three entities above but the bie_producttype entity needs to link to the bie_backbonusproductgroup entity not bie_backbonus which I think it is. Is there a function or way to link to other entities than the "Primary" one?

I have also tried using http://www.sql2fetchxml.com/ but this produced code that my report couldn't read.

I am a SQL Developer and the code would be this if written in SQL:

SELECT  b.bie_backbonuscontractno
   ,b.bie_backbonusnoteforletter 
   ,b.bie_offdate
   ,b.bie_ondate
   ,b.bie_backbonuscontractversion
   ,b.bie_backbonuscontractname
   ,b.bie_backbonuscontactname
   ,b.bie_legacybackbonuscode
   ,b.owneridname
   ,b.createdon
   ,pg.bie_tier2percent
   ,pg.bie_tier2value
   ,pg.bie_tier3percent
   ,pg.bie_productsubtype
   ,pg.bie_tier1value
   ,pg.bie_tier3value
   ,pg.bie_producttype
   ,pg.bie_tier1percent
   ,p.bie_producttypenl
FROM bie_backbonus b
JOIN bie_backbonusproductgroup pg ON pg.bie_backbonuscontractno =    b.bie_backbonusid
JOIN bie_producttype p ON p.bie_name = pg.bie_producttype

Thanks in advance for any help

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Suggested answer

Adrian Begovich 1,027 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi KrissWatts,

It is possible to link multiple entities in one FetchXml query. Advanced Find or FetchXml Builder can assist you with creating the FetchXml query. I recommend reading this article on building queries with FetchXML and this article which talks about FetchXML generation methods.

Was this reply helpful? Yes No
KrissWatts on at

Like (0)

Report
Copy link

Link copied!

Hi Adrian,

I didn't know the FetchXML Builder existed, so thanks for this. I've completed the query now.

Was this reply helpful? Yes No
Verified answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi Kriswatts,

As suggested above, the best way to generate fetchxml is to use advance find or fetch tools.

I have done some changes to you existing code, could you please try the below code and see if it works?

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="bie_backbonus" enableprefiltering="1"> <attribute name="bie_backbonuscontractno" /> <attribute name="bie_backbonusnoteforletter" /> <attribute name="bie_offdate" /> <attribute name="bie_ondate" /> <attribute name="bie_backbonuscontractversion" /> <attribute name="bie_backbonuscontractname" /> <attribute name="bie_backbonuscontactname" /> <attribute name="bie_legacybackbonuscode" /> <attribute name="owneridname" /> <attribute name="createdon" /> <link-entity name="bie_backbonusproductgroup" from="bie_backbonuscontractno" to="bie_backbonusid" alias="pg" link-type="inner"> <attribute name="bie_tier2percent" /> <attribute name="bie_tier2value" /> <attribute name="bie_tier3percent" /> <attribute name="bie_productsubtype" /> <attribute name="bie_tier1value" /> <attribute name="bie_tier3value" /> <attribute name="bie_producttype" /> <attribute name="bie_tier1percent" /> <link-entity name="bie_producttype" from="bie_name" to="bie_producttype" alias="p" link-type="inner"> <attribute name="bie_producttypenl" /> </link-entity> </link-entity> </entity> </fetch>

Hope this helps.

Was this reply helpful? Yes No
KrissWatts on at

Like (1)

Report
Copy link

Link copied!

Thanks, Ravi. Works perfectly.

Was this reply helpful? Yes No