Diving into Column Comparison with Conditional Operators in FetchXML for Dynamics 365

Fetching data is like painting, and with FetchXML in Dynamics 365, we have a lot of colours to choose from. One cool feature is comparing columns straight away using different rules. Today, we’ll explain how to use these rules properly.

Instead of comparing a field to a set value, column comparison pits one field against another. This can be useful for spotting inconsistencies, tracking changes, or even identifying trends.

Limitations: Here are some constraints when using column comparison in Dataverse:

You can compare columns using following condition operators in FetchXML, Web API, or the .NET SDK.

• eq: Equal to
• ne: Not equal to
• gt: Greater than
• ge: Greater than or equal to
• lt: Less than
• le: Less than or equal to

Practical Examples

a. Equal (eq)

To identify accounts where the primary contact’s first name is the same as the last name:

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

b. Not Equal (ne)

Find opportunities where the estimated value is different from the actual value:

<fetch>
    <entity name="opportunity">
        <filter type="and">
            <condition attribute="estimatedvalue" operator="ne" valueof="actualvalue" />
        </filter>
    </entity>
</fetch>

c. Greater Than (gt)

Uncover products where the production cost exceeds the retail price (potentially a data entry error):

<fetch>
    <entity name="product">
        <filter type="and">
            <condition attribute="productioncost" operator="gt" valueof="retailprice" />
        </filter>
    </entity>
</fetch>

d. Greater Than or Equal To (ge)

Find employees whose overtime hours are equal to or more than regular hours:

<fetch>
    <entity name="employee">
        <filter type="and">
            <condition attribute="overtimehours" operator="ge" valueof="regularhours" />
        </filter>
    </entity>
</fetch>

e. Less Than (lt)

Identify orders where the shipped quantity is less than the ordered quantity:

<fetch>
    <entity name="salesorder">
        <filter type="and">
            <condition attribute="shippedquantity" operator="lt" valueof="orderedquantity" />
        </filter>
    </entity>
</fetch>

f. Less Than or Equal To (le)

Check for products where discounts are less than or equal to 10% of the product price:

<fetch>
    <entity name="product">
        <filter type="and">
            <condition attribute="discount" operator="le" valueof="price" factor="0.1" />
        </filter>
    </entity>
</fetch>