A specimen archive is a collection of tab-separated values (.tsv) files packaged as a zip archive with a .specimens extension. This topic details fields that are specified directly in the archive. There are other fields that are calculated or rolled-up between specimen tables. Learn more about these derived fields in Specimen Fields and Rollup Rules.
When these files are imported into LabKey Server, the data is stored in a hierarchy of tables described in more detail in Specimen Data Destinations.
Each TSV file contains required and optional columns. The required columns are primary/foreign key values and other data that are used to drive the system. The remaining optional columns are included for your convenience and can be left blank or filled with data as desired. Set up custom grids to show, hide, and reorder these columns as desired.
LabKey Server recognizes and imports data from five types of specimen TSV files. The type of file is indicated by the text on the first line of the file. Each specimen data file contained within the archive must begin with one of the "hashtags" listed in the table below. (Note the space following each "#" sign.)
| File Type | Description | Hashtag for First Line |
| specimen | Contains the primary specimen data. | # specimens |
| primary types | A list of primary specimen types. | # primary_types |
| labs | A list of labs. | # labs |
| derivatives | A list of derivative types. | # derivatives |
| additives | A list of additives. | # additives |
Each file has a primary key (additive_id, derivative_id, primary_type_id, etc), collectively referred to as the ExternalId property. Each file must have a value for the primary key column in each row. If not, an error message will be raised indicating that "ExternalId" is a required property. No file contains a column by that name per se, but the error message will indicate which file is misconfigured.
For example, if there is a problem with the derivative_id column in derivatives.tsv, the error message would read:
"ExternalId: Missing value for required property: ExternalId (File:derivatives)".
This file type contains one row for each time each location has possessed each specimen sample, in this case a vial. For example, if a vial has passed from a clinic on to a repository and finally to a lab, three entries for this vial (one for each location) will appear in this file. Required fields are shown with a grey background.
| Column Name | Data Type | Max Characters | Required? | Description | Attribute Of... |
| record_id | int | Y | Primary key | Draw | |
| global_unique_specimen_id | text | 50 | Y | LIMS-generated global unique specimen ID. Used for joins to results and request data (clinical data joined based on participant/visit). | Vial |
| lab_id | numeric | Y | LIMS lab number. Labeled "Site Name" in specimen grid views. Foreign key into the lab list. This field should contain only values found in the lab_id column in the labs.tsv file. | Event | |
| ptid | text | 32 | Y | Participant/subject identifier. Only needs to be unique within the study. | Draw |
| draw_timestamp | date/time | Y | Date and time specimen was drawn | Draw | |
| visit_value | numeric | Y | Visit value | Draw | |
| volume | numeric | Y | Aliquot volume value. May differ across LIMS records; the largest value found is stored in the database. This usage is based on the assumption that volume may decrease as specimen is consumed, so the original volume is what should be tracked by the system. | Draw | |
| volume_units | text | 20 | Y | Volume units | Draw |
| primary_specimen_type_id | int | N | Foreign key into primary type table. This field should contain only values found in the primary_type_id column in the primary_types.tsv file. See footnote 2. | Draw | |
| derivative_type_id | int | N | Foreign key into derivative table. This field should contain only values found in the derivative_id column in the derivatives.tsv file. See footnote 2. | Draw | |
| derivative_type_id2 | int | N | A second foreign key into the derivative table. Functioning identically to derivative_type_id. This field should contain only values found in the derivative_id column in the derivatives.tsv file. Not used by most installations, but available if a single vial/aliquot/slide contained multiple derivative types. |
Draw | |
| additive_type_id | int | N | Foreign key into additive type list. This field should contain only values found in the additive_id column in the additives.tsv file. See footnote 2. | Draw | |
| storage_date | date/time | N | Date that specimen was stored in LIMS at each lab. See footnote 1. | Event | |
| ship_date | date/time | N | Date that specimen was shipped. See footnote 1. | Event | |
| lab_receipt_date | date/time | N | Date that specimen was received at subsequent lab. Should be equivalent to storage date. See footnote 1. | Event | |
| record_source | text | 20 | N | Indicates providing LIMS (generally "ldms" or "labware") | Event |
| originating_location | numeric | N |
LIMS lab number. Labeled "Clinic" in specimen grid views. Foreign key into the lab list. This field should contain only values found in the lab_id column in the labs.tsv file. This field can be used when vials are poured from a specimen at a location different than the location where the specimen was originally obtained. It can record the location where the specimen itself was obtained while the lab_id records the site of vial separation. |
Draw | |
| unique_specimen_id | text | 50 | N | Unique specimen number | Event |
| parent_specimen_id | numeric | N | Parent unique specimen number | Event | |
| sal_receipt_date | date/time | N | Date that specimen was received at site-affiliated lab | Draw | |
| specimen_number | text | 50 | N | LIMS-generated specimen number | Event |
| class_id | text | 20 | N | Group identifier | Draw |
| protocol_number | text | 20 | N | Protocol number | Draw |
| visit_description | text | 10 | N | Visit description. The system does not actively use this field, but it still appears in vial and collection grid views by default. | Event |
| other_specimen_id | text | 50 | N | Other specimen ID | Event |
| stored | date/time | N | LIMS-specific integer code for storage status | Event | |
| storage_flag | numeric | N | Storage flag | Event | |
| ship_flag | numeric | N | Shipping flag | Event | |
| ship_batch_number | numeric | N | LIMS generated shipping batch number | Event | |
| imported_batch_number | numeric | N | Imported batch number | Event | |
| expected_time_value | numeric | N | Expected time value for PK or metabolic samples | Draw | |
| expected_time_unit | text | 15 | N | Expected time unit for PK or metabolic samples. | Draw |
| group_protocol | numeric | N | Group/protocol field | Draw | |
| sub_additive_derivative | text | 50 | N | Sub additive/derivative. Appears in vial and collection grid views. | Draw |
| comments | text | 500 | N | Up to 500 characters are passed through from the comment field in the LIMS. Comments are rolled up into the LatestComments field. | Event |
| specimen_condition | text | 30 | N | Condition string | Event |
| sample_number | int | N | ignored | ||
| x_sample_origin | text | 50 | N | ignored | |
| external_location | text | 50 | N | ignore | |
| update_timestamp | date/time | N | Date of last update to this specimen’s LIMS record | Event | |
| freezer | text | 200 | N | Freezer where vials are stored. | Event |
| fr_level1 | text | 200 | N | Level where vials are stored. | Event |
| fr_level2 | text | 200 | N | Level where vials are stored. | Event |
| fr_container | text | 200 | N | Container where vials are stored. | Event |
| fr_position | text | 200 | N | Position where vials are stored. | Event |
| shipped_from_lab | text | 32 | N | Shipped from lab string. | Event |
| shipped_to_lab | text | 32 | N | Shipped to lab string. | Event |
| frozen_time | date/time | N | Date / time when frozen. | Event | |
| primary_volume | numeric | N | volume value | Vial | |
| primary_volume_units | text | 20 | N | volume unit | Vial |
| processed_by_initials | text | 32 | N | Initials of sample processor. | Event |
| processing_date | date/time | N | Date when processed. | Event | |
| processing_time | date/time | N | Time when processed. | Event | |
| total_cell_count | int | N | Total cell count. | Vial | |
| tube_type | text | 32 | N | Specimen tube type. | Vial |
| requestable | nullable boolean | Not Recommended |
Provides a mechanism for overriding built-in requestability rules. Can be used if the requestability rule cannot be built into the system for some reason, or if a user wants to entirely manage requestability in an external system. We generally recommend using built-in functions for this instead. When NULL, this flag has no effect. |
Vial |
Columns that are "attributes of" the draw, vial or event. Columns in the specimen file can describe the "draw" of the specimen (a.k.a. its "collection"), a "vial" subdivision of a specimen, or an "event" that marks its transfer or processing. These three types of columns exhibit different visibility in the LabKey Server UI:
If additional fields are added to the specimen tables, values for those fields will be included in the exported archive, however values need not be specified for non-required fields in order to successfully import an archive.
This file type has one row per additive.
| Column Name | Data Type | Max Characters | Required? | Description |
| additive_id | int | Y | Primary key | |
| additive | text | 100 | Y | Descriptive label |
| ldms_additive_code | text | 30 | N | LIMS abbreviation |
| labware_additive_code | text | 30 | N | LabWare abbreviation |
This file type has one row per derivative.
| Column Name | Data Type | Max Characters | Required | Description |
| derivative_id | int | Y | Primary key | |
| derivative | text | 100 | Y | Descriptive label |
| ldms_derivative_code | text | 20 | N | LIMS abbreviation |
| labware_derivative_code | text | 20 | N | LabWare abbreviation |
This file type has one row per primary type.
| Column Name | Data Type | Max Characters | Required? | Description |
| primary_type_id | int | Y | Primary key | |
| primary_type | text | 100 | Y | Descriptive label |
| primary_type_ldms_code | text | 5 | N | LIMS abbreviation |
| primary_type_labware_code | text | 5 | N | LabWare abbreviation |
This file type has one row per lab.
| Column Name | Data Type | Max Characters | Required? | Description |
| lab_id | int | Y | Primary key | |
| lab_name | text | 200 | Y | Lab name |
| ldms_lab_code | int | N | LIMS lab code | |
| labware_lab_code | text | 20 | N | LabWare lab code |
| lab_upload_code | text | 10 | N | Lab upload code |
| is_sal | boolean | N | Indicates whether this lab is a site affiliated lab | |
| is_repository | boolean | N | Indicates whether this lab is a repository. In order to use specimen tracking, at least one lab must be marked as a repository. | |
| is_clinic | boolean | N | Indicates whether this site is a clinic | |
| is_endpoint | boolean | N | Indicates whether this lab is an endpoint lab | |
| street_address | text | 200 | N | Street address |
| city | text | 200 | N | City |
| governing_district | text | 200 | N | Governing district |
| country | text | 200 | N | Country |
| postal_area | text | 50 | N | Postal area |
| description | text | 500 | N | Description |
1. At least one of storage_date, ship_date, or lab_receipt_date is needed to accurately order records chronologically. The system will tolerate records with all nulls for these dates, but incorrect ordering may result.
2. Type information is not required (the system will display the type as "unknown"), but the data may not be useful without the type info.
Use the following spreadsheet as a template when creating new specimen archive files: template spreadsheet.
When records in the specimens table disagree about a property of a draw or vial that should be consistent, LabKey Server displays the property as blank. It also flags the records with red highlighting to indicate that quality control is needed for that record. For further details, see Specimen Quality Control.
A sample specimen archive file is available in the LabKey Server demonstration samples. Download the samples here: LabKeyDemoFiles.zip
| previousnext |
| expand allcollapse all |
