I’m working with a FetchXML query to retrieve data from Microsoft Dynamics CRM, and I need to filter the results based on a search term that should match fields from both the main entity and a linked entity. Specifically, I’m trying to search for the name field in the activity entity and the name field in the activitytype linked entity.

Here’s the code I’m using:

const fetchXml = `<fetch mapping="logical" count="${perPage}" page="${page}">
                    <entity name="activity">
                      <attribute name="name"/>
                      <attribute name="city"/>
                      <link-entity name="activitytype" from="activitytypeid" to="activitytypeid" link-type="outer">
                        <filter type="or">
                          <condition attribute="name" operator="like" value="%${search}%" />
                        </filter>
                        <attribute name="name" alias="activity_type_name" />
                      </link-entity>
                      <filter type="or">
                        <condition attribute="name" operator="like" value="%${search}%" />
                        <condition attribute="city" operator="like" value="%${search}%" />
                      </filter>
                    </entity>
                  </fetch>`;

Problem: The query returns results from the activity entity where either name or city matches the search term. However, when searching for name within the linked activitytype entity (via activitytypeid), it does not return any matching results.

What I want to achieve: I want to search the activity entity’s name field, the activity entity’s city field, and also the activitytype entity’s name field and include the results if they match the search term. The activitytype linked entity should always be included in the results, but the filter on activitytype.name does not seem to work.

Questions:

1. Is the issue with the way I’m using the filter inside the link-entity?

2. Should the filter on the link-entity be structured differently for it to return matching results from the activitytype entity?

3. How do I ensure that the activitytype data is returned even if it doesn’t match the search term, while still filtering the results correctly?

Any help would be appreciated!

I have tried changing the XML by putting the link-entity inside the filter itself. I've also tried using aliases not I'm not sure if that's relevent. The joins I'm using as well I thought could be an issue but I'm not sure