Sample Tracking with IMS
From ICISWiki
This page defined how to use IMS and other ICIS modules for the tracking of DNA samples using for eample in genotpying
Introduction
The ICIS IMS is the inventory management system that is being used for tracking germplasm in genebanks. IMS will now be used as a tool for sample tracking.
Design of the Inventory Management System
See TDM Inventory Management System 5.4.
Sample Tracking Properties
| Property | Value examples | Database Storage |
|---|---|---|
| GID | | |
| Sample | seed, leaf, DNA | Seed, leaf, and DNA samples will be identified with different GIDs; the samples classification will be contained in the METHODS table |
| Source | from plants in trial/crossing block X in location Y, Year Z, leaf/seed GID | |
| Storage | Location A, freezer X, Shelf Y, Box Z | Location will be identified by a Location ID which is linked to the LOCATIONS table; the locations that are going to be used in the inventory must be declared in the UDFLDS table to signify that those are indeed locations for inventory |
| Quantity 1 | Number, weight, volume | Quantities will be determined by its scale (identified by a Scale ID which is linked to the SCALE able in DMS); actual quantity will be contained in the TRANSACTION table |
| Concentration | ug/ul | Concentration will be linked to the Lot as an attribute and will have to be setup both in the UDFLDS and DMSATTR tables. |
| Quantity 2 | DNA quantity: volxconc | Quantities, in this case DNA quantity, will be determined by its scale (identified by a Scale ID which is linked to the SCALE table in DMS); actual quantity will be contained in the TRANSACTION table |
| Gel images | Link to images, e.g. in WIKI | Links to URLs can be saved on the Comments column of the LOT table |
| Location on extraction plate | plate GID, tube GID | The plate can be simulated as a List wherein each well will be the Entry code to the List. In some situation, a plate can contain rows of connected tubes; in that case, the rows can be also simulated as a list and the plate will be the containing folder for the rows/lists. |
| Location on storage plate/tube | plate GID, tube GID | |
| Amount used | Number, weight, volume | This will be contained in the Quantity column of the TRANSACTION table. |
| Use | quantification, quality check, PCR, DTMA genotyping, DNA extraction | This will be contained in the Comments column of the TRANSACTION table |
| User | | The LOT and TRANSACTION table both have a User Id column which links to the users table. |
| Residual quantity | Number, weight, volume | After each Transaction, the amount left is recorded still in the Quantity column. |
| Shipping address | | The requestor/donor is being kept in the PERSONS table. The address for a person is his Institute. Since Institute does not equate Shipping Address at all times, shipping address can then be saved in Comments |
| DNA shipment plate GID | need plate design tool | |
Setting up the IMS Database
See IMS Setup. It serves as a manual to setting up the IMS Database.
For the In-Track application of ICIS, setting up an IMS database also means populating tables in GMS and DMS that are being referred to by some IDs in the IMS tables. Aside from the steps described in the manual, there are some more steps that need to be added. These steps are required to properly store samples which are contained in plates and/or tubes.
Adding List Types in the UDFLDS table
To be able to include plates and rows in the inventory, those have to be declared as list types in the UDFLDS table.
| FLDNO | FTABLE | FTYPE | FCODE | FNAME |
|---|---|---|---|---|
| 708 | LISTNMS | LISTTYPE | PLT | 96-WELL PLATE/RACK OF TUBES |
| 709 | LISTNMS | LISTTYPE | ROW | ROW OF TUBES/WELLS |
Attributing a Location to a Plate/Rack or Row
To be able to store the location of the plate or the rack containing the samples, the next two entries have to be added in the UDFLDS table and DMSATTR table respectively. The plate/rack and row were simulated as a list. However, in the schema of list management, there is no column that connects to the Location table. Instead, it is needed to declare a field type "List Location" in the UDFLDS table and then use this declaration to attach the location to the list (which, in this case, is the plate/rack or row).
| FLDNO | FTABLE | FTYPE | FCODE | FNAME |
|---|---|---|---|---|
| 726 | LISTNMS | LISTINFO | LISTLOC | LOCATION OF LIST |
| DMSATID | DMSATYPE | DMSATAB | DMSATREC | DMSATVAL |
|---|---|---|---|---|
| <sequential number> | 726 | LISTNMS | <LISTID> | <Location value or LOCID> |
Attributing the Concentration Property to a Lot (Sample)
To be able to store the Concentration property of the sample, the following has to be declared in the following tables:
TRAIT
| TRAITID | TRNAME |
|---|---|
| 1500 | SAMPLE STOCK |
SCALE
| SCALEID | SCTYPE | SCNAME | TRAITID |
|---|---|---|---|
| 137 (for example) | D | ng/μL | 1500 |
UDFLDS
| FLDNO | FTABLE | FTYPE | FCODE | FNAME | SCALEID |
|---|---|---|---|---|---|
| 510 (for example) | LOT | LOTINFO | LOTCON | CONCENTRATION OF SAMPLE | 137 |
DMSATTR
| DMSATID | DMSATYPE | DMSATAB | DMSATREC | DMSATVAL |
|---|---|---|---|---|
| <sequential number> | 510 | LOT | <LOTID> | 1030.88 |
Attributing the Absorbance Property to a Lot (Sample)
To be able to store the Absorbance property of the sample, the following has to be declared in the following tables:
TRAIT
| TRAITID | TRNAME |
|---|---|
| 1500 | SAMPLE STOCK |
SCALE
| SCALEID | SCTYPE | SCNAME | TRAITID |
|---|---|---|---|
| 138 (for example) | D | A260/A280 | 1500 |
UDFLDS
| FLDNO | FTABLE | FTYPE | FCODE | FNAME | SCALEID |
|---|---|---|---|---|---|
| 511 (for example) | LOT | LOTINFO | LOTABS | ABSORBANCE OF SAMPLE | 138 |
DMSATTR
| DMSATID | DMSATYPE | DMSATAB | DMSATREC | DMSATVAL |
|---|---|---|---|---|
| <sequential number> | 511 | LOT | <LOTID> | 1.93 |
An Example of Location Table for IMS Sample Tracking
| LOCID | LTYPE | LNAME | LABBR | SNL3ID | SNL2ID | SNL1ID | CNTRYID |
|---|---|---|---|---|---|---|---|
| -4 | 1500 | Box a | BX-a | -3 | -2 | -1 | 140 |
| -3 | 1500 | Shelf 1 | SF-1 | 0 | -2 | -1 | 140 |
| -2 | 1500 | Freezer A | FZ-A | 0 | 0 | -1 | 140 |
| -1 | 1500 | Freeze Dry Room | FDR | 0 | 0 | 0 | 140 |
Implementing Sample Tracking in InTrack
After using all the information above, setting up the IMS Database to be suitable for sample tracking data, and with some sample data, InTrack is now capable to be used in Sample Tracking. All the properties that need to be stored can be stored with the current IMS system, except for the DNA shipment plate GID.
However, there are still some functions that need to be added to InTrack to be able to show all properties that pertain to a certain sample. Moreover, there should be functions in InTrack or at least an application in ICIS that can input all the properties in the said tables above without having to do it manually in the database, thus automating the whole process of setting up the IMS.
- adding list types in UDFLDS table / adding entries to the UDFLDS table
- attributing a location to a plate/rack or Row / attributing a location to a list
- adding entries to the DMSATTR table
- attributing properties (e.g. concentration, absorbance) to a lot
Basic Functions for Sample Tracking
| Function | Assumption | Database Action |
|---|---|---|
| Add Lot | Existing entity | Add entry to IMS_LOT |
| Add entity/entities to existing List | Existing entity/entities; existing List | Add entry/entries to LISTDATA |
| Add entity/entities to new list | Existing entity/entities | Add entry to LISTNMS (new List); add entry to LISTDATA (entity/entities) |
| Reserve | Existing Lot | If TRNID+LOTID+RECORDID+SOURCEID combination is unique, add entry to IMS_TRANSACTION, otherwise, update the TRNQTY of the IMS_TRANSACTION entry where the TRNID+LOTID+RECORDID+SOURCEID is the same as the one to be reserved ; TRNQTY should be negative (-) |
| Deposit | Existing Lot | If TRNID+LOTID+RECORDID+SOURCEID combination is unique, add entry to IMS_TRANSACTION, otherwise, update the TRNQTY of the IMS_TRANSACTION entry where the TRNID+LOTID+RECORDID+SOURCEID is the same as the one to be deposited; TRNQTY should be positive (+) |
| Save subset of a List to another List | | Add new List to LISTNMS; add subset of old List to new List in LISTDATA; delete subset of old List in LISTDATA |
| Add requestor/donor | | Add entry to PERSONS |
| Add location | | Add entry to LOCATION |
| Cancel deposit (admin) | Existing transaction | Update TRNSTAT in IMS_TRANSACTION from 0 (anticipated) to 9 (canceled); add entry to CHANGES table |
| Cancel reserve (admin) | Existing transaction | Update TRNSTAT in IMS_TRANSACTION from 0 (anticipated) to 9 (canceled); add entry to CHANGES table |
| Commit deposit (admin) | Existing transaction | Update TRNSTAT in IMS_TRANSACTION from 0 (anticipated) to 1 (confirmed) |
| Commit reserve (admin) | Existing transaction | Update TRNSTAT in IMS_TRANSACTION from 0 (anticipated) to 1 (confirmed) |
| Repack material | Existing Lot | Update SCALE_ID in IMS_LOT; add entry to SCALE if necessary; update TRNQTY in IMS_TRANSACTION; add entry to CHANGES table |
| Move material | Existing Lot | Update LOCATION_ID in IMS_LOT; add entry to LOCATION if necessary; add entry to CHANGES table |
| Update Lot details | Existing Lot | Update entry in IMS_LOT |
| Update Transaction details | Existing Transaction | Update entry in IMS_TRANSACTION |
| Split material (admin) | Existing Lot; only one Lot can be split into two Lots | Add new entry to Lot; add entry to IMS_TRANSACTION for the new amount of both Lots; add entry to CHANGES table |
| Merge materials (admin) | Existing Lots; two or more Lots combined into one Lot; only Lots with same GID can be merged | Add entry to Lot (combined materials); update TRNQTY and TRNSTAT in IMS_TRANSACTION for the amount of the new Lot; update STATUS of Lots that were merged from 0 (active) to 1 (closed) in IMS_LOT; add entry to CHANGES table |
