Online Help | Desktop App

Advanced Filter: SQL mode

This article describes how to create or modify Advanced Filter in the SQL mode, by directly specifying a SQL WHERE clause. .

To configure an advanced filter by specifying a SQL WHERE clause:

  1. Right-click anywhere in the grid and choose Filter > Advanced Filter from the pop-up menu. The Customize View - Advanced Filter window opens.

  2. If the Advanced Filter tab appears in the Design mode, click Switch to SQL Mode.

  3. In the SQL mode, type in the SQL WHERE clause (without the word WHERE), and click OK.

Your SQL WHERE clause can include the following placeholders: <Current User>, <Current User Organization>, and <Current User Location>. Before the filter is applied, the placeholders will be automatically replaced with the respective data from the Full Name, Organization, and Location fields of the current user’s Person record.

ClosedClick here to show the examples of SQL WHERE clauses for the Tickets grid

Meaning WHERE clause

Show overdue Tickets

(DATEDIFF(n, [Due_Date], [Completed_Date]) >0) OR ([Completed_Date] is NULL) AND(DATEDIFF(n, [Due_Date], GETDATE()) >0)

Show overdue Tickets which status is neither Closed nor Resolved

([Status] <> N'Resolved') and ([Status] <> N'Closed') and (DATEDIFF(n, [Due_Date], GETDATE()) >0)

Show unassigned Tickets

([Status] = N'Unassigned') and (([Assignee] ='') or ([Assignee] is NULL))

ClosedClick here to show the examples of SQL WHERE clauses for the Computers grid

Meaning WHERE clause

Show leased Computers with expired Lease Return date

[Lease] = '1' AND (DATEDIFF(n, [Lease_Return_Date], GETDATE()) >0)

Show out-of-warranty Computers

(DATEDIFF(n, [Warranty_Exp], GETDATE()) >0)

NOTE: When you switch to the Design mode, all the data entered in the SQL Mode is lost.

To remove an advanced filter created by specifying an SQL WHERE clause:

  1. The Advanced Filter tab appears in the SQL mode. Right-click anywhere in the filtered grid and choose Filter > Advanced Filter from the pop-up menu. The Customize View - Advanced Filter window opens.

  2. Clear the criteria box, click OK.