3

I am trying to build a FetchXML query that does the same as the following SQL query. Basically, output the opportunity names and the linked appointments' subjects if custom attribute "dev_ownercommunityid" on the appointment equals to the opportunity's ownerid attribute.

select top 10 o.[name], apt.[subject]
from Opportunity o with (nolock)
left join Appointment apt with (nolock)
on o.opportunityid = apt.regardingobjectid
where apt.dev_ownercommunityid = o.ownerid  
/* dev_ownercommunityid is a custom attribute we added to appointment entity */

I can do the outer join easily but I can't figure out how to do the "where" part.

Please kindly help. I am using Dynamics CRM 2013.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Miracle Zhou
  • 31
  • 1
  • 2

2 Answers2

1

In recent version (July 2020), the feature is available to compare the two attributes of the same entity in fetchxml using valueof tag, and it is also possible using SDK & web api. Read more

<fetch>
  <entity name='contact' >
    <attribute name='firstname' />
    <filter>
      <condition attribute='firstname' operator='eq' valueof='lastname'/>
    </filter>
  </entity>
</fetch>

In near future, we may get option to compare across two entity attributes. Watch the above documentation closely.

Till then, we have to fetch the related entity attribute value in a separate query and pass it as a parameter filter in main query.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
0

Unfortunatelly this is not possible. You can't compare two columns directly. One side of the comparison has to be a constant value. See https://www.kingswaysoft.com/blog/2013/06/18/Limitations-with-CRM-FetchXML for more details.

Ondrej Svejdar
  • 21,349
  • 5
  • 54
  • 89
  • Ondrej, I think you are correct but, while your reference addresses this issue as point #2, I would not consider it a definitive source (as say Microsoft documentation might be considered) and the associated free SQL-to-FetchXML tool while useful has bugs & limitations beyond those currently listed (e.g. lack of support for parenthesis, fails to translate's Microsoft's example SQL for FetchXML from https://learn.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/dn531006(v=crm.8) ). – Zeek2 Oct 03 '18 at 10:08
  • "...the associated free SQL-to-FetchXML tool..." being http://www.sql2fetchxml.com/ . – Zeek2 Oct 03 '18 at 10:15