Dynamics Community Forum Thread Details

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