locked
Fetch XML: How to retrieve data with multiple linked entities RRS feed

  • Question

  • 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

  • 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