Filter Syntax

In This Topic

The following guide will help you understand how to tell FOURA what you want to search for. Remember to look at the examples included at the end of each of the sections!

Filters are used to search grids and entry forms for specific records that match your search condition. A search condition is what you need to type to tell FOURA what to search for. FOURA uses an advanced search engine that tries to "guess" what you mean. Filters are entered into the green Filter Bar located at the top of the data grid on Browse Forms, and in the Filter For option on the context menu that is displayed when you right-click on a field.

Search Data Types

It is important to consider what type of data you are searching. Every column and entry field in FOURA is handled as one of the following:

Data Type	Description	Examples
Text	Alphanumeric or special characters - Supports all search methods including Pattern Matching.	AB010 , Bob's Diner
Numeric	Any number - Supports all search methods except pattern matching. Number filters must be entered as "pure" numbers without any symbols, parentheses or commas. For example, when filtering for $1,000 you would enter 1000.	55.12 , -20
Date	A date or date+time - Supports all search methods except pattern matching. Can use Dynamic Date Names.	12/1/18 , 5/31/19
Yes/No	Yes or No - Only supports direct comparison.	Yes , No

If you attempt to search for something that does not make sense for the data type then you will get a "type mismatch" error when you click the Query button or attempt to apply the filter. For example, if you try to search a numeric field for "ABC" it won't work and you will get the error.

Search Conditions

A search condition is the text that you enter into the filter box. Search conditions must be one of the following:

Search Condition	Description / Examples
<Comparison><Expression>	Common basic comparisons. The comparison must be a supported Comparison Operator. If no comparison is entered then equals (=) will be assumed. >8/10/18 PO
[NOT] [LIKE] <Expression>	Pattern Matching. Only works for Text data types. If you enter a wildcard then pattern matching will be assumed. The word LIKE must be included when using bracketed wildcard expressions (see Pattern Matching). C* Not ERR*
[NOT] BETWEEN <Expression> AND <Expression>	Range Matching. The first expression is the start of the range and the second is the end of the range. The endpoint will be included in the search. Between 8/1/18 and 8/31/18 Between 1000 and 1500
[IS] [NOT] NULL	Null detection. Use this to find missing or blank values. NULL
[NOT] IN(<List>)	List Matching. Each item in the list must be a valid expression for the column Data Type. IN(AB010,AA999,JC010) NOT IN(RES,COM)

The expression is what you actually want to search for. The other parts of the search condition determine the type of search. Multiple search conditions can be combined using AND or OR. For example, in the Item Master you could right-click the Item Description field and enter *Air* OR *Plane* in the Filter For box to find all records with a description that contains either Air or Plane:

Search Condition	Meaning
MA010	All text values exactly equal to MA010
A*	Any text values starting with A
>100 and <1000	All numeric values larger than 100 and less than 1000
Between 100 and 200	All numeric values between 100 and 200, including 100 and 200
Between A and Cz	All text values starting with A through Cz
<100 or >200	All numeric values either less than 100 or greater than 200
Not C*	All text values that do NOT start with C
0	When used for a number, all numeric values equal to 0. When used for a Yes/No, all UNCHECKED items.
1	When used for a number, all numeric values equal to 1. When used for a Yes/No, all CHECKED items.
10/15/2018	Only the date October 15th, 2018
Between 10/1 and 10/30	Any dates between October 1st and October 30th of the current year
>[TODAY-7]	Any dates greater than seven days before today's date. (See Dynamic Date Names section)

Comparison Operators

Comparison operators can be entered before the search expression. They are used to control what type of search will be performed. The following comparison operators are supported:

Operator	Meaning
=	Equal to. This is the default operator that will be used if you just enter an expression by itself.
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal to (this is a less than followed by a greater than)
!=	Not equal to (equivalent to <>)
!<	Not less than
!>	Not greater than

Pattern Matching

Pattern matching lets you search using wildcards. Only text data types support pattern matching. FOURA will automatically use pattern matching if you include an asterisks (*) or a question mark (?) in your search expression. The following wildcards are supported:

Wildcard	Meaning
*	Any text of zero or more characters
?	Any single character
[ ]	Any single character included in the square brackets. For example, [acb] will match a, b or c. You can also include a dash for a range of characters. For example, [a-f] will match any character between a and f.
[ ! ]	Any single character NOT included in the square brackets. Also supports using a dash for a range of characters (see above). For example, [!xyz] will match any character except for x, y or z.

Tip: When using a bracket wildcard you must explicitly include the LIKE operator and include a single quote before and after the expression. So if you wanted to find all text that started with r, s or t the full search condition would be: like '[hv]*'

Tip: You can also use a bracket wildcard to search for special characters. For example, if you wanted to find any an asterisks (*) you would need to use a bracket wildcard since the asterisks is a special character used to search for any text!

Wildcard expression	Meaning
AL*	Any text that begins with the letters AL (Albert, Alan)
*soft	Any text that ends with the letters soft (Fourasoft, Microsoft)
en	Any text that contains the letters en (Document, Aberdeen, Management)
?heryl	Search for any text that is exactly six-letters in length, where the first letter is any character and the next five letters are heryl
like '[CK]ars[eo]n'	Searches for Carsen, Karsen, Carson or Karson
like '[M-Z]inger'	Searches for text that is exactly six-letters in length, starts with a letter between M and Z, followed by inger. (Ringer, Singer, but NOT Ginger)
like 'M[!C]*'	Searches for text of any length that starts with M where the second character is NOT C. (Marianne, Michael, but NOT McGrail)
like '[]'	Searches for any text that starts with an asterisks.

List Matching

You can use list matching to search for any exact match in a list of values by using the word IN followed by the list of value in parenthesis. Each value in the list must be appropriate for the data type, so if you are searching a numeric value then each item in the list must be a number.

Tip: Be careful to NOT include a space between IN and the left parenthesis! To use list matching the search condition must start with IN( and end with )

Search condition	Meaning
IN(A,B)	Search for A or B
NOT IN(CA010,XY020)	Search for any text that is NOT CA010 or XY020

Dynamic Date Names

FOURA supports special search expressions for named dates. This can be helpful when you want to create a saved filter that changes based on the current date. When using a named date it will be automatically replaced with an actual date the corresponds to the name. Date name expressions can only be used for date date types. The following date names are supported:

Date Name	Meaning
[TODAY]	Today's date, if today was 11/30/18 then this would search for 11/30/18
[YESTERDAY]	Yesterday
[TOMORROW]	Tomorrow
[WEEKSTART]	The start of the current week based on your Windows regional settings. For the US, this will always return a Sunday
[WEEKEND]	The end of the current week based on your Windows regional settings. For the US, this will always return a Saturday
[MONTHSTART]	The first day of the current month
[MONTHEND]	The last day of the current month
[NEXTMONTHSTART]	The first day of next month

Tip: Named dates can optionally include a modifier to add or subtract a number of days. To include a modifier, just before the closing square bracket enter a + or - followed by the number of days to add or subtract. For example, [TODAY-14] will return a date that is 14 days before the current date.

Date Name	Meaning
>[TODAY-7]	Include any dates that are greater than 7 days before today
between [MONTHSTART] and [MONTHEND]	Include all dates in the current month
between [WEEKSTART-28] and [WEEKSTART-1]	Include all dates from the previous 4 weeks, not including the current week
>[TODAY-30] and <[TODAY+30]	include all dates within 30 days of the current date