From ICISWiki

Contents 1 ICIS 5.5 New Features 1.1 Database change 1.1.1 DMS 1.1.1.1 Additional Tables 1.1.1.1.1 LEVELS Table 1.1.1.1.2 REPRESTN Table 1.1.1.1.3 OBSUNIT Table 1.1.1.1.4 STEFFECT Table 1.1.2 GMS 1.1.2.1 INSTLN table 1.1.2.2 LISTNMS table 1.1.2.3 LISTDATA table 1.1.3 IMS 1.1.4 GEMS 1.2 ICIS.INI 1.3 ICIS32 DLL 1.3.1 Modifications 1.3.2 [DLL SETTINGS] section of the ICIS.INI 1.3.3 New Return Value by DLL function 1.3.4 New Functions 1.3.4.1 GMS_hasCIDSID 1.3.4.2 GMS_addUDField 1.3.4.3 SUP_addPerson 1.3.4.4 SUP_addInstitute 1.3.4.5 SUP_getPerson 1.3.4.6 SUP_getInstittutePrnt 1.3.4.7 DMS_deleteDataset 1.3.4.8 DMS_setRepresName 1.3.4.9 DMS_getRepresName 1.3.4.10 DMS_setEffectName 1.3.4.11 GMS_deleteProgenitorID 1.3.4.12 GMS_deleteOneProgenitor 1.3.4.13 GMS_getNextSequence 1.3.4.14 GMS_getNextName 1.3.5 Modified Functions 1.3.5.1 GMS_findAttribute 1.3.5.2 GMS_listName 1.3.5.3 GMS_getGermplasm2 1.3.5.4 All GMS_add functions 1.3.5.5 getConsData 1.3.5.6 getOunitData 1.3.5.7 getPFLevel 1.4 Launcher 1.5 GMS Search 1.6 SetGen 1.6.1 Changes 1.6.2 Adding/Deleting other progenitors 1.7 InTrack 1.8 Workbook 1.9 ICIS Retriever 1.10 Data Comparison 1.11 GEMS Catalog 1.12 Installation Diagnostic Tool 1.13 Data Validation Tool 1.14 GMS Input 1.15 Browse

ICIS 5.5 New Features

Database change

DMS

• STUDY table: addition of a status field (SSTATUS) to tell whether the record in the STUDY table is a study or folder

0001 - study
0000 - folder
0011 - hidden study
0010 - hidden folder
0101 - locked study
0100 - locked folder
1000 - finalized folder
1001 - finalized study

Any user can hide/unhide a hidden list/folder

A locked list/folder can only be unlock by the user who owns it

The user who owns the study or who creates a folder is the only who can finalize the list/folder

The status of a finalized list/folder can not be changed by a user through the ICIS Applications.

• STUDY table: addition of a hierarchy field (SHIERARCHY) to store the parent folder

Additional Tables

The following tables are added to store the unique IDs for levels, representations, observation units and effects. Most of the tables in the ICIS 5.4.2 and lower versions have these IDs stored in the relation tables. FOr example, OINDEX contains the relations of the observation unit with the levels and the representation. EFFECT table has the relation of the representation with effect and factors. There are no tables in the previous versions where those IDs are the primary keys.

These additional tables are also added for the issue about multiple loading in the DMS at the same time. The absence of these tables will make it difficult to assign IDs or primary keys that are not assigned through other loading process.

LEVELS Table

Stores the distinct level numbers of the factor.

Field name	Description	Type	Length
LEVELNO	The level no of the factor (Primary key)	Number (long)	4
FACTORID	The factor wherein the level is one of its classifications	Number (long)	4

REPRESTN Table

Stores the distinct representation numbers of effect.

Field name	Description	Type	Length
REPRESNO	The representation number of a dataset of an effect. (Primary Key)	Number (long)	4
EFFECTID	The associated effectid	Number (long)	4
REPRESNAME	The name of the representation or dataset name.	Char	150

OBSUNIT Table

Stores the distinct observation units of effect.

Field name	Description	Type	Length
OUNITID	The observation unit. (Primary Key)	Number (long)	4
EFFECTID	The effectid where the observation unit belongs.	Number (long)	4

STEFFECT Table

Stores the effects of study

Field name	Description	Type	Length
EFFECTID	The effect id	Number (long)	4
STUDYID	The study where that effect belongs	Number (Long)	4
EFFECTNAME	The name of the effect	Char	150

GMS

INSTLN table

• new column ULRECID (of type Long which will store the latest LRECID used in GMS database after upload)

LISTNMS table

• LISTDESC - length increased to 255

LISTDATA table

• SOURCE - length increased to 255

IMS

GEMS

ICIS.INI

• Debug option for the DLL should be in the DEBUG key of the [DLL Settings] INI section. DEBUG=NO to turn off the debug mode
• Session ID key is added to run a specific stored procedure named Set_ICIS_Sec_Info with the key's value as its input parameter. This stored procedure is meant to set up the necessary grants to user on tables and other resources.

 [DLL Settings]
 DEBUG=NO
 SESSION_ID=ADMIN

• New INI section [GMSSRCH] with the following INI keys:

1. DEF_TAB - which sets the active tabpage at startup

2. DEF_CASE_FOR_NAME_SEARCH - which sets the default case of name searched.

3. LOCAL_DB_SEARCH - Sets the default sign of the GID searched (i.e. YES for negative)

4. ALPHASORT_NAME - sets the default sorting order of the searchname result (i.e. YES - sorted, NO - not sorted)

5. STUDYSQL - sql statement to use for retrieving DMS data

 [GMSSRCH] 
 #**************************************************************
 # LOCAL_DB_SEARCH - Sets the default sign of the
 #                   GID searched (i.e. YES for negative)      
 # 
 # DEF_CASE_FOR_NAME_SEARCH - indicates the default case for Name
 #                            Search (e.g. UPPER,LOWER,ANY)
 # DEF_TAB - indicates the default tabpage at start-up
 #
 # ALPHASORT_NAME - sets the default sorting order of the searchname result
 #                  (i.e. YES - sorted, NO - not sorted)  
 # STUDYSQL - sql statement to use for retrieving DMS data
 #**************************************************************
 LOCAL_DB_SEARCH=
 DEF_CASE_FOR_NAME_SEARCH=
 DEF_TAB=
 ALPHASORT_NAME=
 STUDYSQL=

• New INI keys for SetGen: More information here...

• Improved INI for In-Track: Before v5.5, all INI keys used by In-Track were under one section [IMS]. In-Track INI keys are now divided into 2 sections:

1. [IMS] - contains all database-related information

2. [INTRACK] – contains all application-related information

ICIS32 DLL

Modifications

• All functions that get the next available primary key are modified. The next ID is now always retrieved from the database.
• For SQLSTATE = 23000 (Unique Constraint Violation when a record is being added), no error message is shown. The add functions will continually call the getNextID and execute the INSERT statement until it succeeds or the 100 attempts are reached. It is assumed that only the primary keys (GID, NID etc) has the unique constraint or else the statements that trap the error is not anymore applicable.
• The ICIS.INI should have AUTOCOMMIT=YES in [SETGEN] section for multiple accesses of the lists.
• Additional tables for DMS to handle the multi-access issue.
• The above changes are done for multi-access of the database.

[DLL SETTINGS] section of the ICIS.INI

• SHOW_PREFID - not mandatory; set to YES if the prefered ID (NSTAT=8) needs to be shown with the Preferred Name;

SHOW_PREFID=YES

New Return Value by DLL function

GMS_UNIQUE_CONSTRAINT = -12 //unique constraint violation

GMS_INVALID_DATA=-11,

New Functions

GMS_hasCIDSID

GMS_hasCIDSID will determine if there are SID and CID fields in the GERMPLSM table. It will return GMS_SUCCESS (1) if those fields are present, otherwise it will return GMS_NO_DATA (0)

Syntax

long GMS_hasCIDSID No arguments, No returns

Returns

GMS_SUCCESS, GMS_NO_DATA

GMS_addUDField

GMS_addUDField adds one record to the local UDFLDS table.

Syntax

long GMS_addUDField (data,szDesc, nszDesc)

Arguments

Argument	Type	Use	Description
data	GMS_UDField *	Input/output	Address of a GMS_UDField structure
szDesc	char *	Input	Description of the user defined field
nszDesc	long *	Input	length of the description string

Returns

GMS_SUCCESS, GMS_ERROR

SUP_addPerson

SUP_addPerson adds record of a person, collaborator or scientist to the local PERSONS table.

Syntax

long SUP_addPerson (data)

Arguments

Argument	Type	Use	Description
data	SUP_PERSONS *	Input	Address of a SUP_PERSONS structure

Returns

GMS_SUCCESS, GMS_ERROR

SUP_addInstitute

SUP_addInstitute adds record of an institute to the local INSTITUT table.

Syntax

long SUP_addInstitute (data)

Arguments

Argument	Type	Use	Description
data	SUP_INSTITUTE *	Input	Address of a SUP_INSTITUTE structure

Returns

GMS_SUCCESS, GMS_ERROR

SUP_getPerson

SUP_getPerson retrieves details of a person from the PERSONS table. The first argument of SUP_getPerson is a pointer to a SUP_PERSONS structure. If the PERSONID element is not zero when the function is called then the details for the corresponding PERSONID are retrieved. If it is zero then the function returns details for the first record if fOpt = FIND_FIRST or for the next available record if fOpt = FIND_NEXT. Any call which fails to find a person information returns DMS_NO_DATA.

Syntax

long SUP_getPerson(data, fOpt)

Arguments

Argument	Type	Use	Description
data	SUP_PERSONS*	input/ output	Address of a SUP_PERSONS structure to contain persons details
fOpt	Long	input	Either FIND_FIRST or FIND_NEXT

Returns

DMS_SUCCESS, DMS_ERROR, DMS_NO_DATA

Comments

If the function is called and there was a problem accessing the database, the function will return DMS_ERROR.

SUP_getInstittutePrnt

SUP_getInstitutePrnt retrieves details of an institute from the INSTITUT table. It can retrieve institutes under a specific parent institute (PINSID) or all institutes in the table. The first argument of SUP_getInstitutePrnt is a pointer to a SUP_INSTITUTE structure. If the PINSID element is not zero when the function is called then, it retrieves details of the institutes under that parent institute. If PINSID = 0, it retrieves all institutes. If fOpt = FIND_FIRST (0), the function returns details for the first record. If fOpt = FIND_NEXT, the next available record is returned . Any call which fails to find a institute information returns DMS_NO_DATA.

Syntax

long SUP_getInstitutePrnt(data, fOpt)

Arguments

Argument	Type	Use	Description
data	SUP_INSTITTUTE*	input/ output	Address of a SUP_INSTITUTE structure to contain institute details
fOpt	Long	input	Either FIND_FIRST or FIND_NEXT

Returns

DMS_SUCCESS, DMS_ERROR, DMS_NO_DATA

Comments

If the function is called and there was a problem accessing the database, the function will return DMS_ERROR.

DMS_deleteDataset

DMS_deleteDataset deletes a dataset of a study. The dataset corresponds to a representation of observations. The records in the OINDEX, EFFECT, VEFFECT, DATA_C and DATA_N of the specified representation are deleted. But the information about the factors, levels and variates are not affected.

Syntax

long GMS_deleteDataset(represno)

Arguments

Argument	Type	Use	Description
represno	Long	input	the representation number of the dataset

Returns

GMS_SUCCESS, GMS_ERROR

DMS_setRepresName

DMS_setRepresName changes the name of a dataset or representation in a study stored in the local database

Syntax

long DMS_setRepresName(represNo,szName)

Arguments

Argument	Type	Use	Description
represNo	long	input	the representation number of the dataset.
szName	Char *	input	Address of NULL terminated string to place the name. It's size should not exceed 150.

Returns

GMS_SUCCESS, GMS_ERROR

DMS_getRepresName

DMS_getRepresName retrieves the name of a dataset or representation

Syntax

long DMS_getRepresName(represNo,szName)

Arguments

Argument	Type	Use	Description
represNo	long	input	the representation number of the dataset.
szName	Char *	input	Address of NULL terminated string to place the name. It's size should not exceed 150.

Returns

GMS_SUCCESS, GMS_ERROR

DMS_setEffectName

DMS_setEffectName changes the name of an effect of a study stored in the local database

Syntax

long DMS_setEffectName(effectID,szName)

Arguments

Argument	Type	Use	Description
effectID	long	input	the id of an effect to which the name should be set
szName	Char *	input	Address of NULL terminated string to place the name. It's size should not exceed 150.

Returns

DMS_SUCCESS, DMS_ERROR

GMS_deleteProgenitorID

GMS_deleteProgenitorID deletes the progenitors of a germplasm in the PROGENITORS table of the local GMS database.

Syntax

long GMS_deleteProgenitorID(gid)

Arguments

Argument	Type	Use	Description
gid	long	input	GID of germplasm for which progenitors have to be deleted

Returns

GMS_SUCCESS, GMS_ERROR

Comments

This function deletes all progenitors of a germplasm from the local database. If gid is positive the function returns GMS_ERROR. Only user with access right greater than 30 is allowed to delete the progenitors.

GMS_deleteOneProgenitor

GMS_deleteOneProgenitor deletes a progenitor of a germplasm in the PROGENITORS table of the local GMS database.

Syntax

long GMS_deleteOneProgenitor(gid, pid)

Arguments

Argument	Type	Use	Description
gid	long	input	GID of germplasm for which progenitors have to be deleted
pid	long	input	the progenitor's GID

Returns

GMS_SUCCESS, GMS_ERROR

Comments

This function deletes only one progenitor of a germplasm from the local database. If gid is positive the function returns GMS_ERROR. The function only allows user with access right greater than 30 to delete a progenitor.

GMS_getNextSequence

GMS_getNextSequence function gets the next sequence number of name type with the specified prefix.

Syntax

long GMS_getNextSequence(ntype, szPrefix, nextSequence)

Arguments

Argument	Type	Use	Description
ntype	long	input	the name type (which is the corresponding FLDNO from the UDFLDS table)
szPrefix	char *	input	Addess of null terminated string which contains the prefix term
nextSequence	long	output	the next number in the sequence

Returns

GMS_SUCCESS, GMS_NO_DATA, GMS_ERROR

GMS_getNextName

GMS_getNextName function provides the next name of a name type having the specified prefix.

Syntax

long GMS_getNextName(ntype, szPrefix, szNextName)

Arguments

Argument	Type	Use	Description
ntype	long	input	the name type (which is the corresponding FLDNO from the UDFLDS table)
szPrefix	char *	input	Addess of null terminated string which contains the prefix term
szNextName	char *	output	Address of NULL terminated string to place the next name.

Returns

GMS_SUCCESS, GMS_NO_DATA, GMS_ERROR

Modified Functions

GMS_findAttribute

- recognizes wild card characters

GMS_listName

- It looks for PreferredID (NSTAT=8) if there is no Preferred Name (NSTAT=1).

GMS_getGermplasm2

- It looks for PreferredID (NSTAT=8) if there is no Preferred Name (NSTAT=1). - It includes the PreferredID with the Preferred Name if SHOW_PREFID=YES in the INI Key

All GMS_add functions

- Reiterates adding the record until there is no Unique Constraint Error or it reaches 100 iteration.

getConsData

-SQL for mySQL/PostGres was corrected

getOunitData

-SQL for mySQL/PostGres was corrected

getPFLevel

-SQL for mySQL/PostGres was corrected

Launcher

• For additional customized items in the Launcher, store it in another text file of the same format as the Launcher.txt. Then, specify the path and name of the text file that contains the items in this INI key

 [LAUNCHER]
 EXTRA_MODULE=D:\ICIS5\EXES\LAUNCHER2.TXT

GMS Search

• Stabilize actual functions and solve bugs
• Add “Find Crosses function”
• Neighborhood, include level/step number and “Exclude DH’s”
• Change Layout to make it more user friendly/"fancy"
• Tabs for names/relatives/neighborhood/inventory/dms data
• Multiple select and copy of items in tabpages
• Add icons for items in Ancestor Tree
• Include important links on wiki and cropforge in About WIndow
• Display number of hits
• Change settings facility (GMS Search-related settings only)
• Passing a parameter with the executable file when Running application
• Searched GID to be defaulted to negative if set in the INI
• Auto-completion of recently typed text for Name Search and GID fields.
• Store used search strings.
• Sorting by Name/Location/Unique ID of the retrieved records

SetGen

• Entering other attributes about a list. The additional attributes should be declared in the UDFLDS table

 FTABLE = LISTNMS
 FTYPE = LISTINFO
 FCODE = <the abbreviation of the attribute>
 FNAME = <attribute term or name>

Click the the button beside Other List Info to show the screen for entering the additional information

• Edit status is assigned to list on edit. A pen icon is shown when a list is on edit status. The list status is

 <User ID>0001

• To allow other user to change status of a list, the following INI key should be defined under [SETGEN] section

 LIST_STATUS_OWNER_ACCESS=NO

• SetGen section (key-value pair) in HELP.INI. Values like http links are examples only.

 [SETGEN]
 AddBatchForm = http://cropwiki.irri.org/icis/help/index.html?batch_mode_processing.htm
 CrossBatchForm= D:\ICIS5\EXES\Help\batch_mode_processing.htm
 DerivativeBatchForm=http://cropwiki.irri.org/icis/help/index.html?batch_mode_processing.htm
 GIDBatchForm=http://cropwiki.irri.org/icis/index.php/TDM_SET_GENERATION_5.4#Batch_Mode_Processing

• For getting the next sequence for a particular name type, combination of 2 INI keys are used. For example:

 NEXT_SEQUENCE_NAMETYPE=1
 NEXT_SEQUENCE_PREFIX=PI

Changes

• List should be created first in the Edit Window before activating any import, interactive or batch tools
• Stabilize actual functions and solve bugs
• Allowing underscore in the germplasm name through INI key (UNDERSCORE_IN_NAMES=YES)
• Importing extra columns from Excel using the Import tool and saving those extra columns as names/attributes/date/location of germplasms
• Update SUFFIX possibilities also for RANGE option in “-“ form. Check if this needs to be only for ‘-‘ window or also for ‘B-‘ screens (NOT YET in B- !) and if it should work for Designation, Entry Code and Source.
• Enhancement of the Add Germplasm Form to make it more user-friendly and similar with the Edit Germplasm form
• Fill With Extra Information (dynamic menu items)

INI key FILL_WITH_CUSTOMIZED_DATA=<path and filename of the text file with the menu items and SQL>

• merging of the new GMS Search with SetGen
• calling of the Help files of the batch forms via F1 key. The ff. INI keys in HELP.INI file correspond to Help files for the diff. batch forms. Values can be a URL or file path (like in the example below).

  [SETGEN]
  AddBatchForm=E:\ICIS5\EXES\Help\B_PLUS.swf
  CrossBatchForm=E:\ICIS5\EXES\Help\Batch_Cross.swf
  DerivativeBatchForm=E:\ICIS5\EXES\Help\B_Minus.swf
  GIDBatchForm=C:\ICIS5\documents\help\batch_mode_processing.htm
 

• “DMS VARIABLE SELECTION” should be changed to “STUDY VARIABLE SELECTION”
• automatic generation of the next sequence of a name type

-The Prefix and the name type should be declared in the INI file

• label of Final list (to be stored in a resource file)

- this will be stored in the ICIS\Exes\ResourceSetGen.rsc

• checking of existence of a particular NSTAT (similar to the NYTPE where it is checked when list is saved or closed)
• calling the Workbook to be one of the options of the right-click menu when list is highlighted (LISTID is the parameter)

- SetGen will write in the ICIS.INI the listID and the List Type of the selected list

- Under the [Workbook] section of the INI are

OpenListId - to contain the listid
OpenListType - to contain the list type

- The corresponding template of the list should be named after the list type. The template folder should be specified in the TmplPath key of the [Workbook] section of the INI file

• When method is import and the name of the imported seed is the same as the source, the NDATA and NLOCN should be the same as the source

- A message asking the user whether to assign the same name date and location as the source when the method is one of the maintenance methods and the name is the same as the source. This will appear when the Start Number=0 in the Naming Convention of the interactive derivative (-) form.

Adding/Deleting other progenitors

• Add progenitor

Add the new progenitor in the local database
If the GID is positive
   create change record for the number of progenitors
   {CTABLE=GERMPLSM, CFIELD=GNPGS, CRECORD=<GID> , CTO=<new GNPGS>, CFROM=<old GNPGS>}

• Edit progenitor

If GID and old PID are positive 
   create change record for the updated progenitor
   {CTABLE=PROGNTRS, CFIELD=PID, CRECORD=<GID> , CTO=<new PID>, CFROM=< old PID>}
   add the new progenitor in the local database
else
  set or update the records in the local database

Note: What if the record is in the local database even the GID and PID are positive?

• Delete progenitor

If GID is negative
   delete the progenitor
else
   create change record to delete the progenitor 
   {CTABLE=PROGNTRS, CFIELD=PID, CRECORD=<GID> , CTO=0, CFROM=<PID>}

InTrack

• Improve interface to make it more user-friendly
• Improve INI file sections
• Searching Data using Barcode
• Checking of Admin rights when logging as Administrator
• Reserve and Deposit functions in Administrator form
• Menu items and toolbar visible when accessing InTrack thru SetGen
• Refreshing of List Selector
• Transaction type of Import to be defaulted to deposit for the Administrator
• Import function at user level
• Selecting a worksheet in Excel while Importing data
• Additional field 'Date' in Import function

Workbook

• Study-status options, same as for lists ! Including the option=9 for deleted Studies (without physical deleting the data from the database)
• Simultaneous loading of study
• Foldering and its manipulation
• Deleting dataset
• selecting variates of dataset to retrieve
• Look up list for location/name type/persons/institutes/methods

• enhance of the data validation
• label of Allele and Marker to be stored in a resource file

-the label names will be stored in ICIS\Exes\Resource\Workbook.rsc

• When called by SetGen, automatically fills up a Workbook template with the LISTID passed by SetGen

-the LISTID will be stored in the OpenListID key of the ICIS.INI

-if that ini key is set, it will open a template named after the listtype and fill it with the list

-it will reset the OpenListID after the process

• Redesign of the Fieldbook form in Workbook
• Storing the representation and effect name in the new tables

ICIS Retriever

• Foldering of Studies similar or the same interface as Workbook
• Folder structure of the lists
• Local studies to be included in the query of the Retrieve by List/Study/Property form
• Selecting variates to retrieve from a study
• Selecting records of study based on the specified values of factor/variate
• In the MAIN SEARCH form, automatically show Germplasm Lists or Studies if the filters or criteria are LIST or STUDY and the operator of the filter is LIST.
• Redesign of the MAIN SEARCH form

Data Comparison

• Retrieval of genotyping data
• Features of the Data Comparison

GEMS Catalog

• will be officially released as part of the ICIS 5.5.1
• separate tool from the database
• automatically link the the database using the info in the INI and registry

Installation Diagnostic Tool

Go to: What's NEW in Version 5.5.1

Data Validation Tool

Go to: What's NEW in Version 5.5.1

GMS Input

Browse