locked
Probleme with related entities inside FetchXml query RRS feed

  • Question

  • Hi Everybody,

    I met a problem about the construction of a FetchXML query :

    My need is to get the result of this SQL Query : 

    SELECT DISTINCT Contact.ContactId
    FROM Contact
    LEFT OUTER JOIN grd_contact_ensemble
            INNER JOIN grd_ensemble
                ON grd_ensemble.grd_ensembleId = grd_contact_ensemble.grd_ensembleid   
        ON Contact.ContactId = grd_contact_ensemble.Contactid
    LEFT OUTER JOIN grd_address
            INNER JOIN grd_communecp
                ON grd_communecp.grd_communecpId = grd_address.grd_PostalCodeId
        ON Contact.ContactId = grd_address.grd_ContactId
    WHERE 
    grd_ensemble.grd_code = 'A04024'
    OR
    grd_communecp.grd_cp LIKE '91220%'
    OR 
    grd_communecp.grd_cp LIKE '05%'
    	

    I tried to write somthing like this, but it seems that the usage of relation into fetch xml are limited:
    In fact the clause WHERE OR between attributes of differents ralated entities seems to be impossible ?

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
        <entity name="contact" >
            <attribute name="contactid" />
            <link-entity name="grd_address" from="grd_contactid" to="contactid" alias="ae" link-type="outer" >
                <link-entity name="grd_communecp" from="grd_communecpid" to="grd_postalcodeid" alias="af" >
                    <filter type="and" >
                        <filter type="or" >
                            <condition attribute="grd_cp" operator="like" value="91220%" />
                            <condition attribute="grd_cp" operator="like" value="05%" />
                        </filter>
                    </filter>
                </link-entity>
            </link-entity>
            <link-entity name="grd_contact_ensemble" from="contactid" to="contactid" visible="false" intersect="true" link-type="outer" >
                <link-entity name="grd_ensemble" from="grd_ensembleid" to="grd_ensembleid" alias="ag" >
                    <filter type="and" >
                        <condition attribute="grd_code" operator="eq" value="A04024" />
                    </filter>
                </link-entity>
            </link-entity>
        </entity>
    </fetch>

    Do you have any ideas about this issue ?

    Thx in advance for your precious help :)

    Kya

    Tuesday, April 16, 2013 1:00 PM

Answers

  • FetchXml does not support an OR condition across conditions on different entities, so your query is not possible in one FetchXml query.

    How are you using the FetchXml results ? If you execute the code from code, your best option is probably to submit two queries (one with a filter on grd_ensemble and the other with a filter on grd_communecp), and then merge the results in code


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Proposed as answer by VidhiyaM Wednesday, April 17, 2013 8:15 AM
    • Marked as answer by Kyamit Wednesday, April 17, 2013 10:16 AM
    Wednesday, April 17, 2013 7:39 AM
    Moderator

All replies

  • FetchXml does not support an OR condition across conditions on different entities, so your query is not possible in one FetchXml query.

    How are you using the FetchXml results ? If you execute the code from code, your best option is probably to submit two queries (one with a filter on grd_ensemble and the other with a filter on grd_communecp), and then merge the results in code


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Proposed as answer by VidhiyaM Wednesday, April 17, 2013 8:15 AM
    • Marked as answer by Kyamit Wednesday, April 17, 2013 10:16 AM
    Wednesday, April 17, 2013 7:39 AM
    Moderator
  • Thank you David for your answer, so my doubts are confirmed.

    In my case, I need to attach this FetchXml to a dynamic marketing list...
    So I guess that I dont have any solution to achieve this need (with dynamic list)

    Thx again for your help

    Kya

    Wednesday, April 17, 2013 9:25 AM