Designing an Advanced Filter by specifying an SQL WHERE clause

You can create, edit or remove the advanced filters by directly specifying an SQL WHERE clause.

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

  1. Right-click anywhere in the grid (or just click the gear icon) and choose Customize View from the pop-up menu. The Customize View window opens.

  2. Click the Advanced Filter tab. If it 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 Customize View from the pop-up menu. The Customize View window opens.

  2. Click the Advanced Filter tab and clear the criteria box, click OK.