How to query a keyword on different attributes based on entity in fetchXML?

Question

I have 2 entities in the database; an Appointment and an Email. I want to write a search function that fetches all Appointments and Emails that contains a string a user enters. However, I want to search on different attributes based on which entity it is. For instance: I want to fetch all Appointments that have the attribute subject containing the string "meeting today" and I also want to fetch all Emails that have the attribute description containing the same string. So in simple terms, only search the subject line for Appointments and only search descriptions for Emails.

Here's what my fetchXml looks so far:

<fetch count="10" distinct="true" mapping="logical" no-lock="true" output-format="xml-platform" page="1" returntotalrecordcount="false" version="1.0">
    <entity name="activitypointer">
        <attribute name="subject"/>
        <attribute name="description"/>
        <attribute name="activitytypecode"/>
        <filter type="or">
            <condition attribute="activitytypecode" operator="like">
                <value>Email</value>
            </condition>
            <filter type="and">
                <condition attribute="description" operator="like" value="%meeting today%"/>
            </filter>
        </filter>
        <filter type="or">
            <condition attribute="activitytypecode" operator="like">
                <value>Appointment</value>
            </condition>
            <filter type="and">
                <condition attribute="subject" operator="like" value="%meeting today%"/>
            </filter>
        </filter>
    </entity>
</fetch>

This doesn't seem to grab any records back however. I can successfully fetch records when querying on a single entity type, but putting both filters doesn't return anything. Is what I'm asking possible to do in fetchXML? Or is there an issue with how I've constructed my query?

Answer 1

I just did a quick test in my environment, this is working. You can build such queries in Advanced find & download the fetchxml, before editing/testing in XrmToolBox FetchXml builder.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
  <entity name="activitypointer" >
    <attribute name="activitytypecode" />
    <attribute name="subject" />
    <filter type="and" >
      <filter type="or" >
        <filter type="and" >
          <condition attribute="activitytypecode" operator="eq" value="4202" />
          <condition attribute="subject" operator="like" value="%test%" />
        </filter>
        <filter type="and" >
          <condition attribute="activitytypecode" operator="eq" value="4201" />
          <condition attribute="description" operator="like" value="%test%" />
        </filter>
      </filter>
    </filter>
  </entity>
</fetch>