---
title: "AssayManagement"
canonical: "https://servicedesk.maxgeo.com/space/DataShed4/1270546433/AssayManagement"
format: markdown
---
# Importing
To import a lab assay result file into the normalised assay system in DataShed an import layout must be created. This will allow the header information to be appended the Batch and Batch Details tables and create a normalised data structure for the assay results. .
DataShed supports SIF (space delimited, fixed width text file) and CSV laboratory files. The Import Layout allows all data to be captured including the lab meta data and all associated analytical information. The lab metadata from the header is stored in the **Assay Batch **and **Assay Batch Details **tables and the results are normalised and imported into the **Incoming Assay Data **table prior to being validated.
**Required Data:**
***AssayResult*****: the results reported**
***Batch_No*****: the lab reference number**
***LabElement*****: the analytical element code used by the lab**
***OrigMethod*****: the analytical method used by the lab**
***Sample ID*****: the sample number**
***UnitCode*****: the unit each element is reported in**
*Recommended Data*
*Lab_Job_Date: the date the lab processed the assays (NB this is required for QAQCr batch selection) Lab_Received_Date: the date the lab received the assays*
*LimitLower: the lower detection limit of the analytical method SourceFile: the file name*
*LimitUpper: the upper detection limit of the analytical method*
Additional Data
Dispatch_No: the reference dispatch number
Preparation: the preparation used for analysis SampleCount: the number of samples from the lab file
## Creating SIF Import Layout
**Step One:**
**Create New Layout**
In the System Administration Form Click on the Import Layouts Tab and click on the **NEW **button.
Create a new Layout with a logical name, i.e. Lab_SIF, in the Import Layouts tab. It is advisable to generate a new Layout and Import Mask for each laboratory.
Once a new Layout is visible in the Import Layouts box, highlight the Layout and click **Modify**.
**Step Two:**
**File Type/Mask Setup**
- Setting up the File Type and Mask allows the import layout to be configured to files with different layouts. Each Layout is linked to a lab code.
- Select the **Import Type/Mask **format from the drop down list. If the File type is not listed, click the
**Add New Assay File Type**
Select the Import Type/Mask format from the drop down list. If the File type is not listed, click the **Add New Assay File Type **and enter the new file type and a description. The description is viewed in the drop down list. For a SIF import file, select the basic_type as MASK. The details_row is the row number where the actual results start.
**Note:**
**The details_row may be left blank and DataShed will populate this field when the file format is captured.**
Click the **Modify **button and the Assay File Format Editor will appear.
**Step Three:**
**Assay File Format Editor**
The form shown below will be visible, click on the browse button to locate the assay file to base the import layout on.
- The file type being modified is highlighted in the Scheme Name.
- Browse to a sample file in the Sample File Name box and open.
In the Layout Definition, go to the **Data Item **drop down list. As each selection is made the appropriate data on the SIF file should be swiped and **Saved**.
#### NOTE
**Only the first column of results and element metadata needs to be swiped as subsequent elements are automatically read.**
- Select Read from file as the **Data Source**.
- If a Data Item is not required select Not Used from the Data Source drop down list.
**Assay Result**
**Batch Number**
**Dispatch Number**
**Lab Job Date.**
If only one date is present in a file, select the data item Lab Job Date. This allows QAQCR program to select the batch. If this field is null, QAQCR will not be able to select the batch number. The date format is selected from the drop down list. If the date format is different, a new date format may be added.
If the date format is different, a new date format may be added. Click on the browse button circled in image above.
Select **Add**
Add the format and click **OK**. The format created will be available to select from the dropdown list.
**Lab Element**
**Limit Lower**
**Orig Method (Lab Method)**
**Sample Id**
Make this field as wide as possible to allow for longer sample ids either in this file or in subsequent files.
**Unit Code**
#### NOTE
**In a SIF file the Length should be consistent. In the example above, LabElement, Unit, LabMethod, AssayResult and LowerLimit should all have a length of 8.**
**Substitution**
- Substitution allows for the translation of a non-numeric code into a numeric code. For example X, or< or – which represents below detection values will be treated as below detection limit (BDL) on import.
- Substitution values can be added using the **Substitution **button which will open the table tblSYSImportSubst.
- There must be one entry for each Scheme Name.
- Symbols used by each lab for substitution can be entered and given a substitution value or sysAssayStatus if required.
- Make sure that when a substitution symbol is entered, it is followed by the * symbol, especially if ‘-‘is used. If a lab uses the ‘–‘symbol for a null value, then DataShed will interpret this as a less than detection limit and not a null value.
**BDL****: **Below Detection. The sysresult will be calculated as 0.5*LLD or -1*LLD. This setting is configured in **System Administration > Assay Management > Special Value Options > Below Detection Treatment.**
**OR****: **Over Range. When a result is analysed at the upper range of the analytical method, the Upper detection limit is required to be reported by the laboratory.
**UP**: Update – A substitution value is needed in the Subst_Value
**DEL****: **Delete – Delete the assay result from the incoming assay data.
#### NOTE:
**At least one substitution value must be entered for each Import Layout. In a flat system DataShed will update the sysResult to the substitution value.**
**Step 4**
**Buffer Modifications **tab. It is important to select a base table name for all assay Import Layouts. It is required to be a normalised assay table either tblAssay or tblSYSAssIncomingData.
**Step 5 ****Advanced **tab.
Tick the Normalised box if the data is to be imported into a database with a normalised assay management system.
- The Process Scheme is configured through the Assay Management Setup
- (Import Post Processing Schemes) and is most typically set as Default.
- Select the Laboratory supplying the results from the Laboratory drop down list.
#### NOTE:
**If an assay file is from a new lab, the lab must be added to the Lab library prior to the import layout being created. It is possible to copy a laboratory name and all the associated codes in the Lab Element and the Lab Method libraries.**
**Enter the name of the new lab in the Lab Code and a relevant description.**
Click on **Check Config **tab and check the configuration of the new layout. This will check that all the required information is populated in the layout but it **will not **check the file format against the import layout.
## Creating CSV Import Layout
A CSV file assay import layout differs from a SIF file as the position of the comma indicates the position of the data item and DataShed will read the data string until the next comma is reached. The setup of the CSV file import layout is similar to the SIF file but the capture of the **Data Item **in the **Assay File Format Editor **is different.
Create a new Layout with a logical name, i.e. Lab_CSV, in the Import Layouts tab. It is required to create a new layout for each lab as the Lab code is linked to the layout.
**Import File Type: **Select the Import Mask format from the drop down list. If the File type is not listed, click the Add New Assay File Type and enter the new file type and a description. The description is viewed in the drop down list. For a CSV import file, enter CSV as the basic_type.
**Assay File Format Editor**
- Click the **Modify **button and the Assay File Format Editor will appear.
- The file type being modified is highlighted in the Scheme Name.
- Browse to the sample file and open.
- In the Layout Definition go to the Data Item drop down list and select a Data Item. Capture the item by placing the cursor to the left of the position of the data and clicking Save.
#### NOTE:
**For any headers that are part of a string (i.e. space delimited) treat as a SIF file the Save button. For each, comma delimited field, click the mouse cursor at the start of each comma delimited section and click the Save button**
**Substitutions**, **Buffer Modifications **and **Advanced **settings are configured the same way as in a SIF file import layout.
# Import Assay Data
When the import layout is completed, the file may be imported in DataShed using the Import Data or the Import Data (multifile) tools.
The assays are loaded into an incoming buffer tables with the same format as assay batch, assay batch details and assay incoming data.
## Import Multi File – SIF or CSV Lab files
1. Select Tools > Import Data (Multifile) or click the icon on the toolbar.
2. From the File Lists box choose the **New **button to enter a new file list or select an existing one from the drop down list.
1. In the **Default Values **box, enter the appropriate details. Make sure that the Layout is correct.
2. Choose the destination folder for the original files to be moved to on import It is a good idea to have a folder for original files and a ‘Loaded’ folder, where the files are moved to upon completion of import
3. Click the **Add File **button and select the files for import.
#### NOTES:
**Use the single file import initially to check that the Import Layout is functioning as expected prior to using the multi file option.**
**DataSet for SIF and Assay CSV imports is irrelevant as files from multiple DataSets can be loaded at the same time.**
# Merging
When DataShed runs the assay import via the single file import or the multi-file import options the following steps are carried out.
1. The assays are loaded into an incoming buffer table so that checks can be made to ensure the integrity of the data. DataShed checks the lab method, element, unit and lab libraries to ensure that the data conforms to these libraries. The Generic Method is updated from the Original Lab Method and the Element and Repeat fields are populated from the Lab Element library.
2. The incoming sample ids are matched with the sample ids in each of the assay tables.
3. DataShed calculates the sysResult from the Assay Result. Any text fields are converted to the corresponding substitution value and the text equivalent is entered into the sysAssayStatus field.
4. DataShed automatically merges the records that have been successfully checked. To view the unsuccessful records go to the **Advanced Assay Tools **and any unmerged data is listed. Once the unmerged data has been sorted out, click the **Retry Merge **button. DataShed manually checks and merges the data.
#### NOTE:
**After every import, open the appropriate tables and DataSet in DataShed and view the imported records to confirm they are correct**
## Viewing Unmerged Data
After importing assays, DataShed retains any assays that have not merged into the main assay tables in the Incoming Assays table.
To view the unmerged assay data go **to Tools>Advanced Assay Tools>Unmerged Assay Results **or click the shortcut icon. By clicking on the Data to View radio buttons, unmerged assays are listed by the various categories of: Missing library entries, No matching samples or Other as shown in the diagram above.
To establish the reason for the data not merging, go through the process that DataShed follows in checking and validating each record.
Each of the steps, with the associated error message if they fail, is described in detail below:
## Missing Library Entries
If there are records in the missing library entries, the exception type is set to Missing Library Entry. The library tables are accessed from the **Assay Library **tab.
**Element Groups: **Names of the element groups
**Elements: **Names of the elements and their associated groups
**Method Priorities: **Ranking of generic methods for assay processing
**Generic Methods: **Translation of laboratory methods into standard methods
**Lab Methods Alias: **Analytical methods used by the laboratory
**Labs: **Analytical laboratory
**Lab Element Alias***: *Element name used by the laboratory
**Units: **A list of the units of measurement
**Conversion Factors: **A list of conversions between measurement units
The Comment field gets populated with a list of the codes that are missing from the library tables.
- Is there a Laboratory with the Code of the Incoming Assays?
- Is there a method for that LabCode in the library that matches the “OrigMethod” of the assay?
- Is there a LabElement for that LabCode that matches the LabElement of the assay?
The Comments field will contain information about what library values are missing:
- Missing Lab Element Alias; Unknown: The lab element does not have an element and repeat specified in the Lab Element Alias library
- Original Lab Method; Missing Method Alias: the Lab method does not have a Generic method specified in the Lab Method Alias library.
Any missing library entries listed in the Unmerged Assay Results can be added via the Assay Libraries tab. To Add a Lab Element:
- Click on the Lab Element Alias
- Enter the Lab Code, this must be a lab that is in the Labs library
- Enter the Lab Element code from the lab file
- Assign an element and a repeat code. The Element must be a code in the Element Assay library.
## No Matching Samples
DataShed references the sample register to match the assay results with a sample id in the database. This is commonly tblSampleRegister and is a table that is managed by triggers in the SQL database. A sample id must be present in this table before the assay result will be merged from the Incoming Assay table into the normalised assay table.
If there are records in the no matching samples, the exception type is updated to No Matching Samples. If the same sample Id has been used in different DataSets exception type is set to More Than One Sample Match.
To manage the SampleIDs that are available to DataShed a DataSet must be “current” or “active”. This is managed in the **Active DataSets **tab. All DataSets on the right of this form have sample ids that are available to have assay merged.
## Other
If there are records listed, it often indicates that samples have a null result or a text result such as NL or SNR, which is not listed in the substitution table. There is no error message, but the sysResult field remains NULL.
**Substitutions**
- If the assay result is 0 or NULL, then DataShed is unable to do a calculation on this record. If the Assay Result is NULL, then these records can be deleted. If the Assay Result is 0, check if it means that the sample was not assayed or whether it is below detection limits.
- BDL substitutions ensure that the lower limit is populated and that the substitution for the import layout has been populated correctly. The sysAssayStatus will be populated with BDL if the substitutions table has been populated correctly. For example, a “<” before the results indicates that it is Below Detection Level for that method and “BDL” is inserted in the sysAssayStatus field.
- OR substitution must have the upper limit populated so that the sysresult can be calculated.
**Units:**
The unit the result recorded in must be in the Units library
There must be a conversion from the unit code reported to the preferred unit specified in the Elements library.
Find out what the preferred unit for the element is from the Elements library. This information is stored in the elements library.
Add the conversion from the unit the result was reported by that laboratory to the preferred unit in the elements table. In the example above, the conversion from Kg to g is added.
# Ranking
**Assess data to determine what fields need to be ranking and what method of populating the field is appropriate.**
1. **Go to Database Structures> RHNorm> Attributes and add selection criteria for fields.**
2. **Open SQL Management Studio and add new fields to tblAssayFlat. Save table.**
3. **Edit assay views and add the new assay fields. Save Views.**
4. **Relink DataShed.**
5. **Run ranking Engine for relevant samples.**
DataShed has a process by which the normalised assay values are pivoted so that they are in a “flat” table structure. This enables the assay results to be viewed in a format that related to a geological context with sample metadata associated with a sample result i.e. HoleI_ID, Depth_From and Depth_To.
This process takes the results from the normalised assay table and uses criteria specified by the user to populate the flat assay table, tblAssayFlat. The flat assay table can then be joined to the various sample tables in views to relate the assay result information with the sample metadata i.e. DHAssays (tblVWDHAssay).
Normalised assay table:
Flat Assay Table
DH Assay View
DataShed takes information to complete this process from the **System Administration>Database Structures**
form:
There are 3 structure types:
**Assay**: Configures the normalised assay table (tblAssay)
**LHNorm**: Configures the sample metadata tables (tblDHSamp, tblDHSampQC etc) and links this to the assay table.
**RHNorm**: Links the flat assay table (tblAssayFlat) to the normalised assay table These structures are all preconfigured as part of the Maxwell Data Model.
## Database Structured - Assay Structure
1. Click **Add **Select type = ASSAY
2. **Type in a suitable name. It is Maxwell’s nomenclature to start all structures with AM (Assay**
Management) followed by the abbreviated structure type and table name. (AM_ASS_Assay)
1. Select the new entry in the list box and click **Modify**.
2. Select the name of the normalised Assay Results table in the pull down (tblAssay).
3. Select the name of the Sample Register in the pull down (tblSampleRegister).
4. Once the assay structure is completed and closed, it cannot be edited.
## Database Structure – LH Structure
The LH table is the Sample details table where the identifying features of the sample are stored. For example, tblDHSamp, is the table where the hole id, metres from and to for each sample number are stored. The LH Structure is how the LH table is joined to the table containing the normalised data. There will be multiple LH structures configured, one structure for each table containing sample data.
1. Create a LH normalisation structure by selecting LH Norm and assign a logical name such as LH DH Sample.
2. Select the **Linked table **tab
3. Select the table which will be the LH table i.e. tblDHSample
4. Select the RH object i.e. tblAssay
5. Select the linking fields, usually DataSet and SampleID. This defines the relationship between the LH and RH tables.
## Database Structure – RH Structure
The Configuration of the RH structure controls how the flat assay table is populated.
1. Create a RH normalisation structure by selecting RH Norm and assign a logical name such as RH Assay. There should be one RH structure for every normalised assay table. .
2. Select the new structure and click **Modify**.
3. In the **Main **tab, select the base name of the table which is the RH table i.e. tblAssay
4. Set the static table options to SYS if the database is a SQL database
## RH Attributes
The **Attributes **tab configures how the assay results are to be ranked. There are 3 ways to rank the assay values
- **Preferred Value Calculations**: Populates a field in the flat assay table based the generic methods priority for the element group the result’s element has been assigned to. This means the field defined will always be populated if there is a result for this element. This field **MUST **be added to the table tblAssayFlat in SQL Management Studio.
- **Static**: Populates a field in the flat assay table based on criteria defined in the database. This may is not always populated in the flat assay table. This field **MUST **be added to the table tblAssayFlat in SQL Management Studio.
- **Dynamic**: Can create a virtual field based on criteria specified, if the attribute is selected. Will attach the virtual field to the select LH sample table. In DataShed.
**Preferred Value Calculation Attributes**
The Assay Ranking Engine uses a generic method order for each element group to automatically rank and average assay results. The priorities of various methods are assigned on an element group. Every element group should have different methods assigned with different priorities.
**DataShed requires a field with the same field name as the name defined in the attribute to be present in the Flat Assay table (tblAssayFlat). This must be added in SQL Management Studio.**
1. Enter the Attribute Name. This name must match exactly the name of the associated column name in the flat assay table in SQL.
2. Select the Preferred Value Calculation from the pull down list in the Mode column. Select sysResult in the Field Name column and Single for the Field Type column from the pull down lists. Enter the required Field Length. This relates to the number of decimal points of the sysResult. Up to five decimal points can be viewed.
In the bottom half of the form, select Element from the Definition Type pull down list. Select the Element being defined from the pull down list. Either type or select from the dropdown list the element
1. If the unit to view the result in is different to the sysresult then apply a conversion factor from the system units for that element. Select Conversion from the pull down list in the DefinitionType column and select or type in the conversion factor in Param1.
2. The name (highlighted in red) must be added to tblAssayFlat in SQL Management Studio.
**Static Attributes**
A Static Attribute presents the original data cached into the assigned left hand table. The criteria defined should identify one result to populate the field.
**DataShed requires a field with the same field name as the name defined in the attribute to be present in the Flat Assay table (tblAssayFlat). This must be added in SQL.**
1. Enter the Attribute Name. This name must match exactly the name of the associated static table column in tblAssayFlat.
2. Select the Static option from the pull down list in the Mode column. Select sysResult in the Field Name column and Single for the Field Type column from the pull down lists. Enter the required Field Length. This relates to the number of decimal points of the sysResult. Up to five decimal points can be viewed.
3. In the bottom half of the form, select Field from the Definition Type pull down list. Select the Element being defined from the pull down list in the Param 1 column and enter the element in the Param 2 column. If you want to convert the standard units for that element, select Conversion from the pull down list. Select or type in the conversion factor in Param1.
4. The name (highlighted in red) must be added to tblAssayFlat in SQL Management Studio.
#### NOTE:
**DataShed must be relinked before ranking so that the new table structure can be imported into DataShed. The message below will be returned if the Configuration is checked before the Relink process.**
**Dynamic Attributes**
Dynamic attributes defined in the same way as a static attribute and can be viewed by attaching a virtual field option to the LH table. This is generally used for less commonly used elements or methods.
Assign a logical name, generally element_Method_units in the Attribute definition The top part of the form is filled in with the following:
- Mode is Dynamic
- Field name is sysResult
- Field type is Single
- Field length is 3
To define the criteria for populating this dynamic view the bottom part of the form must be assigned. The user assigns the number of criteria. There must be enough criteria to describe a unique population of results. Generally, the criteria used are element, method (either original or generic), unit code and repeat.
Once the field has been defined, close the RH Attribute tab and **Modify **a LHNorm structure.
1. Move the field to the right hand side.
2. Close the System Administration form.
## Viewing Dynamic Attributes
Highlight the LH table to attach the field to. In this example it is DH Sample. Right click to open menu and select **Change Optional Field**.
Highlight the field to attach to the LH table and move to the Right hand side. Click **Apply**.
Open the DH Sample table and the virtual field will be visible on the far right hand side of the table.
## Ranking Customisation
Ranking Engine Options are setup in the Assay Management tab of DataShed under Advanced Config.
**Tag New Imported Samples for Ranking Engine**: If this option is selected, the SampleID of samples which have been merged into the normalised assay table will be appended to a list so that the next time the ranking engine is run they will be ranked.
**Run Ranking Engine After Import**: The Assay Ranking Engine runs automatically any time assay data is merged either during the import of a file or using the Retry Merge option.
The ranking process can utilise a stored procedure from the SQL database which improves performance. The standard MDS uses the stored procedure usp_DataAssayRankingProcess. If this box is unticked or there is no name in the box, DataShed will revert to the ranking process in the application.
## Ranking Engine
The Ranking Engine may also be manually run in the Advanced Assay Tools.
Select the Table to process then choose one of the following options to select the SampleIDs to rank:
- Table to Process selected only. The SampleIDs that have been merged but not ranked previously will be ranked. The list of SampleIDs is found in the table tblSYSNormProcessStack.
- **Re-rank currently selected DataSets**. Sample to rank are all the sample ids in the DataSets currently selected in DataSet Manager.
- **Re-rank currently filtered Sample Register**. A DataShed filter is applied to the Sample register table and this is the list of sample ids to rank.
## Troubleshooting
**No Ranked Results**
There are a number of reasons why you may not get a ranked result when you expected one. Check the following:
- Are there assay results for that sample in the normalised Assay table?
- The Generic Method and Element Group combination for all results for that sample have a priority defined.
**Note: Adding a new Generic Method**
**In the assay libraries click on the Generic Method box**
- **Add the Generic Method and description.**
- **Click on the Method Priorities box**
- **Add the Generic Method in the relevant Element Group lists and assign a priority. This will ensure that if a preferred value calculation has been selected in the attribute, the result will be ranked.**
DataSet and Sample ID combination does not appear in the processing stack – check that the assay import configuration entries have not been disabled.
- Then tick the Re-rank currently selected DataSets.
- Has an “attribute” been defined for the assay table?
- Is the preferred value calculation set to ‘0’?
**Error Messages**
**Error Adding Records to Flat Table:**
There is a sample Id in the list of samples to be ranked that does not exist in the normalised assay table.
**Solution:**
The sample Ids will need to be deleted from the table, tblSYSNormProcessStack before the results can be ranked.
**Error processing table tblAssay**:
There is a field defined in the Database structure for the RHNorm structure that does not have a field in the SQL table tblAssayFlat.
**Solution:**
Add field to SQL table tblAssayFlat and relink DataShed before running ranking engine.
# Assay Management Configuration
## Import Post-Processing Schemes
An Import Post-Processing Scheme is a logical listing of tables that the incoming assays will be merged to. The Default Post Processing scheme is configured in the standard MDS
Additional Import Post Processing schemes may need to be configured if multiple normalised assay tables are utilised.
## Advanced Config
The **Advanced Config **tab contains setting for the configuration of Importing and ranking functionality.
**Ranking Engine Options**
**T****ag New Imported Samples for Ranking Engine**: DataShed will tag assays for ranking and each record will be added to the process stack. (tblSYSnormProcessStack)
**Run Ranking Engine After Import**: When this is ticked, DataShed will automatically run the flattening and ranking process, after each assay file load. However, if large amounts of historic assays are loaded it is best to disable it and manually run the ranking process. Always check that all the samples have been merged.
**Use Related Samples (duplicates) in Ranking Engine**: the ranking engine will use original samples and duplicate samples when calculating a ranked result. For example, field duplicates may need to be averaged with the original sample. The sample register must contain an OrigSampleID column for this option to be available and all related samples must be in the same DataSet. This setting will only be applied if the DataShed ranking engine is used.
**Highest Repeat No to Use in Ranking**: Choose the number of repeats to average from the pull down list. The default is set to ‘0’. This setting will only be applied if the DataShed ranking engine is used.
**Multi-File Import Option**
**Run Append/Merge on Import**
If this option is selected, the import process with include merging the results into the normalised assay table if all the validation criteria is fulfilled. If this option is not selected it allows the samples to be held in the incoming buffer until you are ready to merge them into the normalised assay table.
**Post Import Action**
This feature allows the system administrator to assign actions to run after imports.
**Special Values Options **
**Below Detection ****T****reatment:**
The detection limit can be set to either –1 times the lower detection limit or to half the lower detection limit.
**WARNING: Do not change the handling of the BDL results unless you are prepared to update and re-rank the entire data- base.**
**Max. No. Optional Fields**
There is no limit to the number of dynamic fields that can be set up in DataShed; however databases have a recommended restriction on optional fields. For an Access database the number is fifteen and for a SQL database the number is twenty- five. The Database Administrator needs to take into account the database type, the speed of the server and the network when choosing the number of optional fields. The default value is set to zero.
**Assay Stored Procedures**
These options are configured to run the standard Maxwell Assay management stored procedures which are standard to the MDS. If it is required to use different stored procedures to Merge, Rank or process the lab QC data, then name of the stored procedure can be entered.
## Check Config
**Check Config**
Select the ‘Check Config’ button to check the configuration of the assay table that has just been configured.
**Check Config for all**
Select the ‘Check Config for all’ button to check the configuration of all the left and right hand tables used in the normalisation process.
**Save to file**
Select the Save to File button to save the description and error messages. Choose the directory to store the DataShed Diagnostic file (*.dsd) and press the Open button. This will save the file and it can now be viewed as a text file.
# Lab Checks and Standards
It is recommended that the labs report the checks and standards that they use for quality control and include the results in the results file. Most labs have a prefix or suffix which identifies lab standards and lab repeats
The stored procedures will process standards and lab QC samples by creating a unique SampleID if necessary and populating the appropriate sample tables with SampleIDs.
The procedure references a library table, tblSYSIncomingDataMasks, which must be configured to suit the client, with the Labcodes, QC_Categories and StandardIDs that are relevant to the client.
**The Process has 3 steps:**
**Setting up Incoming Data Masks:**
This process identifies which records are lab QC results, and configures how the samples are to be processed.
**Updating libraries: Standards Reference and QC Category**
Adds the relevant records to the library tables so that the samples can be inserted into the standard sample table or the sample QC table
**Running the SP Data Driver**
Runs the process to update the sample ids in the incoming assay table and populate the relevant sample tables with the sample ID
## Setting up the Incoming Data Masks
The table tblSYSIncomingDataMasks is populated with the identifiers for the various QC data supplied by the lab. This information is generally supplied at the end of the lab file. The following notes detail how to populate this table.
The % is the wild card for this stored procedure.
**Identifier: **This uses wildcards to isolate the key identifier which will indicate that the sample is a lab dup or standard.
**Lab Code: **The code of the lab as stated in the database library table tblSYSAssLab.
**QC Category: **The QC_Category of the lab check as stated in the table tblLIBQCCategory. Do not populate for “identifiers” that relate to standards. Is Standard: Flag if it is a standard (0 = no, -1 = yes). If this is false then the stored procedure will assume that it is a QC sample.
**StandardID: **Populate with the Standard ID for “identifiers” that related to standards. Leave blank for “identifiers” that are QCsamples. Not essential to populate.
**Append batch no**: Flag (0 = no, -1 = yes) to append the Batch_No to the SampleID. If the standards in the lab file don’t have the Batch_No attached, then set flag to yes. This will prevent duplication of standard SampleIDs, which must be avoided. Only relates to standards, and must be set to no (0) for QC Samples.
**Add Lab to BLANK**: Flag (0 = no, -1 = yes) to append the lab code to the StandardID where the StandardID = Blank. This enables the analysis (in QAQCR) of the Blanks from different labs on separate charts, rather than grouping all of the blanks from all labs together. This flag will add a new record to tblSYSQCReference where necessary (e.g., Blank_ALS_VAN, or Blank_SGS_VAN). Relates only to standard “BLANK”
**STD nonUnique**: Flag (0 = no (SampleID is unique), -1 = yes (SampleID is not unique). If the standard SampleIDs in the lab file are not unique, then this flag generates a unique sample Id by adding the SourceRowNumber to the SampleID. This flag is typically set to no (0) for standard SampleIDs such as “ST01: ST02:” It is generally necessary to set this flag to yes (-1) for standard SampleIDs such as “ST:” without a unique number, or where SampleIDs = StandardID and do not have any prefix (such as “Blank”, “Sarm7B”), and are repeated in the laboratory file. Relates only to standard identifiers.
## Setting up the Libraries
**Repeats. **The QC Category is linked to the library LIB QC Category (tblSYSQCCategory). Any code entered into the Incoming Data Masks table QC_Category must first be entered to QC Category.
**Standards**: All Standard Ids need to be added to the library Standard reference (tblSYSQCStandardReference)
## The Stored Procedure
To run the stored procedure, go to the Unmerged Assay results and click **Run SP Data Driver**
#### NOTES
**The sample tables will only be populated if some of the batch file is already appended into tblAssay. This is the only way that a DataSet can be located for the standards.**
**Ensure that the StandardIDs are populated in the tblSYSQCReference.**
**For the QC Samples ensure that the tblSYSQCCategory table is populated. In the SQL database there are 3 stored procedures:**
- **usp_ProcessInComingDataDriver,**
- **usp_ProcessLabChecksInComing and**
- **usp_ProcessStandardsInComingData**
**To run the process, usp_ProcessInComingDataDriver needs to be executed. This SP will run the other 2 SPs and process the data.**
88