Searching

Standard Search Dialog

The Standard Search dialog is used in many parts of the system.  It is used to select specimens for inclusion in a report, a data entry batch, or a requisition.  It can also be used to select rows in the tables of the Shipments, Data Entry and Requisitions editors as well as to select the items listed on a manager.

The Search window allows you to build a search statement to execute against the database. To create a new search statement, follow the formula below:

Choose a field to search on:

1. Select the appropriate field group from the drop down:
   • The field group drop-down list box is located in the top left-hand corner of the screen. Because the list of fields available to the system is large, they are divided into groups. The definition of these groups may change between different search screens.
2. Select the field to search on:
   • The field list box is located directly below the field group drop-down list box. It displays the fields available for the current field group. Most of the time, this is a simple list of field names. However, sometimes folders are displayed, containing fields. Double-click on a table's folder to open it and display its fields.

Choose the appropriate operator to use in your search:

3. Select an operator:
   • The Operators box contains all of the valid operators for a search statement. This includes equal to, less than, greater than, etc. A single operator must be selected for each search statement. The selection is done by single clicking on the operator of choice. Users can utilize the all term and any term operators.

Enter values for your search criteria:

4. Enter values manually:
   • The Value(s) box is located at the top right corner of the search screen. These values are to be compared to the selected field values using the selected operator.

5. Select values for choice fields:
   • The value list box is located directly below the Value(s) box. It contains a list of code list values associated with the currently selected field from the field list box. When typing in the Value(s) Box, items listed in the Value List Box will automatically be filtered.
   • To select multiple values at one time, click on each entry desired while holding down the Shift or Ctrl keys on the keyboard.
   • Select the Specify Values or Specify IDs tool, if available, then enter values or IDs.

Creating the search query line:

6. Select the Add to Search Criteria
   1. Clicking this button will combine the currently selected field, operator, and value(s) into a search line and add it to the Search box. Multiple criteria can be selected by separating each with a semi-colon in the Value(s) Box.
      • When scanning or pasting values, a semi-colon delimiter will be added automatically where a new row or new line is indicated.
   2. There is no practical limit on the number of search lines that can be in a search statement. Records returned will match all search criteria in the given search box.

Edit any criteria, as necessary. To edit an existing search line, click on the line in the Search box. The Field, Operators, and Value(s) boxes discussed above will be reset to the values for this search line. Users may then edit the properties of this search line by making new selections in the appropriate boxes, then selecting Add to Search Criteria. Add an additional search. A new set of search criteria can be added by clicking the + tab above the Search Box. Each tab represents an individual query, or an “OR” statement. Records returned will match all search criteria in at least one search tab used.

View your query in the Search box

You may view and edit your current search criteria within the Search box. Select a row within the Search box to edit or delete search items.

Additional options:

• Test Button - executes the search on the server and displays the number of records returned by this search. This number will be displayed in the bottom left hand corner of the screen. It is always a good idea to test a search before using it!
• Delete Button - This button will delete the selected line in the Search box.
• Clear - deletes all of the lines in the Search box.
• More - contains a sub menu with the following options:
• Open - opens a saved search file and loads it into the Search box.
• Save - saves the current search statement to a local file.
• History – Contains up to 10 previous searches from the current session. Hovering the mouse pointer over the search number ("Search 1", "Search 2", etc...) in the list will display the search criteria in a Tooltip.
• Limit Records Returned - opens a dialog that lets the user specify the maximum number of records returned by the search.

When your search statement is complete, select OK to process your search.

Introduction to Searching

On the Search dialog, users can create search statements that return specific records. A search statement is a collection of one or more search lines.  A search line has the following syntax:

[Field] [Operator] [Value(s)]

A field is a single field in the database.  An operator indicates how the field is to be compared to a value.  A value can consist of single or multiple values separated by semicolons.

The values specified are compared to values for the search field in the database.  Only records that match the criteria specified by the search statement are returned.

Users create one or more search lines to define a search.  If multiple search lines are used, BSI treats them as if they were connected with an “and”.

Defining a search with multiple search statements is a good way to limit the amount of records returned.  Users can also specify a range of values by separating the low and high value with a hyphen.

Additional search criteria may be added to conduct OR searches. The '+' on the last tab in the search box adds a new tab/query to the right of existing tabs. Each tab represents an individual query.

Select a Search Field

The Search Fields area, on the left, contains a Field Group drop-down menu where you select the type of search field to use in the search statement. Not all of the following tbales will be available from all search dialogs:

• Fields/Columns - Lists the columns on the current manager of editor table.  This is the only search field category when searching in managers or editors.
• Display Fields – Fields selected for display on a report.
• Select Vial Fields – Fields that pertain to specimens.
• Select Requisition Fields – Fields that pertain to requisitions.
• Select Kit Fields – Fields that pertain to kits.
• Select Results Fields – Fields that pertain to results.
• Select Shipment Fields – Fields that pertain to shipments.
• Select Study Fields – Fields that pertain to studies.
• Select User Fields – Fields that pertain to user accounts.
• Code Lists – Fields that pertain to code lists.
• Select Subject Fields – Fields that pertain to subjects.
• General Database Tables – Fields that are searchable but which do not fit into any other category.
• All Database Tables – Lists all directories containing fields for each table in the database.

Fields in the selected category are listed below the drop-down menu.  Select a category from the drop-down menu and then highlight a search field from the Field List Box to use in the search statement.

Select a Search Operator

The Operators List Box, in the middle of the Search dialog, contains comparison operators.  Select an operator to compare the search field to the specified values:

The selected search field determines the operators available in the list.  For example, a text field, such as Study ID, would not need inequality operators, such as < (less) or > (greater). A value for this field will either be equal to (=) or not equal to (!=) a record’s Study ID.

There is a system preference called Like Search Ignores Case which, if enabled, will make the =@ (like) operator be case insensitive while searching.

Specify Values

The Specify Values option is available for Search Fields that have a fixed set of possible values or formats.  A format may have many pieces of information attached to it.  However, the Value List box only displays the value for the format - no other information is displayed.  To view and select this other information, click on the Specify Values button located directly below the Operator List box.  This will launch the Select Values dialog that displays all the information for that format.  For modifier fields the Specify Values button opens the Modifiers Selection dialog.

Use the Select Values dialog to search for and select one or more values to use in the search line.

Codes and Labels for the values are listed in the Available pane.  To select a value highlight it with the cursor and press the Add button.  To select multiple values, highlight them while holding down the Ctrl key and then press the Add button.  The Codes and Labels for the selected values will be listed in the Selected pane. Codes can also be imported by selecting the Import button, which launches the Importer. Matching values from the import file will be added to the Selected Codes section of the Select Codes dialog.

Use the Find text box, located below the Available pane, to locate desired values.  As you type in the Find text box, the Available pane will scroll to values that most closely match what is typed.  The Clear button will clear all the values from the Selected pane.  The Remove button will remove only highlighted values from the Selected pane.  Once all the desired values have been added to the Selected pane, press the OK button to use the values in the search statement.

Specify IDs

For ID fields, such as BSI ID and Sample ID, the Specify IDs option is available.  This option allows users to import a text file containing a list of IDs or ranges of IDs to use as values in a search statement.  For BSI IDs, the Specify IDs dialog also contains a Find BSI IDs feature that allows users to create multiple search statements in order to locate BSI IDs.  To access the Specify IDs dialog, press the Specify IDs button located beneath the Operators list.

To Import a list of values from a text file, press the Open button and follow the instructions on the Importer.  Imported values will appear in the Specify IDs dialog.  ID values can also be typed into the IDs list.  When entering ranges of values, enter the starting ID in the ID (From) column and the ending ID in the ID (To) column.  Remove values from the Specify IDs dialog by selecting a value and pressing the Remove button.  Press OK to use these values in the search statement.

Searching with the Any/All Terms Operators

These operators are used for multi-value fields.  A multi-value field can have more than one value assigned to it.  

For example, suppose there is a record in the database with the following values for the field vial.material_modifiers: “P53;WK=3;NPT=0;”.  If users wanted to search this field to find all the records that have the modifier P53, users would not want to use the statement “vial.material_modifiers = P53" since this record has other values in addition to P53 and so it would not be included in the search results set.  If users use the “all terms” or the “any terms” operators instead of the “equals” operator, this record will be selected in the results set because it meets the requirement of containing the term P53.  

The difference between the two terms only occurs when users search on multiple terms, (i.e., "vial.material_modifiers = P53;X99"). In this case, using the “all terms” operator would not select the sample record as it does not contain X99. The “any terms” operator would select the record, however, because it contains one of those terms, P53.

Specify Values for Modifier Fields

Since multiple modifiers' codes may be assigned to a vial and some modifier codes can be given values, the Specify Values option works differently for modifier fields.  When a modifier field is selected as the search field, the Specify Values... button opens a Modifiers Selection dialog.  

This dialog contains three columns where a modifier search value is created:

• Modifiers - where the modifier code is selected and displayed.
• Operator - where an operator is selected if a value can be assigned to a modifier code. If no value can be assigned the operator is =*(exists).
• Value - where values are entered for modifier codes that accept values.

Each row contains one modifier search value.  Additional rows can be added by pressing the Insert button.  Rows can be deleted by highlighting them and pressing the Delete button.

To create a modifier search value:

1. On the search dialog, highlight the modifier field then select the Specify Values button.
2. Highlight the desired modifier code.  If the "...Value Req" column for the highlighted modifier code is set to True/Yes, a value may be entered for that code in the Modifiers Selection Value text box.
3. Press the Add to Selected button.
4. Once all values are added, press the OK button.  This dialog will close and the search dialog will display the selected modifier values in the Value(s) text box.
5. Once finished creating the modifier search value(s), press the OK button.  The modifier search value(s) will be added to the Values text box on the Search dialog.

Enter Search Values

The values box is located in the upper right pane of the Standard Search dialog. Here a user can enter their desired search values. Some search fields, such as Study ID or Repository, will have a set of possible values to choose from. Users may add additional values to these fields from the Code Lists Manager.

Certain search fields allow search values to be selected with the Specify Values or Specify IDs dialogs. If available, a button to open one of these dialogs will appear under the Operators box in the Standard Search dialog.

Select Values for a Search:

1. Type search values into the top text box OR:
2. If available, select values from the list of possible values located below the text box.
   • Users can use the Find text box, located below the values list, to locate desired values in the Value List box. As users type in the Find text box, the values list will shrink to contain values that most closely match that which is typed.
3. Select a value from the Value List box by clicking and highlighting it.

Note: A unique scenario exists in the Data Entry manager when searching for multiple Shipment IDs. Since Shipment ID is a text field, you must select the @like operator and ensure that each Shipment ID is surrounded with asterisks, in order for the search to be properly executed.

Add Multiple Values for a Search:

1. To add multiple values for a search field, type the values separated by a semi-colon.
2. To select multiple values from the Value List box, hold down the Ctrl key while highlighting the desired values.

Add A Range of Values for a Search:

1. To enter a range of values, enter the minimum and maximum values separated by a hyphen.

Specify BSI IDs

The Find BSI IDs feature allows users to create multiple search statements in order to locate BSI IDs.  This feature is accessed by selecting BSI ID from the Search Field list, pressing the Specify IDs button to open the Specify IDs dialog and then pressing the Find button to open the BSI ID Lookup.  

Each row on the BSI ID Lookup dialog contains one user created search statement.  Rows can be added by pressing the Insert button and removed by highlighting a row and pressing the Remove button.

To create a search statement:

1. On the BSI ID Lookup dialog, for each row, enter values in the displayed fields.  Each row's values are treated as though connected by an "AND"; they are combined to create one search statement. Multiple rows can be added using the Add Rows button. Each row will function as a separate "OR" search statement.

2. Once users have created their search statements, press the Test button.  A count of the BSI IDs that meet each row's search criteria will be displayed in the Count column.  Users can now limit or expand their searches by modifying search criteria.

3. Press the OK button. The BSI ID Lookup dialog will close and any BSI IDs selected by the search(es) will be added to the Specify IDs dialog.

4. Press OK on the Specify IDs dialog to add the ID(s) to the Value(s) text box on the Search tab.

Searching with the Like Operator

The =@(like) operator allows users to use wildcard characters in the search value.  

• Use the underscore (_) or vertical bar (|) character to represent any single character.

• Use the asterisk (*) character to represent any combination of zero or more characters.

When wildcards are used in the search value, all possible strings defined by that expression are returned as results.  For example, searching on Current Label =@(like) AA3* will return all the Current Label fields that begin with the letters AA3.

Add the Search Line to the Search Box

The Search box, at the bottom of the Search dialog, will contain the search lines that define a search.  Once the user has selected a search field, operator, and value(s) for their search line, press the Add to Search Criteria button or the Enter key to add the search statement to the Search box.

Delete search statements from the Search box by highlighting a statement and pressing the Delete button, located to the right.

Additional search criteria may be added to conduct OR searches. The + on the last tab in the search box adds a new tab/query to the right of existing tabs. Each tab represents an individual query. Add search criteria to the search box. The tabs/queries are connected via an OR statement. Results returned will match at least one of the queries.

The Clear button will remove the currently selected tab.

Execute the Search

Once all the search statements that define the search criteria have been created, added to the Search box, and tested for the number of records returned, the search can be executed.  To execute a search for a Data Entry batch or a Requisition, press the OK button.  To execute a search for a Report, either press the Execute icon or select Execute from the Reports editor File menu.

Indexed Searches

Certain fields within the tables are indexed.  An index allows BSI to perform searches on a field quickly.  Although it may seem to be beneficial to index all fields within the system, it is not possible.  A search on non-indexed fields requires a longer time to execute than a search on indexed fields.  Generally, most of the searches performed in BSI are of the non-indexed variety, and the speed of the search is then directly related to the number of records in the tables.  This is the reason why some searches execute quickly and some seem to take much longer.

Use the Test button to get a count of how many records are returned by a search.

Test the Search

The Test button, to the right of the Search box, will return a rough count of records that meet the search criteria without actually adding the records to a batch, Requisition, or Report.  This count is returned to the lower left corner of the Search dialog.

The more records a search returns, the longer the search will take to execute.  If the search returns a large number of records, try narrowing it by adding additional search statements.

The number of records returned may also be limited by pressing the More button, to the right of the Search pane, and selecting Limit Records Returned.  A window will appear allowing users to enter the maximum number of records to be returned by the search.

Search on Missing values

Sometimes you may want to search for records that contain a missing (or blank) value for a particular field. You may also want to search for records which do not have a missing or blank value for a particular field. To do this select @@Missing from the value list box.