Filter Your Data with Database Queries

Share this article:

Overview

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:

 

TaskStatus = "Open" and DeadlineType = "Hard External Deadline"

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:


(Country = “United States” and ActualCost > 2000) or
(Country <> “United States” and ActualCost > 1000)

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:


(InvoiceAmount – Payments) > 1000

You can also compare data fields. For example, to select all tasks that were closed before their due date you could use:

ClosedOn <= RespondBy and TaskStatus = “Completed”

Also note that datafield names in query expressions are not case sensitive. For example:

 

Country = “United States”
is the same as
country = “United States”



Constants

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.



Strings

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

Numbers are specified simply as 1234 or 67.89. Do not use commas or currency signs.



Dates

To specify a date constant, you need to enter either of the following:

 

DateTime(<year>,<month>,<day>)
or
DateTime(<year>,<month>,<day>,<hour>,<minute>,<second>)

For example, if you want to select all matter records that were filed on or after September 16, 2012, your query is:

FilingDate >= DateTime(2012, 9, 16)
 
You can also pick date offsets from the current date. For example, if you want to select all matters that were created in the past 30 days, use the query:
 
CreatedDate > DateTime.Now.AddDays(-30) 

Replace (-30) with (30) to return the date range for the next 30 days, instead of the past 30 days.



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:


FinalDueDate <> null  or FinalDueDate <> ""



Or to show if a data field has no value, you would use:
 
FinalDueDate = null or FinalDueDate = ""



String Functions

String functions allow you to select part of a string in a data field for comparison, or to search for a string within a data field. To use a string function, add a period and then the name of the function after a string field in the query.


Contains

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:

 

DeadlineType.Contains(“External”)
 
You can also specify if the field should not contain the string by adding an exclamation point to the front of the query.
!DeadlineType.Contains(“External”)


EndsWith

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:

 

AttorneyRef.EndsWith(“CN”)
 
You can also specifiy if the field should not start with the string by adding an exclamation point to the front of the query.
IAttorneyRef.EndsWith(“CN”)


StartsWith

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:

 

AttorneyRef.StartsWith(“ROBO”)

You can also negate this by putting an exclamation point at the start of the query.



Substring

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).

 

AttorneyRef.Substring(4,2) = “P2”

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.


ToUpper

This function converts a string to upper case. For example “ROBO” use:

 

AttorneyRef.ToUpper() = “ROBO”

This would match ROBO-P2132 and ROBO-P2134


Operators

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.