locked
Complicated FetchXML, Outer Joins and filters RRS feed

  • Question

  • I am trying to create a complicate fetchxml and would appreciate some help. I am trying to do an "or" filter across attributes from the primary entity and also the filtered entity. I can't seem to be able to reference the attributes from the linked entity in my filter. Anyone know how to do this? You can see below I have tried to prefix the field name in the filter with the alias of the linked entity but it doesnt work.

    Thanks
    Paul

    <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
    	<entity name='activitypointer'>
    		<attribute name='activitytypecode'/>
    		<attribute name='subject'/>
    		<attribute name='statecode'/>
    		<attribute name='prioritycode'/>
    		<attribute name='modifiedon'/>
    		<attribute name='activityid'/>
    		<order attribute='modifiedon'/>
    
    		<filter type='or'>
    			<condition attribute='subject' operator='eq' value='Test Task'/>
    			<condition attribute='fund.myco_name' operator='eq' value='Fund 123'/>
    		</filter>
    
    		<link-entity name='myco_activityadditionalparty' from='myco_activityguid' to='activityid' alias='aa' link-type='outer'>
    			<link-entity name='myco_externalfund' from='myco_externalfundid' to='myco_relatedguid' alias='fund'  link-type='outer'>
    				<attribute name='myco_name'/>
    				
    			</link-entity>
    		</link-entity>
    	</entity>
    </fetch>

    Tuesday, April 28, 2009 9:57 AM

Answers

  • I'm not convinced that it's possible to do what you seem to be attempting with FetchXml. The problem is that the structure of a filter on attributes of a link-entity (in this case 'fund') has to be placed within the <link-entity> element (see below), and so you get 2 distinct filter expressions, which implicitly work as an AND. I don't think you can apply an OR condition across attributes in different entities.

    How are you intending to use this query ? If the results don't have to be in the xml results format from a Fetch, I'd suggest using a SQL query instead

     <filter >
    <condition attribute='subject' operator='eq' value='Test Task'/>
    </filter>

    <link-entity name='myco_activityadditionalparty' from='myco_activityguid' to='activityid' alias='aa' link-type='outer'>
    <link-entity name='myco_externalfund' from='myco_externalfundid' to='myco_relatedguid' alias='fund'  link-type='outer'>
    <attribute name='myco_name'/>
    <filter >
    <
    condition attribute='fund.myco_name' operator='eq' value='Fund 123'/>
    </filter>
    </link-entity>
    </link-entity>

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Tuesday, April 28, 2009 1:23 PM
    Moderator

All replies

  • Hi Paul,

    Are you using fetch xml builder developed by Stunnware for your fetchxml query ?

    http://www.stunnware.com/crm2/topic.aspx?id=FindingData6

    That would ease the job of writing fetchxml queries.

    Regards,
    Nishant Rana

    http://nishantrana.wordpress.com
    Tuesday, April 28, 2009 10:05 AM
  • I'm not convinced that it's possible to do what you seem to be attempting with FetchXml. The problem is that the structure of a filter on attributes of a link-entity (in this case 'fund') has to be placed within the <link-entity> element (see below), and so you get 2 distinct filter expressions, which implicitly work as an AND. I don't think you can apply an OR condition across attributes in different entities.

    How are you intending to use this query ? If the results don't have to be in the xml results format from a Fetch, I'd suggest using a SQL query instead

     <filter >
    <condition attribute='subject' operator='eq' value='Test Task'/>
    </filter>

    <link-entity name='myco_activityadditionalparty' from='myco_activityguid' to='activityid' alias='aa' link-type='outer'>
    <link-entity name='myco_externalfund' from='myco_externalfundid' to='myco_relatedguid' alias='fund'  link-type='outer'>
    <attribute name='myco_name'/>
    <filter >
    <
    condition attribute='fund.myco_name' operator='eq' value='Fund 123'/>
    </filter>
    </link-entity>
    </link-entity>

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
    Tuesday, April 28, 2009 1:23 PM
    Moderator