Remove From My Forums

Fetch XML: How to retrieve data with multiple linked entities

Question
0

Sign in to vote
Hello,

I have three entities; Project, Product and Contact (Default entity).

Project entity can have multiple Products but a product can only belong for one project (1:N relation)

Product can have multiple contacts and a contact can belong to multiple products (N:N relation).

What I'm trying to do is to create a FetchXML query which will return every contact that belong to any products that belong to this particular project. I'm doing this inside a Project form so I know the ProjectId.

But I'm stuck. I'm not really familiar with FetchXML yet. I tried to Google and stuff with no success. Probable this is clear for you so I hope you could help me.

What I have achieved so far is a FetchXML query that will return all contacts that are related to any products. So I'm halfway there I guess. Now I should only add a condition where it would return only the contacts that are related to this project's products right?

Anyway, here is my FetchXML so far

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'> <entity name='contact'> <attribute name='contactid' /> <attribute name='fullname' /> <attribute name='jobtitle' /> <attribute name='cre_role' /> <attribute name='cre_stakeholder' /> <attribute name='address1_city' /> <order attribute='fullname' descending='false' /> <link-entity name='cre_contact_cre_product' from='contactid' to='contactid' alias='aa'> </link-entity> </entity> </fetch>

If you wonder the link-entity name 'cre_contact_cre_product' it's because Product - Contact relationship is N:N and thus I have to use intersect entity (http://msdn.microsoft.com/en-us/library/gg309538.aspx)

So the code above works in a way that it returns every contact in CRM that is related to any product. So how should I continue? I tried almost everything. I guess I have to add one more link-entity inside the current link-entity there...

I tried something like this without success

<link-entity name='cre_project' from='cre_productid' to='cre_productid' alias='bb'> <filter type='and'> <condition attribute='cre_projectid' operator='eq' uiname='Contact' uitype='contact' value='" + projectid + "' /> </filter> </link-entity>

Thank you in advance!
- Edited by kkivist Saturday, May 18, 2013 9:51 AM
Saturday, May 18, 2013 9:45 AM

Answers

Thank you for your answer but it was not what I was looking for. I think I explained my problem not very well. I think what I was looking for are called nested link-entities?

Anyway, I figured this out by myself just a moment ago. Here my final answer and it works how I wanted. Maybe this will help somebody in a future

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
    <entity name='contact'>";      
        <attribute name='contactid' />
        <attribute name='fullname' />
        <attribute name='jobtitle' />
        <attribute name='cre_role' />
        <attribute name='cre_stakeholder' />
        <attribute name='address1_city' />               
        <order attribute='fullname' descending='false' />           
        <link-entity name='cre_contact_cre_product' from='contactid' to='contactid' alias='aa'>     
            <link-entity name='cre_product' from='cre_productid' to='cre_productid' alias='bb'>          
                <link-entity name='cre_project' from='cre_projectid' to='cre_projectid' alias='cc'>                   
                    <filter type='and'>                   
                        <condition attribute='cre_projectid' operator='eq' uiname='Contact' uitype='contact' value='" + projectid + "' />                    
                    </filter>                           
                </link-entity>                      
            </link-entity>                 
        </link-entity>
    </entity>
</fetch>

My problem was that when I was using intersect entity (<link-entity name='cre_contact_cre_product' from='contactid' to='contactid' alias='aa'>) I wrongly assumed that it returns the entity itself but of course it does not.

Marked as answer by kkivist Saturday, May 18, 2013 10:49 AM

Saturday, May 18, 2013 10:47 AM

All replies

0

Sign in to vote
Hi,

To use multiple link entities in fetchxml,go through this link,

https://community.dynamics.com/crm/b/mscrmshop/archive/2012/09/30/outer-joins-in-fetch-xml-queries-in-crm2011.aspx#.UZdVsqJHKu8

http://lakshmanindian.wordpress.com/2012/11/30/retrieve-data-using-fetchxml-with-multiple-link-entities-in-crm-2011/

http://blog.rahulsharma.in/2011/04/microsoft-dynamics-crm-2011-fetch-xml.html

hope it will help you

VidhiyaM
- Edited by VidhiyaM Saturday, May 18, 2013 10:21 AM
- Proposed as answer by VidhiyaM Saturday, May 18, 2013 10:21 AM
Saturday, May 18, 2013 10:18 AM