condition operator values
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 |
By Data type
This section groups operators by the type of data they can be used with.
- Choice data
- Datetime data
- Hierarchical data
- Number data
- Owner data
- String data
- Unique Identifier data
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.
All filter conditions for string values are case insensitive.
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. |
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:
- Choice
- Number
- Unique Identifier
- Owner
- String Up to 850 characters
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