(c) Nigel Maddocks, 2007
Edit-DB ver 2.05.016, Doc ver 2.05.016
* Also applies to Views
Records are selected (or filtered) according to record selection criteria which are applied to the columns. Edit-DB enables the user to select records without needing to enter SQL clauses. Underneath each column's heading, there is a small text box into which the user can enter record selection criteria.
For example, to select the record with ID=12, enter 12 into the record selection criteria text-box underneath the column-heading for ID and press Enter (or the Requery button).
Whatever is entered into these column-level record selection criteria text-boxes is interpreted into valid SQL and displayed above the output table. In the above example, the generated SQL is
SELECT * FROM Employee WHERE ID = 12
The column-level record selection criteria are AND-ed together, so to display records where Role=2 AND Activity=5, into the record selection criteria text-boxes enter 2 for Role, and 5 for Activity, and press Enter. The generated SQL is
SELECT * FROM Employee WHERE Role = 2 AND Activity = 5
|Intended selection||Text-box entry||Generated SQL WHERE clause|
|IDs 12, 5 and 7||12 5 7||ID in ( 12,5,7)|
|IDs between 7 and 10||7-10||ID between 7 AND 10|
|Forenames starting with F||F%||Forename like 'F%'|
Quoting character (CHAR, VARCHAR, TEXT etc) values is usually unnecessary as Edit-DB will apply quotes where necessary. The exception is where the value contains a special character(s), in which case the value should be entered within single-quotes
Special characters are -, %, _, [, ], =, >, <, !, like, in, is, not, between, space
To select records where a column isn't null, enter is not null into the column-level record selection text-box. Similarly to select records where a column is null, enter is null.
Although the record selection text-boxes within the multi-record display provide easy-access, there will be occasions where they are too small to easily enter all the selection criteria you require, or it may be difficult to see which columns have record selection criteria applied to them. In these cases, it may be easier to enter or visualise the selection criteria on a dedicated web page which is available on pressing the button.
This screen actually provides access to the same functionality as the row of check-boxes and miniature text-boxes underneath the column-headings in the multi-record display.
Occasionally, the column-level interface for record selection is just not flexible enough
to satisfy your requirements. If this is the case then checking the
check-box gives you access to a text-area for specifying the WHERE clause by hand.