ICIS Retriever 5.5

From ICISWiki

Introduction

The ICIS Retriever is an MS Access application that extracts data primarily from DMS by the specified property (trait), scale and method. But it also retrieves related information from GMS. When you run the Retriever, RTV menus are added in the MS Access main menu.

RTV Menu

Retrieve

Study - browses a study and retrieves its dataset. Refer to this one for details.

Germplasm List - Retrieves a SetGen list into a RTV-LIST table that can be used as criteria for search. For details, refer to this one

Study and Property of List
- Any - Retrieves observed or measured data for any entries of SetGen list(s) based on the selected studies and properties

- All - Retrieves observed or measured data from studies where all entries of selected SetGen list(s) are tested

GMS Information - Gets information from Genealogy Management System for a given query or list

Inventory Information - Provides the stock balance of the entries in selected query or list

Environment Information - Provides information about the specified location in a query based on the set environmental variable

Define RTV Query

Main - equivalent to clicking the MAIN QUERY button in the ICIS Data Retriever form. [[1]]
Aggregate - equivalent to clicking the AGGREGATE button in the ICIS Data Retriever form.[[2]]
Subquery - equivalent to clicking the SUB QUERY button in the ICIS Data Retriever form. [[3]]
Link - equivalent to clicking the LINK button in the ICIS Data Retriever form. [[4]]
Parallel - equivalent to clicking the PARALLEL button in the ICIS Data Retriever form. [[5]]

Run RTV Query

Main
Aggregate
Subquery
Link
Parallel

RTV Save As

GMS List

IRRISTAT Excel

RTV Addin

Switchboard

The Switchboard form is the default form shown when ICIS Retriever is run. This is a customizable form where commands can be added aside from the default ones as shown below.

The default Swtichboard form of the ICIS Retriever

Linking the Database

Before any queries can be done, the ICIS databases should be linked to the Retriever. This is done automatically for MS Access and MySQL/Postgres databases using the information stored in the ICIS.INI. However if this fails, there is still a tool that will allow you to link/unlink the ICIS Databases. To do that, just click the Link/Unlink ICIS Database button in the Switchboard. Two options are provided just as shown in the figure.

The Switchboard menu items for linking ICIS databases

User can click any of the options depending on the ICIS databases they want to access. If the ICIS Databases are MS Access, the form that will be displayed is similar to the figure below. When one of the link buttons is clicked, a dialog box will appear where the folder and the database to be linked can be entered or selected. A different form is used for linking Non-MS Access databases. The full connection path to the database including the ODBC driver, user name and password should be entered as illustrated below.

Link MS Access database form

Queries/Searches

The other command in the Switchboard is to open the ICIS Data Retriever.

The Data Retriever form allows user to define searches on the DMS which is done through the MAIN QUERY button. Three other kinds of queries can be done on the result of the MAIN QUERY, which are sub query, aggregate query and link query.

Main Query

The Main Query retrieves data from the DMS database based on the property, scale and method provided by the user. It creates a table of columns corresponding to the variable names given by the user to each property. . Defined queries are shown in the list under the Main Query button. To run a defined query, click the Retrieve button. View button displays the resulted table. Edit button allows user to modify the defined query while Delete button will delete the definition of the query and all related queries with it.

To create a new main query, choose the “Define RTV Query” from the added RTV menu and click "Main" and a screen similar to figure shown below.

The Main Query retriever accepts two sets of properties. The first set contains the properties to be used as Filter to the query while the second set includes the other query variables or properties of interest to the user in relation to the filter. Under the Filter tab, the user has to provide the property, scale, method, operator, data type and a restricting value. A variable name for the property can be selected from the list and it will be the column name in the resulted table. For the other query variables, the user need to select a variable and check that the default Property, Scale and Method given is the ones he is interested with.

The Main query form

The Retriever runs the query and creates a table named after its specified name by clicking the RETRIEVE button.

Parts of the Main Query form

Search Name - a meaningful name of the query. The name can be any alpha-numeric charaters with no blank and special characters in between.
Description - a useful description of the query
Date Created - the date the query is created which is automatically filled up
VARIABLE column - any variable name with no space in between. Based on the selected variable, the system will automatically fill up the Property, Scale and Method. But the user is free to change this.
PROPERTY column - property or trait of the variable
SCALE column - measurement unit of the variable
METHOD column - the procedure on how the variable is measured or derived
DATA TYPE - the type of data of the variable which is either number or character
OPERATOR - if the data type is Number, it can be any of this: =, >, <, >=, <=, LIST. If the data type is Character, it can be either LIKE or LIST
VALUE - the restricting value or a table name that contains list of values if the operator is LIST. The table name can be created by double clicking on the entered table name.

To define a main query, refer to this one[6]

Sub Query

The sub query creates a subset of the main query based on a criteria specified by the user. The retriever for the sub query is similar to the screen below and it is activated by clicking the SUB QUERY button under the SUB QUERY tab.

The sub query form

The main query name is specified beside the Main Search text box. Then the name of the sub query is entered beside it. In the CRITERIA section, the user will select variables from the main query table, their corresponding operators and the restricting values. In the TRAITS TO VIEW section, the user will list the variables to be viewed. The query is built by clicking the RETRIEVE button.

Parts of the Sub Query

Sub Query Name - a meaningful name of the sub query which can consist alpha-numeric characters with no blank and special character in between
Main Search - the main query where you intend to subset
RESTRICTING VARIABLES
1. VARNAME - a variable of the main query which is the basis of the subsetting
2. OPERATOR - the operator to restrict the result. If the variable contains numeric data, it can be either of =, >, <, >=, <=, <>. If it contains character data, the LIKE operator can be used. IS operator can be used for either number or character data

Aggregate Query

Averages, minimum values, maximum values and other aggregate group values can be computed in the Retriever using the AGGREGATE query retriever. This retriever is enabled by clicking the “AGGREGATE” button in the AGGREGATE tab and a screen similar to Fig. 10.5.4 will be shown. The aggregate values are computed from a main query and the user has to specify its name in the text box at the topmost of the retriever. A new name for the aggregate query is entered on the lower text box.

In the VARIABLES section, the user specifies the grouping function to apply to the variables of the main query. For variables of numeric type, the Retriever can perform the following grouping functions: sum, count, average, minimum, maximum. For variables, the user can specify the find first and find last function. Variables with GROUP BY option are the basis of the grouping. Users must specify a new variable name for the aggregate value in the ALIAS column.

Fig. 8.4.1 The screen for computing aggregate values

By clicking the RETRIEVE button, the aggregate query is built.

Link Query

The user can link queries through the LINK query retriever which is activated by hitting the LINK button of the LINK tab. As shown in Fig. 10.5.5, the LINK query asks for the names of the two queries to be linked and the variables where the linking will be constructed. RTV-LIST table, which contains the entries of a retrieved germplasm list can also be linked with a query. In the OTHER VARIABLE section, the user needs to list the variables to view in the link query. By clicking the RETRIEVE button, a query labeled after the link name is created.

Fig 8.5.1 Creating link between queries

Parallel Representation of the Query Results

There are situations when data are better viewed in parallel than in serial. The Retriever has a tool for presenting data set in parallel. A data set is in parallel format when the values of one of its variables become the column names. The parallel query has a screen similar to Fig. 8.6.1. It is activated by clicking the PARALLEL button of the main screen. At the topmost part, the user will select a main query to be viewed in parallel. The new name of the parallel query is entered on the lower text box. The Column Heading is the variable whose values will become column names. The Cell Value is the variable whose values will be displayed under the columns created by the parallel variable. The Row Heading is the other variable which will define the rows of the parallel query. The query is built by clicking the RETRIEVE button.

Fig. 8.6.1 Screen for creating a parallel view of a main query

Creating a SetGen list

The germplasm entries retrieved from the Main Query, Sub Query, Aggregate Query can be stored as SetGen List by clicking CREATE LIST. Its form is similar to Fig 8.7.1. On the Source Query, specify the queries to store as SetGen list. Then, on the box beside GID, select the columns or variables f the query which corresponds to GID. Similarly, select the columns that correspond to DESIGNATION, ENTRY CODE and SOURCE if there is any. Click CREATE to make the SetGen list.

Fig. 8.7.1 Screen for creating a SetGen list

Customizing the Switchboard form

The Switchboard form is a customizable form, which allows the user to define his own frequently used commands or queries or to open his reports. The menu items in the Switchboard can open a report, run a query or execute a macro or code. The menu and its items are stored in the Switchboard Items table. The user can add or modify a menu entry in this table.

The Switboard Items table consists of the following fields:

Field	Description
Swtichboard ID	level or id of the menu, 1 - first level menu
Item Number	the order of the items in the menu; 0 - menu name
ItemText	the text or caption that will appear on the screen
Command	specifies whether to open a form, a report, or run a macro or code
Argument	name of the form or report to open, macro or code to run
Options	when the argument is SearchList or SearchStudy form, the macro that corresponds to the choices made on the switchboard menu is run.

The command field can take the following values

Command	Description
1	another switchboard menu
2	open a form in append mode
3	open a form in browse mode
4	open a report
6	Exit application
7	Run a Macro
8	Run a Code in Module
9	Run a query

As an example, if the user desires to have a menu that looks like Figure 10.5.8 with some menu items opening another menu and some running macros, the following will be records of the Switchboard Items table.

SwitchboardID	ItemNumber	ItemText	Command	Argument	Options
1	0	IRRIGATED LOWLAND HRO SYSTEM	0	Default	Irrigated Lowland HRO System
1	1	Hybridization Block	1	4
1	2	Replicated Yield Trial	1	2
1	3	Observational Yield Trial	1	6
1	4	Bulu Nursery Trial	1	8
1	5	Released Varieties	1	5
1	6	F2 Data of Germplasm List	1	7
1	7	Search by Variety Name	7	Query By Name
1	8	Search by Germplasm Identification (GID)	7	Query By GID

Fig. 8.8.1 A sample menu of the Switchboard form

Clicking the Hybridization Block opens another set menu items in the Switchboard. Some of the menu items of Hybridization Block have the following commands. The Item number 10 returns the Switchboard to its Main Menu.

SwitchboardID	ItemNumber	ItemText	Command	Argument	Options
2	0	RYT Nursery	0
2	1	Set up RYT Study	7	RYT Study
2	2	Get Entries of RYT List	7	Get RYTList
2	3	Plot Management	1	3
2	4	Entry Datasheets	3	HRO Nursery	RYT*;
2	5	Entry Data Form	3	HRO Nursery Form	RYT;AllTrait
2	6	View Selected Traits	3	HRO Nursery Form	RYT;SelectedTrait
2	10	Return to Main Menu	1	1

Added Features in version 5.4.

Features and Changes in 5.5

Retrieve List

This is the modified form for retrieving list. You can navigate to the folder of the list from the tree-structure list box on the left side of the form. To retrieve entries of a germplasm LIST into Retriever, type the name of the LIST or select from the list box. Highlight or click on the name. Then, click the Select button.

You can clear the selections by clicking the Clear button.

There are three options to retrieve the entries. One option is to retrieve the entries itself (Exact GID). The second option is to get the entries and those within its specified derivative neighborhood. The Management Neighborhood option include the entries and those germplasms within its management neighborhood. Click the OK button to retrieve the entries based on the tagged or marked option.

Retrieve Study

The above is the modified form of Retrieve Study. Type the name of the study on the Search box or navigate through the folders on the tree-structure list at the left side. Click on the desired study.

Whole dataset

The datasets for the selected study are listed in the Dataset listbox . Select a dataset from the list. The whole dataset can be retrieved by selecting <All Variates> from the listbox beside Variate. If there is only one variate to retrieve, select it from the said listbox. Click the OK button to retrieve the dataset.

Filter the Dataset

If you wish to select more than one variate or you want to filter the dataset, click the check button. A form similar below will appear.

Check the factor or variate you want to retrieve. To filter the data on a particular factor or variate, highlick and click the factor or variate. Select an operator and the value for filtering. Then click the plus button.

To remove a filter, highlight then, click the cross button. Click the OK button to retrieve the dataset based on the specified filtering.

Main Search

This the new form for defining a search.

Search description

Type a search name with no blank in between characters.
Specify a short description about the query.

Filter of the Search

Activate the Filter tab.
Select a variable as filter of your search.
A default Property, Scale and Method will be given below it but you can modify them by selecting your desired property, scale or method from the provided list boxes.
Select a data type.
Select an operator.
Select a value from the list box.

If single value, select a valid value from the list box.
If you wish to specify more than one value, be sure that the operator is LIST. Then a table name will provided to contain the list of values.

Select the table name.
Click on the ellipsis button to specify the different values.

If the table name is RTV-QRY-STUDY, a form similar below will be shown. Type or highlight the study then, click the Select button. Click the OK button to close the form and continue. Click Cancel to ignore the selections.

If the table name is RTV-QRY-GID, a form similar below will be shown.

Click the SELECT LIST button to choose the list of germplasms from the Retrieve List form.

For other suggested table name, a form similar below will appear. To select a value, highlight it and click the down arrow button.

7. Click the Add button

Query Variables of the Search

Activate the Query Variables tab.
Select a variable you wish to retrieve.
Default property, scale and method given are given. Verify they are the ones you are interested.
Select the data type.
Click the Add button

Edit or Delete a variable from the Search Definition

Highlight the variable from the list box of defined Filters or Query Variables.
Click the cross button to delete or the pen button to edit

Transfer Queries from old RTV

Go to ICIS Retriever->Copy Queries from Old RTV menu.
Click the "Link RTV" button. Select the latest RTV. Then click Open.
Click "Transfer Queries" to move the queires to the latest RTV.
Click the "Delete Link" button after the queries are transfered.