Some of the trickier aspects of Tracy are to be found in the "Find" form. The find form allows for more than the simple "find records that were collected by Bill" query. It provides a wild card character "%", a way to indicate that the field should be blank """", a method for building complex searches "+ Find!", and method to access the WHERE clause in the generated SQL query for detailed control of the query. Each of these controls and their use are covered in the following sections.
|How the Find window does its job
The find window consists of the same type of fields that are in the edit window. It has the additional fields of UserID and InputDate that reflect information about the users who have edited the records. The Find window takes input from the user and converts it into a SQL query which is then applied to the collection data. For example, if the user has filled in the Family field and left all the others blank then the Find routine will generate an SQL query that specifies that the Family field must be equal to what the user entered but that their is no restriction on the rest of the fields returned. The results of this SQL query are then displayed in the Results window.
To accomplish its job, the Find window must understand how to create appropriate SQL "where" expressions for each of its fields. In SQL date and number fields are handled in a different manner than a plain ASCII character fields.
|The Wild Card character: "%"
In any of the fields that take alphabetic input you can use the wild card % to indicate that any string of characters should be accepted as a match (Note that the wild card will not work in the following types of fields: Dates, Number, Memos). By using the wild card character the user can tailor his searches to a fairly high degree. This is useful for locating records that contain information in forms that you may be unsure of. For example, if you were looking for collections of a collector named "Bill Smith" but you're not sure if the name is in the database as "Bill Smith" or "Smith, Bill" or "Smith, B. W." or "B. Smith" or "Bill W Smith" or some other permutation. You could simply enter in "Bill Smith" in the Find window and then enter in "B. Smith" and so on. While this would work, it is a "most restricted case" method of searching. In the case where the data may be in a number of forms, it is usually more efficient to start with the "most general case" and then add restrictions based on the result. The most general search could be made for this collector by searching for the name "Smith" and accepting any leading string and any trailing string. To do this, enter the following into the CollectorName field in the Find form: "%Smith%". The two percent signs will match with any strings and therefore any record where the CollectorName field contains the word "Smith" will be retrieved. Note that since SQL is case sensitive names such as "Woodsmith" would not be returned.
Since Smith is a common name you may end up with a large number of Smiths
in the results set. To restrict the search a bit more, the user may
want to add an initial to the search field, "%B%Smith%". This will return
all names where there is a "B" in some form before the word "Smith". (By
using the "+Find" button the user can further restrict the search to "%B%Smith%"
and "%Smith%B%". See the documentation for the "+Find" button.) But say
there is a "Bill Smith" and a "Bobby Smith" in the returned data set. To
further restrict the query the user may enter "%Bi%Smith%" and he will
get back just the "Bill Smith" set (+Find may be used again here to include
variations on this).
|Locating Records with Blank Fields
By using the special search string """" (two double quotes) in a given field a user can tell the system to only retrieve records where there is no data in that field. For example, if you wanted to get all the records that did not have a county name. You would enter the two double quotes string into the County field in the find window. The resulting set would be all those records where there was no entry in the county field.
|The "+Find!" Menu Item
The "+Find!" menu item effectively allows the user to string together a series of Find operations. For example, if the user wanted the specimens from both Brazos and Harris counties, the user would first enter "Brazos" into the County field of the Find window and hit the "Find!" item. The system would then retrieve all the specimens from that county. Then the user would change the County field to "Harris" and click the "+Find!" item. The system would then find all the specimens from Brazos and all the specimens from Harris county would be added to this set and everything would be displayed in the Results window.
If the user then wanted to start over he could do one of two things. Entering his new search constraints into the Find window he can click the "Find!" item and the system will ignore all previous searches and just construct a new search with the new values. Or the user can click the "ClearSQL" button to wipe out the commands that generated the previous result set. This would occur without doing another find command.
The "+Find!" works by remembering the previous SQL query and UNIONing the new SQL query to it and then sending the whole query to the SQL server (or database engine).
|The SQL Menu ("CustomSQL", "ClearSQL", "ShowSQL")
At the top of the Find window there are three choices under the SQL menu: CustomSQL", "ClearSQL", and "ShowSQL".
To understand the use of these buttons it is necessary to be familiar with how the Find window does its job. When the "Find!" button is clicked, the system examines the fields in the Find window, one by one. For each field, it knows how to build up an appropriate SQL WHERE statement expression. All of the expressions are ANDed together to form a complete SQL WHERE statement. This WHERE statement along with the fields that are to be in the Results window, are is used to build up a complete SQL query on the collection data. The "Find!" button generates a new SQL query each time it is clicked. The "+Find!" button generates the same SQL query, but instead of replacing the last query it UNIONs the current query with the previously executed query.
The three SQL buttons give the user some control over this SQL query. The two basic functions are to show the SQL ("ShowSQL") and clear the SQL ("ClearSQL"). Both of these buttons do what they say. "ShowSQL" will place a copy of the SQL query into a dialog box for the user to view. "ClearSQL" will clear the current query without clearing the Find window itself (Note the difference between this and the "Clear" button). In contrast, the use and function of the "CustomSQL" button is not as simple as the other two.
The "CustomSQL" button opens a dialog window that allows the user to enter their own WHERE expressions. Also in this dialog window there are "Clear" and "Find" buttons. The "Clear" button clears the content of the dialog window and the "Find" button takes the user's WHERE expression and the expresion that is generated from the information in the Find window and constructs a complete SQL query that is then run on the data set.
extract (month from BeginDate) = "10" and extract (year from BeginDate) = "1996"Next, the user would click the "Find" button on the dialog itself. These expressions would be ANDed to the expressions generated by the POACEAE in the Find window and the result would be displayed in the Results window. Example 2:
The following would be entered in the CustomSQL dialog:
extract (month from BeginDate) >= "5" and extract (month from BeginDate) <= "7" and extract (year from BeginDate) >="1994"Simply extending the above expressions in a similar fashion allows the user to describe rather complex queries with a minimum of SQL coding.
You can look at more examples of custom SQL strings here.
One of the primary goals for a future version of Tracy is to incorporate this type of functionality in a way that will require no coding on the part of the user.
Back to Tracy