For some of the AppColl modules, you can create filters by using “programming language” database queries to select records from within that module. These queries use Boolean expressions that use the column headings from the particular module. If you are comfortable writing queries in this way, you can quickly create complex filters and reports.
Initially, follow the same steps used to filter reports: Within the Change View section in the left column, click the Add Filters button to open a pop-up window. (See the article titled, "Searching and Filtering for Information" for instructions on simple filters.) In the Column dropdown menu, select Database Query. Use the column titles of the module to create your queries. For example, if you wish to select all open tasks that have a hard external deadline, use the query:
These expressions can be arbitrarily complex, and may include comparisons and expressions using constants (discussed below). For example, if you want to select all US matters that have an actual cost greater than $2,000 and all foreign matters that have an actual cost greater than $1,000, you would query:
Note the use of parentheses to force the 'and' expressions to be evaluated before the 'or' expression. You can also do mathematical expressions using data fields, and test the results. For example, to select all invoices that have an outstanding balance greater than $1,000, use the query:
You can also compare data fields. For example, to select all tasks that were closed before their due date you could use:
Also note that datafield names in query expressions are not case sensitive. For example:
Constants are used to compare against the data fields in the query expression. There are three primary types, which correspond to the types of the data fields in a module. When doing comparisons or expressions with data fields in the module, you should use the type of constant that matches the field.
A string is specified using “quotes”, such as “United Status” or “Robert”. If you wish to include a quote in a string constant you should use double quotes: “”.
Numbers are specified simply as 1234 or 67.89. Do not use commas or currency signs.
To specify a date constant, you need to enter either of the following:
For example, if you want to select all matter records that were filed on or after September 16, 2012, your query is:
Null (or empty)
To determine if a data field has any value, compare it with the null constant or empty quotation marks. For example, to find all tasks that have a final due date, you could use the query:
This function looks for a string anywhere in the data field. For example, to find all task deadline types that are external, use the query:
This function looks for a string at the end of a data field. For example, to find all attorney references that end with “CN”, such as ROBO-P2132-CN or ROBO-P2134-CN, use the query:
This function looks for a string at the start of a data field. To find all attorney references that begin with “ROBO”, again using ROBO-P2132 and ROBO-P2134, use the query:
Use the substring function to select a specific part of a data field for comparison. Specify an offset and length to select part of the field: Substring(<offset>,<length>). For example, AttorneyRef.Substring(4,2) would select 2 characters starting at the 5th character (offsets start at 0, which is the first character).
This substring would be true for an attorney ref of ARK-P2132 and false if the attorney ref is DA-P21322.
You can also negate this by putting an exclamation point at the start of the query.
This function converts a string to upper case. For example “ROBO” use:
This would match ROBO-P2132 and ROBO-P2134
The table below shows the operators supported in queries in order of precedence from highest to lowest. Operators in the same category have equal precedence. In the table, x, y, and z denote expressions. Note that you can use parentheses to change the precedence if desired.