Setting up a visual hierarchy on a custom entity

hier-main

Hierarchy visualisation can be configured in MSD 365, its pretty easy. Here are the steps for it. Continue reading “Setting up a visual hierarchy on a custom entity”

FetchXML Query with NOT-IN

Tips & Trics

If you want to get all available Resources of specific Resource Category that don’t have any booking for the particular period with FetchXML,  I tried NOT-IN clause and landed in following error,

Microsoft.Crm.CrmException: Unknown Condition Operator: not- in
at Microsoft.Crm.Platform.QuerySerializationUtil.ConditionOperatorFromString(String operatorText)

But OUTER join worked for the scenario and it gave the correct result. With small change it will give the result of get all Accounts who does not have Active Contacts or Opportunity does not have tasks in a specific time period.

Here is the code snippet for it

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
 <entity name="bookableresource">
 <attribute name="name" />
 <attribute name="bookableresourceid" />
 <order attribute="name" descending="false" />
 <filter type="and">
 <condition attribute="statecode" operator="eq" value="0" />
 </filter>
 <link-entity name="bookableresourcebooking" from="resource" to="bookableresourceid" link-type="outer" alias="bx">
 <filter type="and">
 <condition attribute="starttime" operator="on-or-after" value="2018-03-01" />
 <condition attribute="endtime" operator="on-or-before" value="2018-03-31" />
 </filter>
 </link-entity>
 <link-entity name="bookableresourcecategoryassn" from="resource" to="bookableresourceid" link-type="inner" alias="by">
 <filter type="and">
 <condition attribute="resourcecategory" operator="eq" uiname="Backhoe" uitype="bookableresourcecategory" value="{798E6C02-3BBE-E711-8123-E0071B686A81}" />
 </filter>
 </link-entity>
 </entity>
</fetch>

That’s it!

Thank You! 🙂