Microsoft Dynamics 365 | Integration, Dataverse...

FetchXML cross table column comparisons does not work onPrem 9.1

(4) Share

Report

Posted on by CU22081717-4

Trying to retrieve some records using fetchXML. I would like to do a 'Cross table column comparison' as explained in the Microsoft documentation found at https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/filter-rows?tabs=fetchxml.

Howerver, trying to execute below query (taken from the documentation) result in below error message.

Query:

<fetch>
   <entity name='contact'>
      <attribute name='contactid' />
      <attribute name='fullname' />
      <filter type='and'>
         <condition attribute='fullname'
            operator='eq'
            valueof='acct.name' />
      </filter>
      <link-entity name='account'
         from='accountid'
         to='parentcustomerid'
         link-type='outer'
         alias='acct'>
         <attribute name='name' />
      </link-entity>
   </entity>
</fetch>

Error message:

{
  "error": {
    "code": "0x80041103",
    "message": "'Contact' entity doesn't contain attribute with Name = 'acct.name' and NameMapping = 'Logical'. MetadataCacheDetails: ProviderType=Dynamic, StandardCache=True, IsLoadedInStagedContext = False, Timestamp=176956829, MinActiveRowVersion=176956829, MetadataInstanceId=58400656"
  }
}

I have tried executing the query in FetchXML Builder inside XrmToolBox and in the browser like this:

<Environment URL>/api/data/v9.1/contacts?fetchXml=<fetch>%20<entity%20name=%27contact%27>%20<attribute%20name=%27contactid%27%20/>%20<attribute%20name=%27fullname%27%20/>%20<filter%20type=%27and%27>%20<condition%20attribute=%27fullname%27%20operator=%27eq%27%20valueof=%27acct.name%27%20/>%20</filter>%20<link-entity%20name=%27account%27%20from=%27accountid%27%20to=%27parentcustomerid%27%20link-type=%27outer%27%20alias=%27acct%27>%20<attribute%20name=%27name%27%20/>%20</link-entity>%20</entity>%20</fetch>

We are running Microsoft Dynamics 365 version 2104 (9.1.37.6) (DB 9.1.37.6) onPrem.

Microsoft documentation claims that:

'With FetchXML only, you can compare field values in related tables.'

Questions:

Does any one else have the same issue?
Is there any one that is able to execute the query successfully in an onPrem environment and if so, what version are you running?
Does the query work in current online version?
Since fetchXML seems to be the only way of making this kind of query, I guess no one have a proper workaround that does not involve fetching lots of data and perform the comparison afterwards?

Categories:

Dynamics 365 general

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Suggested answer

DAnny3211 11,417 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi CU22081717-4,

The issue you're encountering with FetchXML cross-table column comparisons in Dynamics 365 v9.1 OnPrem is due to a limitation in how FetchXML is supported across different deployment models.

🔍 Why the Error Occurs

The error message:

'Contact' entity doesn't contain attribute with Name = 'acct.name' and NameMapping = 'Logical'

indicates that FetchXML is trying to reference a field (acct.name) from a linked entity (account) directly in the filter condition of the primary entity (contact). This type of cross-entity column comparison is supported only in online environments starting from certain platform updates[1].

✅ Key Findings

Cross-table comparisons using valueof (e.g., comparing contact.fullname to account.name) are supported in online environments but not in OnPrem v9.1 [1] [2].

The valueof attribute in FetchXML allows comparing fields within the same row, but only within the same entity or in supported online versions [3].

OnPrem environments, especially older versions like 9.1, do not support this feature, and attempting it results in the 0x80041103 error.

🛠️ Workarounds

Post-processing in code: Retrieve the data from both entities separately and perform the comparison in your application logic.

Use SQL-based reporting: If you have access to the database, you can write SQL queries to perform the comparison directly.

Upgrade to Online or newer OnPrem version: If feasible, consider upgrading to a version that supports this feature natively.

Unfortunately, there is no FetchXML-only workaround for this in OnPrem v9.1 due to platform limitations.

Thanks and best regards,

Daniele

Note: This response was prepared with support from Copilot to ensure clarity and completeness.

[1]: Solutionade – Comparing Two Columns in D365

[2]: Dynamics Community Forum Discussion

[3]: Rajeev Pentyala – FetchXML Filters

References

[1] Solved: Performance Issues After v26 Upgrade - Dynamics 365 Community

[2] How to work with a performance problem - Business Central

[3] Inexplicable delays when calling (OnPrem) Business Central API’s (and ...

Was this reply helpful? Yes No
Suggested answer

Nour Abuzaid 197 on at

Like (0)

Report
Copy link

Link copied!

Hi @CU22081717-4

I hope you are doing well, The issue is that valueof for cross-table column comparisons in FetchXML isn’t supported in Dynamics 365 On-Prem 9.1—it only works in Online. That’s why you're seeing the 0x80041103 error.

Possible fix
you can try fetch both fields, and compare in your application logic.

<fetch>

<entity name="contact">

    <attribute name="fullname" />

    <link-entity name="account" from="accountid" to="parentcustomerid" alias="acct">

      <attribute name="name" />

    </link-entity>

</entity>

</fetch>

Then compare contact.fullname == account.name in code.

References:
https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/filter-rows?tabs=fetchxml

Was this reply helpful? Yes No
Suggested answer

CU22081717-4 12 on at

Like (1)

Report
Copy link

Link copied!
Hi @Nour Abuzaid and @DAnny3211,

First of all, many thanks for taking your time to answer my question, I appreciate it!

You both mention that cross-table column comparisons in FetchXML isn’t supported in Dynamics 365 On-Prem 9.1. Is there any official documentation where I can read about this? Or where have you gotten this information from?

Anyhow, the official example of a cross table join that I posted in my initial question is quite simple, it's a question that retrieves all contacts where there is an account with the same name as the fullname on the contact. That query can be re-written to just do an inner join on the account name and contact fullname column instead, which is supported in Dynamics 365 On-Prem 9.1.

In my case, what I really wanted to do was to check if there where any accounts with a related contact where the organization number (custom field) didn't match the social security number (custom field) on the contact record. And I wanted to do this in combination with some other checks. All this would be possible to do in a single query if I could use the cross-table comparison and it would only return me the data I actually need.

Yes, in my situation it is possible to use SQL and that would solve the challenge however, I would not like to go that way since a move to the cloud hopefully isn't that far away and I do not want to re-write all this logic when the move happens.

Yes, it would be possible to retrieve all data and make the comparison after the retrieval of data but the performance would be really bad since it would require to fetch basically every single account and contact from the system.

So, to solve the challenge I ended up in splitting the query up in 2 queries. The first one just retrieves all records where the socical security number didn't match the organization number. The second one I did the same way, just changed the fields to the other couple of fields I needed to compare.

How I made the query (simplified):

<fetch top="50"> <entity name="contact"> <attribute name="contactid" /> <attribute name="fullname" /> <filter> <condition entityname="oouterAcct" attribute="accountid" operator="null" /> </filter> <link-entity name="account" from="primarycontactid" to="contactid" link-type="inner" alias="innerAcct"> <attribute name="name" /> </link-entity> <link-entity name="account" from="name" to="fullname" link-type="outer" alias="oouterAcct" /> </entity> </fetch>

Was this reply helpful? Yes No