Query Builder in Specify 6

[Grant]

 Query Builder

One of the expected benefits entering data into a structured database is to be able to retrieve the data efficiently and consistently. Specify's Query Builder offers a flexible interface for defining and running searches. Queries are a precise request for information retrieval and can restrict the returned records based on the contents of any Specify data field.

The results from queries can then be used to create Record Sets or Reports.

Open the query builder by clicking the  (Query button) on the Taskbar.

The side bar displays a list of frequently used tables to begin the query configuration. Less common tables are found by clicking  (More Tables) in the side bar and choosing a table from the pop-up dialog.

A Query Creators Configuration tool gives access to less frequently used tables and also allows the side bar and More Tables lists to be interchanged and rearranged:

Choose Tabs > Configure side bar from the Menu bar or right-button-click an open section of the side bar to activate the Context Menu and choose Configure Query Creators to open the Configure New Queries dialog.

• Use the or (reorder) button to change the order in which the forms appear in the side bar and More Forms list.
• Use the or (move) button to move a side bar item to the Extra Forms list and vise versa.
• Click the (add) button for a list of additional forms that can be moved into either the side bar or More Forms list.
• Click the (delete) button to remove forms from either the side bar or More Forms list; this will put them back into the master list.

Note: deleting a table from either the side bar or More Tables will place it back in the master list which is accessed by the  (add) button.

 

A Configure Saved Queries tool allows saved queries to be rearranged on the side bar. Choose Tabs > Configure side bar from the Menu bar or right-button-click an open section of the side bar to activate the Context Menu.

Choose Configure Queries to open the Configure Queries dialog.

• Use the or (reorder) buttons to change the order in which the forms appear in the side bar and More Forms list.
• Use the or (move) buttons to move a saved query from the Favorite Queries side bar list to the Other Queries list and visa versa.

 

Choose a table from the side bar list to begin building the query.

Note: Because of the way Specify combines related information, almost every field can be found from within most tables. This will determine the data elements to be returned i.e. searching from Collection Objects (CO) will return CO records.

 

Choose Fields

A list of fields available in the chosen table, as well as other related tables, appears in a list box at the top of the query workspace.

To display fields in other tables, simply click a table in the list box. Fields and other related tables display in a new list box.

Choose fields and/or tables for the query from within the appropriate list boxes by either selecting the field/table and clicking the  (add) button or double clicking on the table/field.

By selecting fields to include in the search users can restrict the type of returned records based on the contents of almost any of Specify's 2500 data fields.

There is no limit on the number of fields added to a query, but larger, more complex queries will take a longer amount of time to run.

In Specify 6, for a column to appear in the results it must first be represented by a field in the query. Users who wish to always see a particular set of columns in their query results are encouraged to create a query with those fields and then save the query as a template for reuse. When using the template subsequently, simply click on the saved query, then add new selection criteria and parameters to create and run the new query.

Specify 6 shows the result of a query as a grid display

Note: The Query Builder tool includes extra fields for Cataloged Date; Cataloged Date (Day), Cataloged Date (Month) and Cataloged Date (Year). This allows users to easily search for a specific Cataloged day, month or year.

 

Choose Tables

Select a related table to be part of the search criteria by double clicking it in the list box. Tables are either formatted or aggregated, depending on their relationship to the primary Table and do not offer any other search criteria. Most tables in Specify have a default format and aggregate setting. Formats and aggregates can be edited, created or deleted in System > Schema Configuration.

 Many-to-One (Formatted Data)

A many-to-one relationship involves a primary and related table in which many primary records are linked to a single related record in the related table. For example, many Collection Object records might be linked to the same Locality record because they were all found at the same location.

Tables with a many-to-one relationship are designated by an (). Tables with many-to-one relationships are formatted to be represented by at least one field. Most tables are represented by only one field, (Collection Object shows the Catalog Number), but some are best represented by more than one field (Agent may be formatted to display last name, first name, middle initial). Query results use this format when returning results.

 One-to-Many (Aggregated Data)

A one-to-many relationship involves a primary and related table in which many related records are linked to a single primary record in the related table. For example, several Collection Object records would be linked to a single record in the Collection Event table because all those specimens were collected during the same event.

Tables with a one-to-many relationship are designated by an icon () and return aggregated data. For example, a Collection Object can have many Preparations. The results of a query of Collection Objects with Preparations will return Preparations aggregated together in one column, separated by colons or semi colons.

When returning aggregated data, it is possible that a field may first be formatted.

 

Query Expressions

Create Query Expressions to narrow the results.

Query Expression Box

Note: Not all fields include Criteria. For instance, a Check Box does not have searchable Criteria and needs only an Operator.

 

Heading Choices Result Available for Field Type Not   Switches the operator into its opposite. (example not =) All Operator   Finds all contents for which the selected field is Yes, No or Empty.     = Finds records for which the selected field is equal to the specified criterion. Number, Date, Text   > Finds all records for which the selected field is greater than the specified criterion. Number, Date   < Finds all records for which the selected field is less than the specified criterion. Number, Date   > = Finds all records for which the selected field is greater than or equal to the specified criterion. Number, Date   < = Finds all records for which the selected field is less than or equal to the specified criterion. Number, Date   Between Finds all records for which the selected field is between the specified criterion. Number, Date   In Finds all records for which the selected field is included in the list of criteria. (example criteria; Northern, North, No. or 32, 35, 36, 39) Can also be used to find individual records as well as records within a series. For example: '16, 127-400, 55, 66-68' can be entered. Number, Text   Like Allows a wildcard character (*). Finds all records for which the selected field matches the specified pattern expression. (example; *ist) Text   Contains Finds all records for which the selected field contains a specified character sequence. Text   Empty It is important to note that Empty is a Condition and not the same as a Condition left blank. A Condition that is left blank will be included in the results but not in the search. A Condition that is set to Empty will only return records that contain an empty field. Number, Check Box, Text   True The field is a Boolean (Check Box) and is True. Check Box   False The field is a Boolean (Check Box) and is False. Check Box Criterion   Criteria are entered into this box. If the operator is 'Between' two boxes will display to enter both criteria. Date, Number, Text Sort   Allows the results to be sorted. It is often useful to sort duplicate data so that results are easier to read and the information may be grouped on a report. For example, if a report is desired that lists Collection Objects according to their Taxon Name then the query must be created with the Taxon Name sorted and then grouped in the Report. All   Do not sort.     Sort in descending order.     Sort in ascending order. See above. Show   Includes the field in the results. All Prompt   This will provide a 'prompt' dialog which allows the query expression to be customized each time a Report is run on the query. All Always   Includes the query Condition when running a Report by dropping a Record Set onto it (in the Report side bar). If 'Always' is not checked then the resulting Report will include all record ID's rather than only those matching the query Condition. This is particularly useful when searching Prep Types and Current Determinations. All   Delete the expression from the query. When editing a Saved query any expressions used in a report may not be deleted. All

 

Using Wild Cards

The operator Like allows a search to be restricted to a specific pattern of letters or symbols or both in a designated field. The wildcard character is an asterisk (*).

*Clinton	Returns any string with Clinton at the end.
Clinton*	Returns any string that begins with Clinton.
*Clinton*	Returns any string that includes Clinton anywhere in the string.

When it is necessary to use an asterisk (*) as part of the literal search criteria, precede it with a "\". For example; when it is desired to find Cli*nton enter the search criteria as *Cli\*nton*.

Other Controls

The order of the condition row determines the order in which fields will be sorted as well as the arrangement of the fields in the results panel. If a query includes both Taxon Name and the Last Name of the Collector and both are sorted but Taxon Name is ordered first, Specify will sort Taxon Name first, then Last Name of the Collector.

Side Buttons	Function	Result
	Reorder Down	Move the field down in order. The order determines how they will display in the results window.
	Reorder Up	Move the field up in order. The order determines how they will display in the results window.

 

Bottom Controls	Result
Search Synonyms (checkbox)	Includes synonyms in the query and query results.
Distinct (checkbox)	Returns unique results only (no duplicates). When the Distinct button is used in a query, the query can not be used to create record sets, view results in data entry forms, or run reports. When both Distinct and Sort are used in a Query the database may need to create its own Sort priorities due to the way that MySQL retrieves data.
Count (checkbox)	Gives a count of the results in a pop up dialog in the query work space only. No results will display when the Count feature is in use.
Search (button)	Engages the query. Pressing the Enter key will also engage the query.
Save / Save As (toggle button)	Click the Save button to save the query as the same name or click the (toggle) button to choose Save As and rename the query. Saved queries appear in the side bar and can be reused.

 

Editing a Query

Click on a saved query in the side bar to display it in the workspace window. Expressions can be edited using the controls above. Expressions that are used in a report may not be deleted.

 

Using Query Results

Query results are limited to 20,000 records. A return count can include more records, but Specify can not display more than 20,000 result records. Use the Count checkbox if only a count of results is desired, or break a query into sections if it is too large.

Query results display as a grid in a query Results window. They are designated by a green () results bar.

Click on the title bar at the top of the column to sort the column. Dates should be in a MM/dd/yyyy format to sort correctly.

Results can be manipulated and utilized in various ways using the buttons on the results bar.

Button Result Create a Record Set from the selected records. Displays associated attachments as thumbnails in an Attachment Tool tab in Specify. Information about the attachment and associated record is also available in the attachment tool. Open records; To open a specific result record, first choose the record then click on the button. To open select records, select them by Command + Click (for Mac) or Ctrl + Click (for PC) each record, then clicking on the button. To open all the records, select the button without choosing a specific record. Print the results Grid. Export the grid to an MS Excel file. Display a report from the returned records. This button only displays if reports are available for the type of query that was searched. Clicking the button will result in a dialog listing all available reports for the query type. Choose a report from the list for the report to display in Specify.

Note: Individual records opened from the query results will open in View mode. To edit the records click the  (Edit) button.

Query results can also be used to add records to an existing Record Set.

Click the  (Record Set) button at the bottom of the results window. The Choose a Record Set dialog will appear:

• Select a record set name in the list and click the OK button to add the selected records to the existing record set.
• Click the New button to create a new record set
• Click Cancel to close the dialog.

Or, select the records for the  (Record Set) button to enable, then drag the button to an existing record set on the side bar to add the records to the existing record set. The existing record set and the new records must be from the same table to be compatible. As the button is dragged toward the side bar, any existing record sets that can accept the new records will display a colored band.

 

Saved Query Menu

 

Right-button-Click on the side bar to open the Query Context Menu:

Menu Option Result Configure Query Creators Opens a Configuration tool for adding, removing or reordering the primary tables in either the side bar or More Tables lists. This is explained Query Creators Configuration. Configure Queries Opens a Configuration tool for including the saved Queries in either the side bar or Other Queries lists. This configuration tool is explained in Configure Queries. Import Queries Opens a file directory to find the query in the local file system. Export Queries Opens a save dialog to save the query in a location on the local file system.

The configure tools can also be accessed by choosing Tabs > Configure side bar from the Menu bar.

 

Import / Export Queries

Queries are imported and exported as a group of queries contained in a file. Even when only one query is being exported, Specify writes the query to a file.

The exported query file will be saved as an xml file.

Import

1. Right-button-Click in the side bar, then choose Import Queries in the context menu.
2. Choose the file from the resulting file menu dialog
3. Choose the queries to import from the resulting dialog list (of queries contained in the import file).

Note: When importing queries that have the same name as existing queries, Specify will simply add a number (2) to the import query name.

 

Export

To export a query:

1. Right-button-Click in the side bar, then choose Export Queries in the context menu.
2. Choose the queries to export from the resulting dialog list and click the Save button.
3. Name the file and choose a storage location for the file in the resulting file menu dialog, then click the Save button.

 

Delete a Query

Queries can be deleted by dragging and dropping them onto the Trash Can in the side bar. A prompt will ask the user if they are sure they want to delete the Query.

Queries that are used in Reports may not be deleted.

 

Query Examples

Higher Level Taxonomic Searches

A special table of fields has been added to the schema to allow for the construction of a query that returns a list of nodes included in a particular level of a tree or a unique list of nodes currently used in a collection. This is available for the taxon, geography, storage, lithostratigraphy and chronostratigraphy tables/trees.

Taxonomic Rank Query Example

The query is shown above. The steps for creating the query include:

1. Select either the taxon table for a list of nodes for a level of the tree or collection object table for a list of all unique nodes associated with a collection.

   If you wish to include all items in a rank (independant of whether they are used by objects in your collection), choose the Name field from the taxon table.

If you wish to only include items used in your collection, then select the approporiate rank field from the taxon table within determinations.

2. Select Taxonomic Rank > Name and add it to the query.
3. Type the level you wish to query in the Criteria text box.
4. Uncheck the Show box.
5. Click the Sort box to sort the results in ascending order (if required).
6. Check the Distinct box at the bottom of the work space if you wish to produce a unique list.
7. Click the Search button.

[sginzbar]

I would like to be able to display township range and section in query results. TRS is stored in the localitydetail table. This table is not available in Configure Query Creators, not in the Side Bar List, not in More Tables. When I click on the + it is not in the Master List.

Steve

[Grant]

Hi @sginzbar,

You can configure a Locality query and use the relationship to the Locality Detail table to display the results.