Operators and Comparisons

Operators and comparisons are represented by keywords or special characters. Use them to filter and refine your formula criteria.

Operators are not case-sensitive.
These sample formulas refer to fields that may not exist in your data dictionary.

Operator or Comparison	Description	Examples
AND	Matches multiple criteria. Must meet all criteria.	Select all males in the first grade. Sex=Male AND Grade=01
OR	Matches multiple criteria where any of the criteria will be met.	Select all profiles in the first or second grades. Grade=01 OR Grade=02
NOT	Excludes criteria.	Select all profiles, excluding all first-grade males. NOT (Sex=Male and Grade=01)
IN	Determines whether a value is included in a specific set of values.	Select students who are in any of grades 3, 4, 5, 6, or 7. Grade IN (03, 04, 05, 06, 07)
LIKE	Commonly used to search character fields using wildcards so that the exact text does not need to be specified. Advanced uses allow comparisons of a character value to a pattern that can utilize pattern matching methods. To match zero or more characters, use a percent sign character (%) as a multiple-character wildcard. To match any single character, use an underscore character (_) as a single-character wildcard. To match a single character within a specified range of characters, specify the range, such as [0-9], [A-E]. To match a single character NOT within a specified range of characters, specify the range, such as [^0-9], [^A-E]. To match a single character within a specified set of characters, specify the set within brackets, such as [012345] or [abcdef012345]. To match a single character NOT within a specified set of characters, specify the set within brackets, such as [^012345] or [^abcdef012345].	LastName starts with the letter M. LastName LIKE "M%" LastName contains the word John anywhere in the name. LastName LIKE "%John%" ZipCode has exactly five numeric characters. ZipCode LIKE "[0-9][0-9][0-9][0-9][0-9]"
IS NOT EMPTY	Determines if a field has a value.	The FirstName contains a value. FirstName IS NOT EMPTY
IS EMPTY	Determines if a field is not populated.	The FirstName field is not populated. FirstName IS EMPTY
. (period)	Access the value of an additional field from a profile or record that the current field references. This is only supported for certain fields (staff, location, keyword) that refer to additional data.	The home school is in the district with ID=of 1000_132 HomeSchool.District=1000_132 Period operators can be chained together. The school that the case manager works is in the district with ID=of 1000_132 CaseManager.WorksAt.Distict=1000_132
< (less than) < (greater than) = (equals)	Use the less than (<) operator to search for values that are less than the specified value and the greater than (>) operator to search for values that are greater than the specified value. Use the equals (=) operator to search for values that equal the specified value. These operators can be used in conjunction with each other. Less than or equal to: <= Greater than or equal to: >= Neither less than or greater than; does not equal: <>	The date of birth is before June 30, 2010. BirthDate < 6/30/2010 The date of birth is after June 30, 2010. BirthDate > 6/30/2010 The date of birth equals June 30, 2010. BirthDate = 6/30/2010 The date of birth is between June 30, 2010, and August 15, 2010. BirthDate > 6/30/2010 AND BirthDate < 8/15/2010 DaysAbsent is less than or equal to 5 DaysAbsent<=5 DaysAbsent is greater than or equal to 5 DaysAbsent>=5 The address is not blank. Address1 <> ""
+ (plus sign)	Combines two or more character values.	Returns a string formatted as "LastName, FirstName". For example, "Smith, John." LastName + ',' + FirstName