Edit

Share via

Filter rows using FetchXml

  • Article

To set conditions on the rows of data to return, use the filter element within an entity, link-entity, or another filter element.

To set the conditions, add one or more condition elements to the filter. The containing filter type attribute determines whether all (and) or any (or) of the conditions must be met. The default is and. By nesting filter elements you can create complex filter criteria that combine criteria evaluated using and or or.

Each condition has an operator attribute to evaluate a row column value. There are many condition operator values for you to choose from.

For example, the following query returns account records where address1_city equals 'Redmond'. It uses <filter type='and'> with the eq operator.

<fetch>
   <entity name='account'>
      <attribute name='name' />
      <filter type='and'>
         <condition attribute='address1_city'
            operator='eq'
            value='Redmond' />
      </filter>
   </entity>
</fetch>

This query returns account records where address1_city equals 'Redmond', 'Seattle', or 'Bellevue'. It uses <filter type='or'> with three condition elements that each use the eq operator.

<fetch>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='address1_city' />
      <filter type='or'>
         <condition attribute='address1_city'
            operator='eq'
            value='Redmond' />
         <condition attribute='address1_city'
            operator='eq'
            value='Seattle' />
         <condition attribute='address1_city'
            operator='eq'
            value='Bellevue' />
      </filter>
   </entity>
</fetch>

The previous query can also be represented using the in operator with a single condition element. This condition contains multiple value elements to specify the values to compare to address1_city.

<fetch>
   <entity name='account'>
      <attribute name='name' />
      <attribute name='address1_city' />
      <filter type='and'>
         <condition attribute='address1_city'
            operator='in'>
            <value>Redmond</value>
            <value>Seattle</value>
            <value>Bellevue</value>
         </condition>
      </filter>
   </entity>
</fetch>

Operator parameters

Operators can require no parameters, a single parameter, or multiple parameters. The operator determines how you set the value to evaluate.

No parameters

Some operators don't require any parameters. For example, you can use the eq-userid operator to evaluate any unique identifier to determine if it matches the calling user's ID.

<condition attribute='ownerid'
   operator='eq-userid' />

Single parameter

When an operator requires a single parameter, use the value attribute to set the value to evaluate. For example, you can use the eq operator to evaluate the statecode choice column value of a record by setting the value attribute.

<condition attribute='statecode'
   operator='eq'
   value='0' />

Multiple parameters

When an operator requires multiple parameters, use the value element to specify the values to evaluate. For example, you can use the between operator to evaluate a number to determine if it is between a set of values.

<condition attribute="numberofemployees"
   operator="between">
   <value>6</value>
   <value>20</value>
</condition>

When you apply a filter within a link-entity, the filter will be applied with the join unless you configure the filter to occur after the join.

When the link-entity link-type attribute value is outer, you might want the filter to be applied after the join by setting the condition entityname attribute value. If you're using a link-entity alias, use the alias value to set the entityname attribute. Otherwise, set the entityname attribute value to the link-entity name attribute value.

For example, the following query returns contacts without a parent account, or a parent account without a fax.

<fetch>
  <entity name='contact'>
    <attribute name='fullname' />
    <filter>
      <condition entityname='a'
        attribute='fax'
        operator='null' />
    </filter>
    <link-entity name='account'
      from='accountid'
      to='parentcustomerid'
      link-type='outer'
      alias='a' />
  </entity>
</fetch>

Filter on column values in the same row

You can create filters that compare columns on values in the same row using the valueof attribute. For example, if you want to find any contact records where the firstname column value matches the lastname column value, you can use this query:

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

Cross table column comparisons

With FetchXML only, you can compare field values in related tables. The following example returns rows where the contact fullname column matches the account name column.

<fetch>
   <entity name='contact'>
      <attribute name='contactid' />
      <attribute name='fullname' />
      <filter type='and'>
         <condition attribute='fullname'
            operator='eq'
            valueof='acct.name' />
      </filter>
      <link-entity name='account'
         from='accountid'
         to='parentcustomerid'
         link-type='outer'
         alias='acct'>
         <attribute name='name' />
      </link-entity>
   </entity>
</fetch>

The link-entity element must use an alias attribute and the value of the valueof parameter must reference that alias and the column name in the related table.

Limitations on column comparison filters

There are limitations on these kinds of filters:

  • Condition can only use these operators:

    Operator Description
    eq The values are compared for equality.
    ne The two values are not equal.
    gt The value is greater than the compared value.
    ge The value is greater than or equal to the compared value.
    lt The value is less than the compared value.
    le The value is less than or equal to the compared value.

  • Only two columns can be compared at a time

  • Extended condition operations aren't supported. For example: valueof='amount'+ 100

  • The columns must be the same type. For example: You can't compare a string value with a number value

To filter on values in related records without returning those values, use a link-entity element within the filter element with one of the following link-type attributes:

Name     Description
any Use this within a filter element. Restricts results to parent rows with any matching rows in the linked entity.
not any Use this within a filter element. Restricts results to parent rows with no matching rows in the linked entity.
all Use this within a filter element. Restricts results to parent rows where rows with matching from column value exist in the link entity but none of those matching rows satisfy the additional filters defined for this link entity. You need to invert the additional filters to find parent rows where every matching link entity row satisfies some additional criteria.
not all Use this within a filter element. Restricts results to parent rows with any matching rows in the linked entity. This link type is equivalent to any despite the name.

When you use these link types inside of a filter element, these filters are child conditions following the behavior defined by the type attribute of the parent filter.

Filters using these types return the parent row at most once even if multiple matching rows exist in the link entity. They don't allow returning column values from the link entity rows.

The following examples demonstrate filtering on values of related records. These examples include the equivalent SQL statements to help explain the behavior.

This query uses a filter of type or with a child link-entity of type any to return records in contact that:

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <filter type='or'>
         <link-entity name='account'
            from='primarycontactid'
            to='contactid'
            link-type='any'>
            <filter type='and'>
               <condition attribute='name'
                  operator='eq'
                  value='Contoso' />
            </filter>
         </link-entity>
         <condition attribute='statecode'
            operator='eq'
            value='1' />
      </filter>
   </entity>
</fetch>

This query uses the not any link type to return records from the contact table that is not referenced by the PrimaryContactId lookup column of any account record that has its Name column equal to 'Contoso'. The contact record might still be referenced by account records with other Name column values.

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <filter type='and'>
         <link-entity name='account'
            from='primarycontactid'
            to='contactid'
            link-type='not any'>
            <filter type='and'>
               <condition attribute='name'
                  operator='eq'
                  value='Contoso' />
            </filter>
         </link-entity>
      </filter>
   </entity>
</fetch>

Note

The meaning of all and not all link types is the opposite of what the names might imply, and they are typically used with inverted filters:

  • A link entity of type not all is equivalent to any and returns parent records that have link entity records matching the filters.
  • A link entity of type all returns parent records when some link entity records with a matching from column value exist but none of those link entity rows satisfy the additional filters defined inside of the link-entity element.

This query uses a link-entity of type not all to return records from the contact table that are referenced by the PrimaryContactId lookup column of at least one account record that has its Name column equal to 'Contoso':

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <filter type='and'>
         <link-entity name='account'
            from='primarycontactid'
            to='contactid'
            link-type='not all'>
            <filter type='and'>
               <condition attribute='name'
                  operator='eq'
                  value='Contoso' />
            </filter>
         </link-entity>
      </filter>
   </entity>
</fetch>

This query uses a link-entity of type all to return records from the contact table that are referenced by the PrimaryContactId lookup column of some account record, but none of those account records have their Name column equal to 'Contoso':

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <filter type='and'>
         <link-entity name='account'
            from='primarycontactid'
            to='contactid'
            link-type='all'>
            <filter type='and'>
               <condition attribute='name'
                  operator='eq'
                  value='Contoso' />
            </filter>
         </link-entity>
      </filter>
   </entity>
</fetch>

Condition limits

You can include no more than 500 total condition and link-entity elements in a FetchXml query. Otherwise, you see this error:

Name: TooManyConditionsInQuery
Code: 0x8004430C
Number: -2147204340
Message: Number of conditions in query exceeded maximum limit.

You need to reduce the number of conditions to execute the query. You might be able to reduce the number of conditions by using the in operator that can be used with numbers, unique identifiers, and strings up to 850 characters.

Next steps

Learn how to page results.

Page results