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! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s