FetchXml Conditions on linked entities


  • Hi, I am wanting to add an some conditions in an OR filter where these conditions are for attributes on different entities.

    For example, something like:

    <fetch mapping='logical' page='1' count='10'>
     <entity name='account'>
    <all-attributes />
    <link-entity name='systemuser' to='createdby' alias='sysuser'>
    <all-attributes />
    <filter type='or'>
    <condition attribute='lastname' operator='like' value='%mar%' />
    <condition attribute='sysuser.lastname' operator='like' value='%mar%' />
    Is there a way to make this kind of filter work?
    Wednesday, March 09, 2011 11:10 PM

All replies

  • Nope. FetchXml doesn't support doing "OR"s between different entities (a problem I've run into many times).  I think you could get a similar query that filters on the user's fullname by doing this:

    <filter type='or'>
      <condition attribute='lastname' operator='like' value='%mar%' />
      <condition attribute='createdbyname' operator='like' value='%mar%' />

    This works because the user's fullname (i.e., the primary field of the systemuser entity) is included in the account view as "createdbyname".

    Another option would be to create a plug-in that would set a custom "created by last name" field on the account.  But of course then you're de-normalizing your data so you have to decide if its really worth it.

    Erik Pool | Avanade XRM |
    Wednesday, March 09, 2011 11:54 PM
  • Thanks for the reply.

    In my case, I'm not actually wanting the condition based on createby (i just used that as my example because I copied it from MSDN and made some minor changes).

    Adding a field and creating a plugin to populate is one way of doing this, but obviously this is something I'd prefer to avoid.

    Thursday, March 10, 2011 12:12 AM
  • I think you should be able to do this. for example:-

     <fetch count='1' mapping='logical'>
     <entity name='annotation'>
     <attribute name='annotationid'/>
     <attribute name='createdon'/>
     <attribute name='documentbody'/>
     <attribute name='filename'/>
     <attribute name='isdocument'/>
     <attribute name='mimetype'/>
     <attribute name='notetext'/>
     <order attribute='createdon' descending='true'/>
     <filter type='or'>
      <condition attribute='documentbody' operator='not-null'/>
      <condition attribute='mimetype' operator='eq' value='text/xml'/>
      <condition attribute='createdon' operator='on-or-before' value='3/4/2011'/>
     <link-entity name='customentity' from='customentityid' to='objectid'>
      <attribute name='lastapproveddate'/>
      <filter type='or'>
      <condition attribute='customentityid' operator='eq' value='e539f9e3-e946-e011-9e01-000c2977dd1b'/>

    You can download fetchxml builder. It will make thing easier. 

    Regards Faisal
    • Proposed as answer by Jim Glass Jr Thursday, March 10, 2011 6:48 PM
    Thursday, March 10, 2011 11:37 AM
  • Think Luke requirement is something as below.

    Need to obtain the accounts whose

    a) Account name is ABC


    b) Last name of the user who created the account is ABC


    Luke - Correct me if i had wrongly understood it.

    If this is going to be the requirement, then I hope Fisal structure is not going to help you. fetchxml is not going to help us. By default fetchxml dosent provide option to combine (AND or OR) filter from primary entity and link entity.

    You can also try to perform the same form advanced find view. You will be provide with an alert

    "To group conditions, select two or more conditions or groups that are listed under the same entity, and then click Group And or Group Or. Individual conditions that are already grouped cannot be included in new groups, and related entity conditions or groups cannot be grouped with the parent entity conditions or groups."

    Thursday, March 10, 2011 2:26 PM
  • use createdbyidname in condition
    Regards Faisal
    • Proposed as answer by Jim Glass Jr Thursday, March 10, 2011 6:48 PM
    Thursday, March 10, 2011 3:07 PM
  • I'm running into a similar situation although I am using QueryExpression to retrieve with this criteria:

    1. PrimaryEntity.Name = "abc" OR RelatedContact.Name = "abc".

    I can create a LinkEntity Criteria but then I cannot create an OR group with another filter on the primary entity. Is there a workaround or is such a query just not possible?

    Monday, March 21, 2011 8:53 PM
  • I have the similar issue. Is there any solution for this?
    Tuesday, March 20, 2012 8:21 PM
  • Does somebody knows why this isn't supported?

    Greivin Britton

    Tuesday, July 31, 2012 11:09 AM
  • You can set up a custom attribute on the related entity to hold the value of the primary entity.  This value can then be maintained via plugins so it is always up to date.  Then you can set up an OR statement at the related entity level.

    • Proposed as answer by itara Tuesday, September 11, 2012 7:01 AM
    Monday, August 20, 2012 11:23 AM
  • Well, maybe a bit late but here is my working example where I solved that problem.

    After defining the link-entity you just have to reference a link-entity attribute using entityname="ENTITYNAME or ALIAS" in the condition.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
      <entity name="opportunity" >
        <attribute name="name" />
        <attribute name="customerid" />
        <attribute name="estimatedvalue" />
        <attribute name="statuscode" />
        <attribute name="opportunityid" />
        <order attribute="name" descending="false" />
        <filter type="and" >
          <condition attribute="statuscode" operator="eq" value="281290013" />
          <filter type="or" >
            <condition attribute="name" operator="eq" value="test" />
            <filter type="and" >
                <condition entityname="campaign" attribute="test_code" operator="ne" value="101" />
                <condition entityname="campaign" attribute="test_code" operator="not-null" />

    Best regards and happy coding


    • Proposed as answer by Daniel327 Thursday, December 01, 2016 6:31 PM
    Thursday, November 26, 2015 4:12 PM