Following are the available filtering operators and methods. Filtering method availability will vary based on the data type.
Null versus Blank:
Null is an absence of a value. A Blank string is a value but is just empty. Null is special to a database. Null has no bounds, it can be used for string, integer, date, etc. fields in a database. The behavior of searches using Null or Blank may experience different results. Blank fields may also be the result of a field that once had a value that was deleted but was not made Null.
| Operator | Method | Description |
|---|---|---|
| Available for All | ... | Selected when no filter is used. |
| Available for All | Blank | Is the field blank? |
| Available for All | Not Blank | Is the field not blank? |
| Available for Some | Null | If available, is the field null? (zero-length field) |
| Available for Some |
Not Null | If available, is the field not null? (zero-length field) |
| Comparison | Is Less Than | Displays only items whose values are less than the input values. |
| Comparison | Is Greater Than | Displays only items whose values are greater than the input values. |
| Comparison | Between | Displays only items whose values lie between and including the input values. |
| Comparison | Isn't Less Than | Displays only items whose values are not less than the input value. |
| Comparison | Isn't Greater Than | Displays only items whose values are not greater than the input value. |
| Comparison | Isn't Between | Displays only items whose values are not between and not including the input values. |
| Equivalence | Equals (Manual entry) |
Manual text entry of the value(s). When followed by the [Enter Key], nearest data match will auto complete. Supports Single or Multiple values. |
| Equivalence | Equals (Manual entry - No Auto Complete) |
Manual text entry of the value(s). When followed by the [Enter Key], will record value exactly as typed. Supports Single or Multiple values. |
| Equivalence | Equals(Select) | Select value(s) from list. Supports Single or Multiple values. |
| Equivalence | Equals(Popup) | Select value(s) from a popup list with checkboxes. Automatically supports Multiple values. |
| Equivalence | Equals(Tree) | Select value(s) from a vertical list of checkboxes. Very similar to Equals (Checkbox). Automatically supports Multiple values. |
| Equivalence | Equals(Checkbox) | Select value(s) from a vertical list of checkboxes. Very similar to Equals (Tree). Automatically supports Multiple values. |
| Equivalence | Not Equal (Manual entry) |
The opposite of Equals (Manual entry). |
| Equivalence | Not Equal (Manual entry - No Auto Complete) |
The opposite of Equals (Manual entry - No Auto Complete). |
| Equivalence | Not Equal (Select) | The opposite of Equals (Select). |
| Equivalence | Not Equal (Popup) | The opposite of Equals (Popup). |
| Equivalence | Not Equal (Tree) | The opposite of Equals (Tree). |
| Equivalence | Not Equal (Checkbox) | The opposite of Equals (Checkbox). |
| Field Comparison | Is Less Than (Field) | Allows you to compare one field to another. Determines if the first field is less than the second field. |
| Field Comparison | Is Greater Than (Field) | Allows you to see if one field is greater than another field. |
| Field Comparison | Equals (Field) | Determines if one field value equals another. This is useful for joining tables on multiple fields. |
| Field Comparison | Not Equals (Field) | The opposite of Equals (Field). |
| Date/Time | Between (Date) | Allows you to select two dates from a popup to filter on. This filter is inclusive - any dates equal to or between the specified values will be included. |
| Date/Time | Between (Time) | Allows you to select two times from a popup to filter on. This filter is inclusive - any times equal to or between the specified values will be included. |
| Date/Time | Between (Date and Time) | Allows you to select two dates/times from a popup to filter on. This filter is inclusive - any dates/times equal to or between the specified values will be included. |
| Date/Time | Not Between (Date) | Allows you to select two dates from a popup to filter on. This filter is exclusive - any dates outside the specified values will be included. |
| Date/Time | Not Between (Time) | Allows you to select two times from a popup to filter on. This filter is exclusive - any times outside the specified values will be included. |
| Date/Time | Not Between (Date and Time) | Allows you to select two dates/times from a popup to filter on. This filter is exclusive - any dates/times outside the specified values will be included. |
| Date/Time | Equal (Date) | Allows you to select a date from a popup to filter on. Any dates equal to the specified value will be included. |
| Date/Time | Equal (Time) | Allows you to select a time from a popup to filter on. Any time equal to the specified value will be included. |
| Date/Time | Equal (Date and Time) | Allows you to select a date/time from a popup to filter on. Any date/time equal to the specified value will be included. |
| Date/Time | Not Equal (Date) | Allows you to select a date from a popup to filter on. Any date not equal to the specified values will be included. |
| Date/Time | Not Equal (Time) | Allows you to select a time from a popup to filter on. Any time not equal to the specified values will be included. |
| Date/Time | Not Equal (Date and Time) | Allows you to select a date/time from a popup to filter on. Any date/time not equal to the specified values will be included. |
| Date/Time | In Time Period | Allows you to select from a list of time periods to filter on. All selections are based on the current date in relation to the filter date selected. Fiscal Year options are based on a 07/01 to 06/30 year. |
| Date/Time | Less Than Days Old | Allows a single numeric entry to represent the days and uses that as a filter. |
| Date/Time | Greater Than Days Old | The opposite of Less Than Days Old |
| Date/Time | Equals Days Old | Filters on records that are only equal to as many days old as the specified value. |
| String | Like | Determines if the value of the field contains the given text. In built-in filters, the filter label is commonly displayed as (-a-) for anywhere. |
| String | Begins With | Determines if the value of the field begins with the given text. In built-in filters, the filter label is commonly displayed as (s--) for starting with. |
| String | Ends With | Determines if the value of the field ends with the given text. In built-in filters, the filter label is commonly displayed as (--e) for ending with. |
| String | Not Like | The opposite of Like. |
| Boolean | True | Determines if the value in the field is "true". Most boolean fields in InSight result in Yes/No values. |
| Boolean | False | Determines if the value in the field is "false". Values other than the number 1 and the text "(T|t)rue" will be interpreted as false. Most boolean fields in InSight result in Yes/No values. |
Rick Stern
Comments