使用CRM REST Builder的Predefined Query在js结合FetchXML语句进行查询
一般情况下使用拓展工具RESTBuilder编辑器，可以很方便的进行操作js中增删改查均能实现，但在某些较为特殊的场景下，需要根据条件去拼接查询过滤条件的，使用编辑器生成的代码无法实现，需要结合使用fetchXML，比如某个条件多个值都查询需要使用in查询，再或者需要过滤关联表中的某个字段的值。
代码使用示例：
1 /** 2 * 根据出口目的国以及限售区域判断是否存在不可销售的产品，如果存在，返回false 3 */ 4 function checkProductRegion() { 5 var isSubmit = true; 6 let returnAnalysisId = commonUtil.delBrackets(Xrm.Page.data.entity.getId()); 7 let destcountryId = commonUtil.getLookupId("new_destcountry") // 出口目的国 8 let destcountryName = commonUtil.getLookupName("new_destcountry") // 出口目的国 9 10 let returnAnalysisFetchXml = `<fetch mapping="logical" version="1.0"> 11 <entity name="new_return_analysis_detail"> 12 <attribute name="new_name"/> 13 <attribute name="new_product_fourthid"/> 14 <link-entity name="new_product_fourth" from="new_product_fourthid" to="new_product_fourthid" alias="PF" link-type="inner"> 15 <attribute name="new_issale"/> 16 <link-entity name="new_product_team" from="new_product_teamid" to="new_product_teamid" alias="PT" link-type="inner"> 17 <attribute name="new_restrictedsales"/> 18 </link-entity> 19 </link-entity> 20 <filter type="and"> 21 <condition attribute="new_return_analysis" operator="eq" value="${returnAnalysisId}"/> 22 </filter> 23 </entity> 24 </fetch>`; 25 26 var url = `/new_return_analysis_details?fetchXml=${encodeURIComponent(returnAnalysisFetchXml.replace(/>\s+</g, '><'))}`; 27 commonUtil.queryWithUrl(url, (result) => { 28 if (result.data.filter(r => r["PF.new_issale"] == false).length > 0) { 29 isSubmit = false; 30 Xrm.Utility.alertDialog(`提交失败 : 明细中有物料不可销售`); 31 return isSubmit; 32 } 33 let restrictedProdList = result.data.filter(r => r["PT.new_restrictedsales"] == 10).map(p => p._new_product_fourthid_value) 34 if (restrictedProdList.length == 0) { 35 return isSubmit; 36 } 37 38 let productRegionRelationShipFetchXml = `<fetch mapping="logical" version="1.0"> 39 <entity name="new_productregionrelationship"> 40 <attribute name="new_productcode" /> 41 <attribute name="new_country_region" /> 42 <filter type="and"> 43 <condition attribute="new_productcode" operator="in"><value>${restrictedProdList.join('</value><value>')}</value></condition > 44 <condition attribute="new_country_region" operator="eq" value="${destcountryId}" /> 45 </filter> 46 </entity> 47 </fetch>`; 48 49 var url = `/new_productregionrelationships?fetchXml=${encodeURIComponent(productRegionRelationShipFetchXml.replace(/>\s+</g, '><'))}`; 50 commonUtil.queryWithUrl(url, (results) => { 51 // 限制区域的产品资源数量 != 查询到的【产品资源与销售区域的关系】数量 52 // 也就是存在当前出库目的国不可销售的产品资源 53 let canntSaleList = restrictedProdList.filter(p => !results.data.some(r =>r.new_productcode === p.id)) 54 if (canntSaleList.length) { 55 isSubmit = false; 56 let prodNameList = result.data.filter(r => canntSaleList.indexOf(r._new_product_fourthid_value) >= 0).map(p => p.new_name) 57 Xrm.Utility.alertDialog(`提交失败 : ${`收益分析明细[${prodNameList.join(",")}]在出口目的国[${destcountryName}]不可销售! `}`); 58 } 59 }, false); 60 }, false); 61 62 return isSubmit; 63 }
let shipmentId = commonUtil.delBrackets(Xrm.Page.data.entity.getId())
let fetchXml = `<fetch mapping="logical" version="1.0">
<entity name="new_box_single_detail">
<attribute name="new_customs_receipts"/>
<attribute name="new_is_erp"/>
<link-entity name="new_outbound_order_details" from="new_outbound_order_detailsid" to="new_out_detail" alias="OOD" link-type="inner"/>
<filter type="and">
<condition entityname="OOD" attribute="new_outbound_order" operator="eq" value="${shipmentId}"/>
</filter>
</entity>
</fetch>`
let boxDetailQuery = `/new_box_single_details?fetchXml=${encodeURIComponent(fetchXml.replace(/>\s+</g, '><'))}`
let showButton = false
commonUtil.queryWithUrl(boxDetailQuery, result => {
showButton = result.success && result.data.length
&& result.data.filter(d => !d.new_is_erp && d.new_customs_receipts).length > 0
}, false)
1 let fetchXml = `<fetch mapping="logical" version="1.0"> 2 <entity name="foton_kd_claimdetail"> 3 <all-attributes /> 4 <link-entity name="foton_kd_vehiclelib" from="foton_kd_vehiclelibid" to="foton_vehicle" alias="LIB" link-type="inner"/> 5 <filter type="and"> 6 <condition entityname="LIB" attribute="foton_codefoton" operator="eq" value="${vehicleno}"/> 7 <condition attribute="foton_ftcontractno" operator="eq" value="${contractno}"/> 8 <filter type="or"> 9 <filter type="and"> 10 <condition attribute="foton_status" operator="eq" value="136020000"/> 11 <condition attribute="foton_addtype" operator="eq" value="136020001"/> 12 </filter> 13 <condition attribute="foton_status" operator="eq" value="136020005"/> 14 </filter> 15 </filter> 16 </entity> 17 </fetch>` 18 let queryStr = `/foton_kd_claimdetails?fetchXml=${encodeURIComponent(fetchXml.replace(/>\s+</g, '><'))}` 19 //queryStr += ` and ((foton_status eq 136020000 and foton_addtype eq 136020001) or foton_status eq 136020005)&$orderby=createdon desc,foton_nofoton asc` 20 commonUtil.queryWithUrl(queryStr, result => { 21 debugger 22 if (!result.success || !result.data || result.data.length < 1) { 23 Xrm.Utility.alertDialog("未能查询到索赔明细数据!") 24 return 25 } 26 }
