Specimen Archive File Format

A specimen archive is a collection of tab-separated values (.tsv) files packaged as a zip archive with a .specimens extension. The archive can contain any file names or directory structure. For example, a typical archive might have the following structure and file names:

  • mySpecimenArchive.specimens
    • additives.tsv
    • derivatives.tsv
    • labs.tsv
    • primary_types.tsv
    • specimens.tsv

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 TypeDescriptionHashtag for First Line
specimenContains the primary specimen data.# specimens
primary typesA list of primary specimen types.# primary_types
labsA list of labs.# labs
derivativesA list of derivative types.# derivates
additivesA list of additives.# additives

 

ExternalId

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)".

File Type: specimens

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_idint YPrimary keyDraw
global_unique_specimen_idtext50YLIMS-generated global unique specimen ID. Used for joins to results and request data (clinical data joined based on participant/visit).Vial
lab_idnumeric YLIMS 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
ptidtext32YParticipant/subject identifier. Only needs to be unique within the study.Draw
draw_timestampdate/time YDate and time specimen was drawnDraw
visit_valuenumeric YVisit valueDraw
volumenumeric YAliquot 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_unitstext20YVolume unitsDraw
primary_specimen_type_idint NForeign 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_idint NForeign 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_id2int NA 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_idint NForeign 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_datedate/time NDate that specimen was stored in LIMS at each lab. See footnote 1.Event
ship_datedate/time NDate that specimen was shipped. See footnote 1.Event
lab_receipt_datenumeric NDate that specimen was received at subsequent lab. Should be equivalent to storage date. See footnote 1.Event
record_sourcetext20NIndicates providing LIMS (generally "ldms" or "labware")Event
originating_locationnumeric 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_idtext50NUnique specimen numberEvent
parent_specimen_idnumeric NParent unique specimen numberEvent
sal_receipt_datedate/time NDate that specimen was received at site-affiliated labDraw
specimen_numbertext50NLIMS-generated specimen numberEvent
class_idtext20NGroup identifierDraw
protocol_numbertext20NProtocol numberDraw
visit_descriptiontext10NVisit description. The system does not actively use this field, but it still appears in vial and collection grid views by default.Event
other_specimen_idtext50NOther specimen IDEvent
storeddate/time NLIMS-specific integer code for storage statusEvent
storage_flagnumeric NStorage flagEvent
ship_flagnumeric NShipping flagEvent
ship_batch_numbernumeric NLIMS generated shipping batch numberEvent
imported_batch_numbernumeric NImported batch numberEvent
expected_time_valuenumeric NExpected time value for PK or metabolic samplesDraw
expected_time_unittext15NExpected time unit for PK or metabolic samples.Draw
group_protocolnumeric NGroup/protocol fieldDraw
sub_additive_derivativetext50NSub additive/derivative. Appears in vial and collection grid views.Draw
commentstext500NUp to 500 characters are passed through from the comment field in the LIMSEvent
specimen_conditiontext30NCondition stringEvent
sample_numberint Nignored 
x_sample_origintext50Nignored 
external_locationtext50Nignore 
update_timestampdate/time NDate of last update to this specimen’s LIMS recordEvent
freezertext200NFreezer where vials are stored.Event
fr_level1text200NLevel where vials are stored.Event
fr_level2text200NLevel where vials are stored.Event
fr_containertext200NContainer where vials are stored.Event
fr_positiontext200NPosition where vials are stored.Event
shipped_from_labtext32NShipped from lab string.Event
shipped_to_labtext32NShipped to lab string.Event
frozen_timedate/time NDate / time when frozen.Event
primary_volumenumeric Nvolume valueVial
primary_volume_unitstext20Nvolume unitVial
processed_by_initialstext32NInitials of sample processor.Event
processing_datedate/time NDate when processed.Event
processing_timedate/time NTime when processed.Event
total_cell_countint NTotal cell count.Vial
tube_typetext32NSpecimen tube type.Vial
requestablenullable 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:

  • Columns associated with the "draw" are stored in the Specimens table and show up in the LabKey Server UI when you choose to view specimens by Vial Group.
  • Columns associated with the "vial" are stored in the Vial table and show up in the UI when you choose to view specimens by Individual Vials. Certain columns associated with the "draw" appear in this grid as well.
  • Columns associated with an "event" are stored in the SpecimenEvent table and show up when you choose to view the History of a vial. Certain columns associated with the "draw" and the "vial" appear in the history grid as well.

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.

File Type: additives

This file type has one row per additive.

Column Name

Data Type

Max Characters

Required?

Description

additive_idint YPrimary key
additivetext100YDescriptive label
ldms_additive_codetext30NLIMS abbreviation
labware_additive_codetext30NLabWare abbreviation

File Type: derivatives

This file type has one row per derivative.

Column Name

Data Type

Max Characters

Required

Description

derivative_idint YPrimary key
derivativetext100YDescriptive label
ldms_derivative_codetext20NLIMS abbreviation
labware_derivative_codetext20NLabWare abbreviation

File Type: primary_types

This file type has one row per primary type.

Column Name

Data Type

Max Characters

Required?

Description

primary_type_idint YPrimary key
primary_typetext100YDescriptive label
primary_type_ldms_codetext5NLIMS abbreviation
primary_type_labware_codetext5NLabWare abbreviation

File Type: labs

This file type has one row per lab.

Column Name

Data Type

Max Characters

Required?

Description

lab_idint YPrimary key
lab_nametext200YLab name
ldms_lab_codeint NLIMS lab code
labware_lab_codetext20NLabWare lab code
lab_upload_codetext10NLab upload code
is_salboolean NIndicates whether this lab is a site affiliated lab
is_repositoryboolean NIndicates whether this lab is a repository. In order to use specimen tracking, at least one lab must be marked as a repository.
is_clinicboolean NIndicates whether this site is a clinic
is_endpointboolean NIndicates whether this lab is an endpoint lab
street_addresstext200NStreet address
citytext200NCity
governing_districttext200NGoverning district
countrytext200NCountry
postal_areatext50NPostal area
descriptiontext500NDescription

Footnotes

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.

Template Spreadsheet

Use the following spreadsheet as a template when creating new specimen archive files: template spreadsheet.

Data Inconsistencies and Quality Control Flags

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.

Sample Specimen Archive

A sample specimen archive file is available in the LabKey Server demonstration samples. Download the samples here: LabKeyDemoFiles.zip

Discussion

previousnext
 
expand all collapse all