Defining a Custom SQL Filter
The Call, DXCC, Date, Since, Sel, LotW, and Broke buttons in the Filter panel at the bottom of the Main window's Log QSOs tab make it easy to quickly filter the Log Page Display in frequently-used ways. In addition, the Advanced Sorts, Filters, & Modifiers window's UTC Filter, Band Filter, and Mode panels enable you to filter the Log Page Display by date range, by commonly-used bands, and by commonly-used modes.
However, there are many ways you might like to filter the Log Page Display that aren't supported by the above mechanisms. For example you might like to filter the Log Page Display to show only
- QSOs made with Spratly Island during the 1990s
- QSOs made with Japan on 15m when the Solar Flux Index was less than 120
- QSOs made in any variant of JT65
- QSOs made while you were operating as P5RY
DXKeeper enables you to describe filter the Log Page Display with an expression that specifies a desired relationship among QSO items; here are the expressions corresponding to the descriptions above:
(DXCCPREFIX = '1S0') and (QSO_BEGIN between #1990-01-01# and #2000-01-01#)
(DXCCPREFIX = 'JA') and (BAND='15m') and (SFI > 0) and( SFI<120)
MODE LIKE JT65*
STATION_CALLSIGN = 'P5RY'
These expressions are specified in a language called SQL, an acronym for Structured Query Language. You can learn more about SQL and its use in filtering DXKeeper's Log Page Display, but the techniques illustrated in the above four examples will get you started. The ADIF FIELD NAME column in this list of QSO items provides the names of all QSO items that you can reference in an SQL expression, adding to the item names like DXCCPREFIX, BAND, MODE, SFI, and STATION_CALLSIGN used in the above examples.
To filter the Log Page Display with an SQL expression, you can simply type it into the Filter panel textbox at the bottom of the Main window's Log QSOs tab, and strike CRTL-S. This is fine for short, infrequently-used expressions, but the Filter panel textbox is too short to reliably type in an expression like
(DXCCPREFIX = 'JA') and (BAND='15m') and (SFI > 0) and( SFI<120)
even if you don't frequently use it to filter your Log Page Display. The SQL Query Filters panel on the Advanced Sorts, Filters, & Modifiers window makes it easy to assemble, review, and execute a long SQL expression, and the SQL expressions specified in this panel can be directly invoked by clicking a button in the Filter panel textbox at the bottom of the Main window's Log QSOs tab.
To display the Advanced Sorts, Filters, & Modifiers window, click the Adv button on the Main window's Log QSOs tab. This window's SQL Query Filters panel provides wide textboxes in which you can define sixteen SQL expressions, but only four such SQL expression textboxes are visible at one time. Clicking the ~ button in the SQL Query Filters panel's upper-right corner cycles the panel through four arrangments, each displaying four SQL expression textboxes:
SQL expression textboxes 1-4
SQL expression textboxes 5-8
SQL expression textboxes 9-12
SQL expression textboxes 13-16
You can compose an expression in the SQL Expression box simply typing, or by pasting an expression copied into the Windows Clipboard from a post on the DXLab group or in an email message from a helpful friend. The ADIF field name selector at the top of the Advanced Sorts, Filters, & Modifiers window makes it easy to see the correct spelling each item's name; the currently selected field name can be append to the contents of an SQL expression textbox by double-clicking.
You can filter the Log Page Display with an SQL Expression in the SQL Query Filters panel by clicking the Filter button to its right. Alternatively, you can specify a Caption of up to seven or eight characters, and then filter the Log Page Display with an SQL expression by clicking the button with that caption in the Filter panel at the bottom of the Main window' Log Page Display. Like the Advanced Sorts, Filters, & Modifiers window's SQL Query Filters panel, the Filter panel provides a ~ button at its right; clicking this ~ button cycles the Filter panel through five arrangements, each offering a set of filter buttons:
Call, DXCC, Date, Since, Sel, LotW, and Broke
SQL expression captions 1-4
SQL expression captions 5-8
SQL expression captions 9-12
SQL expression captions 13-16
Here's a picture of the SQL Query Filters panel populated with SQL expressions that filter the Log Page Display to show
- all QSOs confirmed via QSL card
- all QSOs confirmed via LotW
- all QSOs confirmed via eQSL
all QSOs with DXCC entities considered deleted by the ARRL
Here's how the Main window's Filter panel would look when the ~ button was struck several times to make these four custom SQL filter buttons appear, each with a caption specified in the SQL Query Filters panel:
