The following are valid expressions that can be used in the "Criteria" column of the conditional formats. %1, %2, etc are placeholders representing fields.

In IQ you would replace %1 by [Notes] for example

 

The supported conversion unary operators are:

• type (unary operator) retrieves the type of the object. For instance type(%1) = 8 specifies the cells ( on the column 1 ) that contains string values. 

  Here's few predefined types:

  • 0 - empty ( not initialized )
  • 1 - null
  • 2 - short
  • 3 - long
  • 4 - float
  • 5 - double
  • 6 - currency
  • 7 - date
  • 8 - string
  • 9 - object
  • 10 - error
  • 11 - boolean
  • 12 - variant
  • 13 - any
  • 14 - decimal
  • 16 - char
  • 17 - byte
  • 18 - unsigned short 
  • 19 - unsigned long
  • 20 - long on 64 bits 
  • 21 - unsigned long on 64 bites
• str (unary operator) converts the expression to a string. The str operator converts the expression to a string. For instance, the str(-12.54) returns the string "-12.54".
• dbl (unary operator) converts the expression to a number. The dbl operator converts the expression to a number. For instance, the dbl("12.54") returns 12.54
• date (unary operator) converts the expression to a date, based on your regional settings. For instance, the date(``) gets the current date-time, while the date("01/01/2001") returns #1/1/2001#
• dateS (unary operator) converts the string expression to a date using the format MM/DD/YYYY HH:MM:SS. For instance, the dateS("01/01/2001 14:00:00") returns #1/1/2001 14:00:00#

Other known operators for numbers are:

• int (unary operator) retrieves the integer part of the number. For instance, the int(12.54) returns 12
• round (unary operator) rounds the number ie 1.2 gets 1, since 1.8 gets 2. For instance, the round(12.54) returns 13
• floor (unary operator) returns the largest number with no fraction part that is not greater than the value of its argument. For instance, the floor(12.54) returns 12
• abs (unary operator) retrieves the absolute part of the number ie -1 gets 1, 2 gets 2. For instance, the abs(-12.54) returns 12.54
• currency (unary operator) formats the giving number as a currency string, as indicated by the control panel. For instance, currency(value) displays the value using the current format for the currency ie, 1000 gets displayed as $1,000.00, for US format.
• value format 'flags' (binary operator) formats the value with specified flags. If flags is empty, the number is displayed as shown in the field "Number" in the "Regional and Language Options" from the Control Panel. For instance the 1000 format '' displays 1,000.00 for English format, while 1.000,00 is displayed for German format. 1000 format '2|.|3|,' will always displays 1,000.00 no matter of settings in the control panel. If formatting the number fails for some invalid parameter, the value is displayed with no formatting. 

  The ' flags' for format operator is a list of values separated by | character such as 'NumDigits|DecimalSep|Grouping|ThousandSep|NegativeOrder|LeadingZero' with the following meanings: 

  • NumDigits - specifies the number of fractional digits, If the flag is missing, the field "No. of digits after decimal" from "Regional and Language Options" is using.
  • DecimalSep - specifies the decimal separator. If the flag is missing, the field "Decimal symbol" from "Regional and Language Options" is using.
  • Grouping - indicates the number of digits in each group of numbers to the left of the decimal separator. Values in the range 0 through 9 and 32 are valid. The most significant grouping digit indicates the number of digits in the least significant group immediately to the left of the decimal separator. Each subsequent grouping digit indicates the next significant group of digits to the left of the previous group. If the last value supplied is not 0, the remaining groups repeat the last group. Typical examples of settings for this member are: 0 to group digits as in 123456789.00; 3 to group digits as in 123,456,789.00; and 32 to group digits as in 12,34,56,789.00. If the flag is missing, the field "Digit grouping" from "Regional and Language Options" indicates the grouping flag.
  • ThousandSep - specifies the thousand separator. If the flag is missing, the field "Digit grouping symbol" from "Regional and Language Options" is using.
  • NegativeOrder - indicates the negative number mode. If the flag is missing, the field "Negative number format" from "Regional and Language Options" is using. The valid values are 0, 1, 2, 3 and 4 with the following meanings:
    • 0 - Left parenthesis, number, right parenthesis; for example, (1.1) 
    • 1 - Negative sign, number; for example, -1.1
    • 2 - Negative sign, space, number; for example, - 1.1
    • 3 - Number, negative sign; for example, 1.1-
    • 4 - Number, space, negative sign; for example, 1.1 -
  • LeadingZero - indicates if leading zeros should be used in decimal fields.  If the flag is missing, the field "Display leading zeros" from "Regional and Language Options" is using. The valid values are 0, 1

Other known operators for strings are:

• len (unary operator) retrieves the number of characters in the string. For instance, the len("Mihai") returns 5.
• lower (unary operator) returns a string expression in lowercase letters. For instance, the lower("MIHAI") returns "mihai"
• upper (unary operator) returns a string expression in uppercase letters. For instance, the upper("mihai") returns "MIHAI"
• proper (unary operator) returns from a character expression a string capitalized as appropriate for proper names. For instance, the proper("mihai") returns "Mihai"
• ltrim (unary operator) removes spaces on the left side of a string. For instance, the ltrim(" mihai") returns "mihai"
• rtrim (unary operator) removes spaces on the right side of a string. For instance, the rtrim("mihai ") returns "mihai"
• trim (unary operator) removes spaces on both sides of a string. For instance, the trim(" mihai ") returns "mihai"
• reverse (unary operator) reverses the order of the characters in the string a. For instance, the reverse("Mihai") returns "iahiM"
• startwith (binary operator) specifies whether a string starts with specified string ( 0 if not found, -1 if found ). For instance "Mihai" startwith "Mi" returns -1
• endwith (binary operator) specifies whether a string ends with specified string ( 0 if not found, -1 if found ). For instance "Mihai" endwith "ai" returns -1
• contains (binary operator) specifies whether a string contains another specified string ( 0 if not found, -1 if found ). For instance "Mihai" contains "ha" returns -1
• left (binary operator) retrieves the left part of the string. For instance "Mihai" left 2 returns "Mi".
• right (binary operator) retrieves the right part of the string. For instance "Mihai" right 2 returns "ai"
• a lfind b (binary operator) The a lfind b (binary operator) searches the first occurrence of the string b within string a, and returns -1 if not found, or the position of the result ( zero-index ). For instance "ABCABC" lfind "C" returns 2
• a rfind b (binary operator)  The a rfind b (binary operator) searches the last occurrence of the string b within string a, and returns -1 if not found, or the position of the result ( zero-index ). For instance "ABCABC" rfind "C" returns 5.
• a mid b (binary operator) retrieves the middle part of the string a starting from b ( 1 means first position, and so on ). For instance "Mihai" mid 2 returns "ihai"
• a count b (binary operator) retrieves the number of occurrences of the b in a. For instance "Mihai" count "i" returns 2.
• a replace b with c (double binary operator) replaces in a the b with c, and gets the result. For instance, the "Mihai" replace "i" with "" returns "Mha" string, as it replaces all "i" with nothing.
• a split b, splits the a using the separator b, and returns an array. For instance, the weekday(value) array 'Sun Mon Thu Wed Thu Fri Sat' split ' ' gets the weekday as string. This operator can be used with the array. 

Other known operators for dates are:

• time (unary operator) retrieves the time of the date in string format, as specified in the control's panel. For instance, the time(#1/1/2001 13:00#) returns "1:00:00 PM"
• timeF (unary operator) retrieves the time of the date in string format, as "HH:MM:SS". For instance, the timeF(#1/1/2001 13:00#) returns "13:00:00"
• shortdate (unary operator) formats a date as a date string using the short date format, as specified in the control's panel. For instance, the shortdate(#1/1/2001 13:00#) returns "1/1/2001"
• shortdateF (unary operator) formats a date as a date string using the "MM/DD/YYYY" format. For instance, the shortdateF(#1/1/2001 13:00#) returns "01/01/2001"
• dateF (unary operator) converts the date expression to a string expression in "MM/DD/YYYY HH:MM:SS" format. For instance, the dateF(#01/01/2001 14:00:00#) returns #01/01/2001 14:00:00#
• longdate (unary operator) formats a date as a date string using the long date format, as specified in the control's panel. For instance, the longdate(#1/1/2001 13:00#) returns "Monday, January 01, 2001"
• year (unary operator) retrieves the year of the date (100,...,9999). For instance, the year(#12/31/1971 13:14:15#) returns 1971
• month (unary operator) retrieves the month of the date ( 1, 2,...,12 ). For instance, the month(#12/31/1971 13:14:15#) returns 12.
• day (unary operator) retrieves the day of the date ( 1, 2,...,31 ). For instance, the day(#12/31/1971 13:14:15#) returns 31
• yearday (unary operator) retrieves the number of the day in the year, or the days since January 1st ( 0, 1,...,365 ). For instance, the yearday(#12/31/1971 13:14:15#) returns 365
• weekday (unary operator) retrieves the number of days since Sunday ( 0 - Sunday, 1 - Monday,..., 6 - Saturday ). For instance, the weekday(#12/31/1971 13:14:15#) returns 5.
• hour (unary operator) retrieves the hour of the date ( 0, 1, ..., 23 ). For instance, the hour(#12/31/1971 13:14:15#) returns 13
• min (unary operator) retrieves the minute of the date ( 0, 1, ..., 59 ). For instance, the min(#12/31/1971 13:14:15#) returns 14
• sec (unary operator) retrieves the second of the date ( 0, 1, ..., 59 ). For instance, the sec(#12/31/1971 13:14:15#) returns 15

The expression supports also immediate if ( similar with iif in visual basic, or ? : in C++ ) ie cond ? value_true : value_false, which means that once that cond is true the value_true is used, else the value_false is used. Also, it supports variables, up to 10 from 0 to 9. For instance, 0:="Abc" means that in the variable 0 is "Abc", and =:0 means retrieves the value of the variable 0. For instance, the "len(%0) ? ( 0:=(%1+%2) ? currency(=:0) else `` ) : ``" gets the sum between second and third column in currency format if it is not zero, and only if the first column is not empty. As you can see you can use the variables to avoid computing several times the same thing ( in this case the sum %1 and %2 .

Samples:

1. "1", highlights all cells or items. Use this form, when you need to highlight all cells or items in the column or control. 
2. "%0 >= 0", highlights the cells or items, when the cells in the first column have the value greater or equal with zero
3. "%0 = 1 and %1 = 0", highlights the cells or items, when the cells in the first column have the value equal with 0, and the cells in the second column have the value equal with 0 
4. "%0+%1>%2", highlights the cells or the items, when the sum between first two columns is greater than the value in the third column
5. "%0+%1 > %2+%3", highlights the cells or items, when the sum between first two columns is greater than the sum between third and forth column.
6. "%0+%1 >= 0 and (%2+%3)/2 < %4-5", highlights the cells or the items, when the sum between first two columns is greater than 0 and the half of the sum between third and forth columns is less than fifth column minus 5.
7. "%0 startwith 'A'" specifies the cells that starts with A
8. "%0 endwith 'Bc'" specifies the cells that ends with Bc
9. "%0 contains 'aBc'" specifies the cells that contains the aBc string
10. "lower(%0) contains 'abc'" specifies the cells that contains the abc, AbC, ABC, and so on
11. "upper(%0)'" retrieves the uppercase string
12. "len(%0)>0'" specifies the not blanks cells
13. "len %0 = 0'" specifies the blanks cells
     

 2. Valid Formats