Edit

condition operator values

  • Article

To learn how to use these values, see Filter rows using FetchXml.

Operator Description Data Types
above Returns all records in referenced record's hierarchical ancestry line. Hierarchical
begins-with The string occurs at the beginning of another string. String
between The value is between two values. Number
Datetime
contain-values The choice value is one of the specified values. Choice
ends-with The string ends with another string. String
eq The values are compared for equality. Choice
Datetime
Hierarchical
Number
Owner
String
Unique Identifier
eq-businessid The value is equal to the specified business ID. Unique Identifier
eq-or-above Returns the referenced record and all records above it in the hierarchy. Hierarchical
eq-or-under Returns the referenced record and all child records below it in the hierarchy. Hierarchical
eq-userid The value is equal to the specified user ID. Unique Identifier
eq-userlanguage The value is equal to the language for the user. Number
eq-useroruserhierarchy When hierarchical security models are used, Equals current user or their reporting hierarchy. Hierarchical
eq-useroruserhierarchyandteams When hierarchical security models are used, Equals current user and his teams or their reporting hierarchy and their teams. Hierarchical
eq-useroruserteams The record is owned by a user or teams that the user is a member of. Owner
eq-userteams The record is owned by a user or teams that the user is a member of. Owner
ge The value is greater than or equal to the compared value. Number
Datetime
String
gt The value is greater than the compared value. Number
Datetime
String
in The value exists in a list of values. Choice
Number
Owner
String
Unique Identifier
in-fiscal-period The value is within the specified fiscal period. Datetime
in-fiscal-period-and-year The value is within the specified fiscal period and year. Datetime
in-fiscal-year The value is within the specified year. Datetime
in-or-after-fiscal-period-and-year The value is within or after the specified fiscal period and year. Datetime
in-or-before-fiscal-period-and-year The value is within or before the specified fiscal period and year. Datetime
last-fiscal-period The value is within the previous fiscal period. Datetime
last-fiscal-year The value is within the previous fiscal year. Datetime
last-month The value is within the previous month including first day of the last month and last day of the last month. Datetime
last-seven-days The value is within the previous seven days including today. Datetime
last-week The value is within the previous week including Sunday through Saturday. Datetime
last-x-days The value is within the previous specified number of days. Datetime
last-x-fiscal-periods The value is within the previous specified number of fiscal periods. Datetime
last-x-fiscal-years The value is within the previous specified number of fiscal periods. Datetime
last-x-hours The value is within the previous specified number of hours. Datetime
last-x-months The value is within the previous specified number of months. Datetime
last-x-weeks The value is within the previous specified number of weeks. Datetime
last-x-years The value is within the previous specified number of years. Datetime
last-year The value is within the previous year. Datetime
le The value is less than or equal to the compared value. Number
Datetime
String
like The character string is matched to the specified pattern. String
lt The value is less than the compared value. Number
Datetime
String
ne The two values are not equal. Choice
Datetime
Hierarchical
Number
Owner
String
Unique Identifier
ne-businessid The value is not equal to the specified business ID. Unique Identifier
ne-userid The value is not equal to the specified user ID. Unique Identifier
neq Deprecated. Use ne instead. Remains for backward compatability only. N/A
next-fiscal-period The value is within the next fiscal period. Datetime
next-fiscal-year The value is within the next fiscal year. Datetime
next-month The value is within the next month. Datetime
next-seven-days The value is within the next seven days. Datetime
next-week The value is within the next week. Datetime
next-x-days The value is within the next specified number of days. Datetime
next-x-fiscal-periods The value is within the next specified number of fiscal periods. Datetime
next-x-fiscal-years The value is within the next specified number of fiscal years. Datetime
next-x-hours The value is within the next specified number of hours. Datetime
next-x-months The value is within the next specified number of months. Datetime
next-x-weeks The value is within the next specified number of weeks. Datetime
next-x-years The value is within the next specified number of years. Datetime
next-year The value is within the next X years. Datetime
not-begin-with The string does not begin with another string. String
not-between The value is not between two values. Number
Datetime
not-contain-values The choice value is not one of the specified values. Choice
not-end-with The string does not end with another string. String
not-in The given value is not matched to a value in a subquery or a list. Number
not-like The character string does not match the specified pattern. String
not-null The value is not null. Choice
Datetime
Hierarchical
Number
Owner
String
Unique Identifier
not-under Returns all records not below the referenced record in the hierarchy. Hierarchical
null The value is null. Choice
Datetime
Hierarchical
Number
Owner
String
Unique Identifier
olderthan-x-days The value is older than the specified number of days. Datetime
olderthan-x-hours The value is older than the specified number of hours. Datetime
olderthan-x-minutes The value is older than the specified number of minutes. Datetime
olderthan-x-months The value is older than the specified number of months. Datetime
olderthan-x-weeks The value is older than the specified number of weeks. Datetime
olderthan-x-years The value is older than the specified number of years. Datetime
on The value is on a specified date. Datetime
on-or-after The value is on or after a specified date. Datetime
on-or-before The value is on or before a specified date. Datetime
this-fiscal-period The value is within the current fiscal period. Datetime
this-fiscal-year The value is within the current fiscal year. Datetime
this-month The value is within the current month. Datetime
this-week The value is within the current week. Datetime
this-year The value is within the current year. Datetime
today The value equals today's date. Datetime
tomorrow The value equals tomorrow's date. Datetime
under Returns all child records below the referenced record in the hierarchy. Hierarchical
yesterday The value equals yesterday's date. Datetime

Back to top

By Data type

This section groups operators by the type of data they can be used with.

Choice data

Use the following operators in conditions using choice values.

Operator Description
contain-values The choice value is one of the specified values.
eq The values are compared for equality.
in The value exists in a list of values.
ne The two values are not equal.
not-contain-values The choice value is not one of the specified values.
not-null The value is not null.
null The value is null.

Datetime data

Use the following operators in conditions using date and time values.

Note

When a column is configured with DateOnly behavior you cannot use the operators that apply to hours and minutes. Learn more about the behavior of date and time columns.

The behavior of some operators depend on the fiscal year settings for the environment. Learn more about fiscal year settings

Operator Description
between The value is between two values.
eq The values are compared for equality.
gt The value is greater than the compared value.
ge The value is greater than or equal to the compared value.
in-fiscal-period-and-year The value is within the specified fiscal period and year.
in-fiscal-period The value is within the specified fiscal period.
in-fiscal-year The value is within the specified year.
in-or-after-fiscal-period-and-year The value is within or after the specified fiscal period and year.
in-or-before-fiscal-period-and-year The value is within or before the specified fiscal period and year.
last-fiscal-period The value is within the previous fiscal period.
last-fiscal-year The value is within the previous fiscal year.
last-month The value is within the previous month including first day of the last month and last day of the last month.
last-seven-days The value is within the previous seven days including today.
last-week The value is within the previous week including Sunday through Saturday.
last-x-days The value is within the previous specified number of days.
last-x-fiscal-periods The value is within the previous specified number of fiscal periods.
last-x-fiscal-years The value is within the previous specified number of fiscal periods.
last-x-hours The value is within the previous specified number of hours.
last-x-months The value is within the previous specified number of months.
last-x-weeks The value is within the previous specified number of weeks.
last-x-years The value is within the previous specified number of years.
last-year The value is within the previous year.
le The value is less than or equal to the compared value.
lt The value is less than the compared value.
next-fiscal-period The value is within the next fiscal period.
next-fiscal-year The value is within the next fiscal year.
next-month The value is within the next month.
next-seven-days The value is within the next seven days.
next-week The value is within the next week.
next-x-days The value is within the next specified number of days.
next-x-fiscal-periods The value is within the next specified number of fiscal periods.
next-x-fiscal-years The value is within the next specified number of fiscal years.
next-x-hours The value is within the next specified number of hours.
next-x-months The value is within the next specified number of months.
next-x-weeks The value is within the next specified number of weeks.
next-x-years The value is within the next specified number of years.
next-year The value is within the next X years.
not-between The value is not between two values.
not-null The value is not null.
null The value is null.
olderthan-x-days The value is older than the specified number of days.
olderthan-x-hours The value is older than the specified number of hours.
olderthan-x-minutes The value is older than the specified number of minutes.
olderthan-x-months The value is older than the specified number of months.
olderthan-x-weeks The value is older than the specified number of weeks.
olderthan-x-years The value is older than the specified number of years.
on-or-after The value is on or after a specified date.
on-or-before The value is on or before a specified date.
on The value is on a specified date.
this-fiscal-period The value is within the current fiscal period.
this-fiscal-year The value is within the current fiscal year.
this-month The value is within the current month.
this-week The value is within the current week.
this-year The value is within the current year.
today The value equals today's date.
tomorrow The value equals tomorrow's date.
yesterday The value equals yesterday's date.

Hierarchical data

Use the following operators in conditions using hierarchical data. Learn more about querying hierarchical data.

Operator Description
above Returns all records in referenced record's hierarchical ancestry line.
eq-or-above Returns the referenced record and all records above it in the hierarchy.
eq-or-under Returns the referenced record and all child records below it in the hierarchy.
eq-useroruserhierarchy When hierarchical security models are used, Equals current user or their reporting hierarchy.
eq-useroruserhierarchyandteams When hierarchical security models are used, Equals current user and his teams or their reporting hierarchy and their teams.
not-under Returns all records not below the referenced record in the hierarchy.
under Returns all child records below the referenced record in the hierarchy.

Number data

Use the following operators in conditions using numeric values.

Operator Description
between The value is between two values.
eq-userlanguage The value is equal to the language for the user.
eq The values are compared for equality.
ge The value is greater than or equal to the compared value.
gt The value is greater than the compared value.
in The value exists in a list of values.
le The value is less than or equal to the compared value.
lt The value is less than the compared value.
ne The two values are not equal.
not-between The value is not between two values.
not-in The given value is not matched to a value in a subquery or a list.
not-null The value is not null.
null The value is null.

Owner data

Use the following operators in conditions using owner values.

Operator Description
eq-useroruserteams The record is owned by a user or teams that the user is a member of.
eq-userteams The record is owned by a user or teams that the user is a member of.
eq The values are compared for equality.
in The value exists in a list of values.
ne The two values are not equal.

String data

Use the following operators in conditions using string values.

You can use wildcard characters for the following operators: like, not-like, begins-with, not-begin-with, ends-with, and not-end-with. Learn more about using wildcard characters in conditions for string values

Operator Description
begins-with The string occurs at the beginning of another string.
ends-with The string ends with another string.
eq The values are compared for equality.
ge The value is greater than or equal to the compared value.
gt The value is greater than the compared value.
in The value exists in a list of values.
le The value is less than or equal to the compared value.
like The character string is matched to the specified pattern.
lt The value is less than the compared value.
ne The two values are not equal.
not-begin-with The string does not begin with another string.
not-end-with The string does not end with another string.
not-like The character string does not match the specified pattern.
not-null The value is not null.
null The value is null.

Unique Identifier data

Use the following operators in conditions using unique identifier (GUID) values.

Operator Description
eq-businessid The value is equal to the specified business ID.
eq-userid The value is equal to the specified user ID.
eq The values are compared for equality.
in The value exists in a list of values.
ne-businessid The value is not equal to the specified business ID.
ne-userid The value is not equal to the specified user ID.
ne The two values are not equal.
not-null The value is not null.
null The value is null.

Back to top

Details

This section includes details about each of the FetchXml condition operators.

above

Returns all records in referenced record's hierarchical ancestry line.

Learn more about querying hierarchical data

Data Type: Hierarchical

begins-with

The string occurs at the beginning of another string.

You can use wildcard characters with this operator. Learn more about using wildcard characters in conditions for string values.

Data Type: String

between

The value is between two values.

This operator requires two values.

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

Data Types:

contain-values

The choice value is one of the specified values.

Data Type: Choice

ends-with

The string ends with another string.

You can use wildcard characters with this operator. Learn more about using wildcard characters in conditions for string values.

Data Type: String

eq

The values are compared for equality.

Data Types:

eq-businessid

The value is equal to the specified business ID.

Data Type: Unique Identifier

eq-or-above

Returns the referenced record and all records above it in the hierarchy.

Learn more about querying hierarchical data

Data Type: Hierarchical

eq-or-under

Returns the referenced record and all child records below it in the hierarchy.

Learn more about querying hierarchical data

Data Type: Hierarchical

eq-userid

The value is equal to the specified user ID.

No value is required for this operator.

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

Data Type: Unique Identifier

eq-userlanguage

The value is equal to the language for the user.

When a column uses the Power Apps Language type, it is an integer value that stores the Microsoft Locale ID Value for the language. The value is compared to the UserSettings.UILanguageId that represents the calling user's preferred language. These columns have AttributeMetadata.AttributeTypeName.Value equal to IntegerType and a Format value of IntegerFormat.Language.

Data Type: Number

eq-useroruserhierarchy

When hierarchical security models are used, Equals current user or their reporting hierarchy.

Learn more about querying hierarchical data

Data Type: Hierarchical

eq-useroruserhierarchyandteams

When hierarchical security models are used, Equals current user and his teams or their reporting hierarchy and their teams.

Learn more about querying hierarchical data

Data Type: Hierarchical

eq-useroruserteams

The record is owned by a user or teams that the user is a member of.

Data Type: Owner

eq-userteams

The record is owned by a user or teams that the user is a member of.

Data Type: Owner

ge

The value is greater than or equal to the compared value.

Data Types:

gt

The value is greater than the compared value.

Data Types:

in

The value exists in a list of values.

Data Types:

in-fiscal-period

The value is within the specified fiscal period.

Learn more about fiscal year settings

The following example shows a FetchXML query that finds all orders fulfilled in period three of any fiscal year, according to the organization's fiscal year settings. The fiscal period value is specified in the value column of the condition element. If the organization uses fiscal months, the query returns results from month three. If the organization uses fiscal quarters, the query returns results from quarter three. If the organization uses fiscal semesters, no results are returned; there are only two semesters, and the value supplied is therefore out of range.

<fetch>
   <entity name="order">
      <attribute name="name" />
      <filter type="and">
         <condition attribute="datefulfilled"
            operator="in-fiscal-period"
            value="3" />
      </filter>
   </entity>
</fetch>

Data Type: Datetime

in-fiscal-period-and-year

The value is within the specified fiscal period and year.

Learn more about fiscal year settings

The following example shows a FetchXML expression that finds all orders fulfilled in period three of fiscal year 2023, according to the organization's fiscal year settings. If the organization uses fiscal months, the query returns results from month three. If the organization uses fiscal quarters, the query returns results from quarter three. If the organization uses fiscal semesters, no results are returned; there are only two semesters, and the value supplied is therefore out-of-range

<fetch>
   <entity name="order">
      <attribute name="name" />
      <filter type="and">
         <condition attribute="datefulfilled"
            operator="in-fiscal-period-and-year">
            <value>3</value>
            <value>2023</value>
         </condition>
      </filter>
   </entity>
</fetch>

Data Type: Datetime

in-fiscal-year

The value is within the specified year.

Learn more about fiscal year settings

The following example shows a FetchXML expression that finds all accounts created in fiscal year 2023.

<fetch>
   <entity name="account">
      <attribute name="name" />
      <filter type="and">
         <condition attribute="createdon"
            operator="in-fiscal-year"
            value="2023" />
      </filter>
   </entity>
</fetch>

Data Type: Datetime

in-or-after-fiscal-period-and-year

The value is within or after the specified fiscal period and year.

Learn more about fiscal year settings

Data Type: Datetime

in-or-before-fiscal-period-and-year

The value is within or before the specified fiscal period and year.

Learn more about fiscal year settings

Data Type: Datetime

last-fiscal-period

The value is within the previous fiscal period.

Learn more about fiscal year settings

The following example shows a FetchXML expression that finds all orders fulfilled in the last fiscal period, according to the organization's fiscal year settings. For example, if the organization uses fiscal months, the query returns orders fulfilled in the last fiscal month. If the organization uses fiscal quarters, the query returns orders fulfilled in the last fiscal quarter. If the organization uses fiscal semesters, orders fulfilled in the last fiscal semester are returned.

<fetch>
   <entity name="order">
      <attribute name="name" />
      <filter type="and">
         <condition attribute="datefulfilled"
            operator="last-fiscal-period" />
      </filter>
   </entity>
</fetch>

Data Type: Datetime

last-fiscal-year

The value is within the previous fiscal year.

Learn more about fiscal year settings

Data Type: Datetime

last-month

The value is within the previous month including first day of the last month and last day of the last month.

Data Type: Datetime

last-seven-days

The value is within the previous seven days including today.

Data Type: Datetime

last-week

The value is within the previous week including Sunday through Saturday.

Data Type: Datetime

last-x-days

The value is within the previous specified number of days.

Data Type: Datetime

last-x-fiscal-periods

The value is within the previous specified number of fiscal periods.

Learn more about fiscal year settings

Data Type: Datetime

last-x-fiscal-years

The value is within the previous specified number of fiscal periods.

Learn more about fiscal year settings

Data Type: Datetime

last-x-hours

The value is within the previous specified number of hours.

You can't apply this operator on a column that is configured to use the DateOnly behavior. Learn more about the behavior of date and time columns

Data Type: Datetime

last-x-months

The value is within the previous specified number of months.

Data Type: Datetime

last-x-weeks

The value is within the previous specified number of weeks.

Data Type: Datetime

last-x-years

The value is within the previous specified number of years.

Data Type: Datetime

last-year

The value is within the previous year.

Data Type: Datetime

le

The value is less than or equal to the compared value.

Data Types:

like

The character string is matched to the specified pattern.

You can use wildcard characters with this operator. Learn more about using wildcard characters in conditions for string values.

Data Type: String

lt

The value is less than the compared value.

Data Types:

ne

The two values are not equal.

Data Types:

ne-businessid

The value is not equal to the specified business ID.

Data Type: Unique Identifier

ne-userid

The value is not equal to the specified user ID.

Data Type: Unique Identifier

neq

Deprecated. Use ne instead. Remains for backward compatability only.

next-fiscal-period

The value is within the next fiscal period.

Learn more about fiscal year settings

Data Type: Datetime

next-fiscal-year

The value is within the next fiscal year.

Learn more about fiscal year settings

Data Type: Datetime

next-month

The value is within the next month.

Data Type: Datetime

next-seven-days

The value is within the next seven days.

Data Type: Datetime

next-week

The value is within the next week.

Data Type: Datetime

next-x-days

The value is within the next specified number of days.

Data Type: Datetime

next-x-fiscal-periods

The value is within the next specified number of fiscal periods.

Learn more about fiscal year settings

Data Type: Datetime

next-x-fiscal-years

The value is within the next specified number of fiscal years.

Learn more about fiscal year settings

The following example shows a FetchXML expression that finds all opportunities with an estimated close date in the next three fiscal years, based on the organization's fiscal year settings. The value for x is specified in the value column of the condition element.

<fetch>
   <entity name="opportunity">
      <attribute name="name" />
      <filter type="and">
         <condition attribute="estimatedclosedate"
            operator="next-x-fiscal-years"
            value="3" />
      </filter>
   </entity>
</fetch>

Data Type: Datetime

next-x-hours

The value is within the next specified number of hours.

You can't apply this operator on a column that is configured to use the DateOnly behavior. Learn more about the behavior of date and time columns

Data Type: Datetime

next-x-months

The value is within the next specified number of months.

Data Type: Datetime

next-x-weeks

The value is within the next specified number of weeks.

Data Type: Datetime

next-x-years

The value is within the next specified number of years.

Data Type: Datetime

next-year

The value is within the next X years.

Data Type: Datetime

not-begin-with

The string does not begin with another string.

You can use wildcard characters with this operator. Learn more about using wildcard characters in conditions for string values.

Data Type: String

not-between

The value is not between two values.

Data Types:

not-contain-values

The choice value is not one of the specified values.

Data Type: Choice

not-end-with

The string does not end with another string.

You can use wildcard characters with this operator. Learn more about using wildcard characters in conditions for string values.

Data Type: String

not-in

The given value is not matched to a value in a subquery or a list.

Data Type: Number

not-like

The character string does not match the specified pattern.

You can use wildcard characters with this operator. Learn more about using wildcard characters in conditions for string values.

Data Type: String

not-null

The value is not null.

Data Types:

not-under

Returns all records not below the referenced record in the hierarchy.

Learn more about querying hierarchical data

Data Type: Hierarchical

null

The value is null.

Data Types:

olderthan-x-days

The value is older than the specified number of days.

Data Type: Datetime

olderthan-x-hours

The value is older than the specified number of hours.

You can't apply this operator on a column that is configured to use the DateOnly behavior. Learn more about the behavior of date and time columns

Data Type: Datetime

olderthan-x-minutes

The value is older than the specified number of minutes.

You can't apply this operator on a column that is configured to use the DateOnly behavior. Learn more about the behavior of date and time columns

The following example shows a FetchXML query that returns incidents that are older than 30 minutes.

<fetch>
   <entity name="incident">
      <attribute name="title" />
      <attribute name="ticketnumber" />
      <attribute name="createdon" />
      <attribute name="incidentid" />
      <filter type="and">
         <condition attribute="createdon"
            operator="olderthan-x-minutes"
            value="30" />
      </filter>
   </entity>
</fetch>

Data Type: Datetime

olderthan-x-months

The value is older than the specified number of months.

Data Type: Datetime

olderthan-x-weeks

The value is older than the specified number of weeks.

Data Type: Datetime

olderthan-x-years

The value is older than the specified number of years.

Data Type: Datetime

on

The value is on a specified date.

Data Type: Datetime

on-or-after

The value is on or after a specified date.

Data Type: Datetime

on-or-before

The value is on or before a specified date.

Data Type: Datetime

this-fiscal-period

The value is within the current fiscal period.

Learn more about fiscal year settings

Data Type: Datetime

this-fiscal-year

The value is within the current fiscal year.

Learn more about fiscal year settings

Data Type: Datetime

this-month

The value is within the current month.

Data Type: Datetime

this-week

The value is within the current week.

Data Type: Datetime

this-year

The value is within the current year.

Data Type: Datetime

today

The value equals today's date.

Data Type: Datetime

tomorrow

The value equals tomorrow's date.

Data Type: Datetime

under

Returns all child records below the referenced record in the hierarchy.

Learn more about querying hierarchical data

Data Type: Hierarchical

yesterday

The value equals yesterday's date.

Data Type: Datetime