Home | Using Katmandoo | Quick Start Guide | How-to | Task Menu | Table of Contents | See Also | Collapse All

System Column and Alias

Overview

In Katmandoo, the system column is an attribute of a table and is part of the database structure. So, system column can not be added or removed from Factor table.

Detail

Following information of columns are stored in the Factor table of the database. Click here for Factor table structure.
  1. Column Name
  2. Column Caption
  3. Column Description
  4. data type
  5. Table name of the Column
  6. Size of the Column Value
  7. Unit of the Column 
The validation rules and validation error message of the Column (Factor) are stored in the the "FactorSubSystem" table of the database.

Column Name

Column name of a column can not be modified because it is part of the database structure. A table can not have more than one column with one column name.

Column Name Naming Convention
Column Name data Type Remarks
Column name suffix with "Id" such as SiteId, TrialId Decimal (16, 2) or Integeter These columns that uniquely identifies a record in a table (eg SiteId in Site table, TrialId in Trial table).

If the "Id" column is used as the foreign key (eg SiteId in SiteYear Table) then it identifies a record in the parent table.
Column name suffix with "Text" such as UnitPositionText and TreatmentText VarChar (255) These columns are generated by the system and uniquely identifies a record in the table ie UnitPosition for UnitPositionText and Treatment for TreatmentText.
Column name suffix with "Name" such as SiteName, GenotypeName and SampleTypeName VarChar (size varies between 32 and 255) These columns with or without combination of other columns are used as the Alternate Key.
Column name suffix with "Note" such as TrialNote, GenotypeNote and TrialAnalysisNote VarChar (6000)* These columns are optional that they may or may not have data and should be used to store any descriptive remarks and comments.

*Why column length is set to 6000, NOT 8000 which is the maximum limit for VarChar data type?
Because maximum size of a page to fit a row in SQL Server 2000 or MSDE is 8K. So, if the row size can not be more more than 8K (8060 characters or bytes to be particular). So, if we change the size to 8000, there will be very high chance that user may 8000 characters in the character which will cause to INSERT statement because the table will have other data as well to insert.

Error Message: Cannot create a row of size <rowlength> which is greater than the allowable maximum of 8060.

See: http://support.microsoft.com/kb/260418/en-us for more information

Maximum Size of Index Keys: http://msdn2.microsoft.com/en-us/library/aa224343(SQL.80).aspx

IMPORTANT NOTE FOR GENOTYPE:
Though Pedigree and GenotypeNote columns both has VarChar(6000), both of them can not have 6000 characters each because of the reason explained above.

Column Caption

In the User Iterface (ie forms), column captions are displayed in stead of column name.

Column caption has following constraints:
  1. Must have at least 2 characters and can have upto 64 characters
  2. Can have any characters other than specified in the "Invalid Character for string Alternate Key (AK) value [ System > Manage Sub-system ]
  3. Must be unique
  4. Must not be a reserved keyword [ Task > System > Manage Reserved Keywords ]

Column Description

The description of the column or factor has following constraints:
  1. Must have at least 2 characters and can have upto 255 characters
  2. Can have any characters other than specified in the "Invalid Character for string Alternate Key (AK) value [ System > Manage Sub-system ]

Column data type

These are the data types that can be used for the column.
data Type Example
String (Alpha numeric) XYZ123, 56MX-12
Integer 1234
Decimal 1234.21, 0.123
DateTime (Date without time)* 31/12/2001
Boolean (Logical) True (Yes), False (No)

*DateTime: System is designed to use Short Date format (dd/mm/yyyy) without any time such as 31/12/2000. So, it is always recommended to use short date while importing data.

Click here to find minimum and maximum size of each of the above data type.

Table Name of Column

Click here for list of the table that are used for the column (not virtual column).

Size of the Column Value

Size (column name in the database if FactorValueMaxLength) of the column value defines the maximum number of characters / digit that the column value can have.
data Type Max Characters that
the Column of the data type can have		 Max Character / Digit
String (Alpha numeric) 255 Maximum number of characters is defined by the size of the System or Virtual Column.

For the case of virtual column value, maximum size is 255 characters.

For "note" column (eg GenotypeNote, TrialNote) the limitation is 6000 characters.

Example:
If a virtual column, "Trial Region", will not have more than 16 characters then set the sze of the Virtual Column to 16. It means Katmandoo will not accept value with more than 16 characters for "Trial Region".
Integer 10 Maximum digit for a Integer Trait is 10 but not exceeding values from negative 2,147,483,648 to positive 2,147,483,647
Decimal (upto 6 decimal places) 16 Maximum digit for a Decimal Trait is 38 including precision, scale and decimal of the value (but not exceeding values from negative 79,228,162,514,264,337,593,543,950,335 to positive 79,228,162,514,264,337,593,543,950,335).

For example
To store 12.345678, the size of the Trait value should be 9 (nine).
DateTime (Date without time) 18 From 01 Jan of Start Year of the Sub-system till 31 Dec of the Current Year of the Sub-system System is designed to use Short Date format (dd/mm/yyyy) without any time such as 31/12/2000.

So, it is always recommended to use short date while importing data.
Boolean (Logical) 5 Either True (ie Yes) or False (ie No)

Unit of the Column

Unit of the column (optional).

Main Form

Use [ Task > System > Manage System Column and Alias ] to load Column Main Form with all the columns in the database.
Illustrative snapshot:
Main Form of Column (Click it to change its size)
  1. Only Edit and Export buttons are enable because column can neither be added nor deleted.
  2. Column Main Form displaying thecolumns.
    • Click here for an illustration of the main form template.
  3. Column Validation Rule form displaying the validation rules for each Sub-system in which the column can be used.
  4. Alias of the selected Column (ie ReplicateNumber in the snapshot).

Setup Form

To edit a column, follow the steps below :
Illustrative snapshot:
Edit Form of Column (Click it to change its size)

As shown in the snapshot, only the Caption and Description of the column can be modified.

Alias of the System Column

A column alias is an alternate name that can be used to represent the System Column / Virtual Column. A System Column / Virtual Column can have one or more aliases but an alias is always associated with a column.

Alias is used while importing data to map column names used in the spreadsheet with the column names used in the database when the spreadsheet uses column name that is not recognised by the database.

Use [ Record > Update Column Alias ] to add or edit alias of the column selected in the column main form.

Click here on tips to shadow System Column using KSCMN (Katmandoo System Column Mapping Name).

Suggestion: Add alias for System Column / Virtual Column using A-Z and 0-9 character only because the import wizard tries to find matching column name with A-Z and 0-9 only when the exact match does NOT exist in the database.

Important Note: Column Alias starting with DiGGer_ must not be deleted as they are used by DiGGer while generating trial design.

Export / Import

Click on Export button in the main form or use [ Record > Export > Export Column Information ] to start Export Wizard to export displayed System Column information.

Note: Import is disabled in the Column Main Form since new column can NOT be added using Katmandoo.

See Also