将我的sql查询转换为crm中的queryexpression或fetchxml

问题描述

我有这个SQL查询，我试图从尚未为其创建approvaldocument的机会实体中获取opportunityId(批准文档是另一个实体的名称).我不认为fetchxml支持这种情况一种查询.我是crm的新手，我的项目是crm 4.0版本.

I have this SQL query where I am trying to fetch the opportunityId from opportunity entity for whom approvaldocument has not been created (approval document is the name of the other entity) .I dont think fetchxml supports such kind of query. I am new to crm and my project is in crm 4.0 version.

这是SQL查询:

Select OpportunityId from opportunity AS c left JOIN (select a.opportunitynameid from opportunity o
JOIN ApprovalDocument a ON 
a.opportunitynameid=o.OpportunityId) AS b ON c.OpportunityId=b.opportunitynameid
Where b.opportunitynameid IS NULL and statecode=0

我将其转换为fetchxml，但是没有给出正确的结果.

I converted this into a fetchxml, but that didn't give the correct result.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> 
    <entity name="opportunity"> <attribute name="opportunityid" /> 
       <link-entity name="approvaldocument" from="opportunitynameid" to="opportunityid" alias="a" link-type="outer"> <attribute name="opportunitynameid" /> 
       </link-entity> 
       <filter type="and"> 
          <condition entityname="a" attribute="opportunitynameid" operator="null" /> 
       </filter> 
    </entity> 
<fetch/>

推荐答案

在本地，无法创建高级查找来查询是否存在关系.但是，有几种不同的解决方案可以实现此功能:

Natively it is not possible to create an advanced find to query for the absence of a relationship. However there are several different solutions for achieving this functionality:

解决方法:创建具有完整记录集的营销列表，然后使用条件相反的条件删除记录.在这篇文章.

Workaround:Create a marketing list with the full set of records and then remove records using the inverse of your condition. The steps for doing this are nicely laid out in this article.

修改FetchXML和第三方解决方案:尽管高级查找无法显示"Not In"结果，但基础FetchXML确实支持此功能.手动构建此类提取的示例为显示在此处.另外，还有一些第三方工具可利用此功能直接在高级查找中提供不包含"功能.我知道的最佳解决方案是在此处可用.

Modifying FetchXML and Third Party Solutions:Although the advanced find cannot show "Not In" results, the underlying FetchXML does support this functionality. An example of manually building such Fetch is shown here. Also there are several third party tools which leverage this ability to provide Not In functionality directly in the advanced find. The best solution I am aware of is available here.

这篇关于将我的sql查询转换为crm中的queryexpression或fetchxml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！