Configuring the Data Entry Application

Forms See also
MDE—JobEdit Form

MDE—Jobs Form

MDE—OpenJobs Form

Finding Jobs using a Workbook Search

Entering Weights and Volumes in a Workbook

Entering Raw Results in a Workbook

Entering Final Results in a Workbook

Validating Results

List of data entry commands

Overview

A workbook comprises scheme and analytes for a selected production or laboratory batch job, presenting the CUID scheme analyte results in a spreadsheet, allowing for result entry, capture and calculation from formula, as well as the use of commands to manipulate the data within the spreadsheet.

Various aspects of the workbook environment can be configured by the LIMS administrator to limit access, fix the type of data expected, and to customise the spreadsheet.

This process is pertinent to LIMS and laboratory managers who configure the LIMS, and workbook sessions in particular.

Process

Set the AccessLevel Program Settings - MDE to configure user access to the Data Entry application.

Enable the ShowTipOfTheDay Program Settings - MDE to display tips to users upon start up of the Data Entry application, and enter the tips, with one tip per line, in a file, place the file in a folder under the CCLAS root folder defined by the CCLASRootDirectory Program Settings—GLOBALSETTINGS for CCLASMGR, then set the TipofTheDayFile Program Settings - MDE to the folder and file name, for example %CCLASRootDirectory%TIPS\MDE.TIP.

Enable the MainListViewGrid Program Settings - MDE to present the list-view panel with grid lines.

Configuring Workbook Job Search

Jobs are located either from a nominated view (refer to Configuring Database Views for Workbook Job Search below) or directly from the database tables. Where they are located from database tables, the jobs are located either via client and project, or directly by job. The list of clients comes from the active list of clients in the PROFCLIENT table which are a subset of clients in the CLIENT table.

Access workbook job data by database view, or database tables using the PROFJOB table directly, or using the CLIENT, CLIENT_PROJECT and PROFJOB tables in a drill-down:

Two methods exist to filter records returned to the list-view panel as each node is selected:

Define the columns of the grid based on the selected node type:

  • Clients node (only presented where job data is accessed by database tables using client-project-job drill-down)—Set the MainListViewColumnn Program Settings - MDE to specify which columns from the CLIENT table are presented in the grid.
  • Projects node (only presented where job data is accessed by database tables using client-project-job drill-down—Set the Sub1ListViewColumnn Program Settings - MDE to specify which columns from the CLIENT_PROJECT table are presented in the grid.
  • Jobs node—Set the Sub2ListViewColumnn Program Settings - MDE to specify which columns from the database view or PROFJOB table are presented in the grid.
Configuring Database Views for Workbook Job Search

When searching for jobs for a workbook session, a database view allows for a logical presentation of data from multiple tables, with the benefits that the search then only operates on that one database view. Database views are created within the database itself.

Without using a database view, only properties from the PROFJOB table are displayed in the job list-view panel. Database views are used to construct a logical PROFJOB table. All queries on the PROFJOB or PROFJOBUSER tables are done on the view instead. Refer to Using Database Views.

Using a database view allows for the following to be displayed in the job browser:

  • Additional properties of the job (that is, those site-specific properties kept in the PROFJOBUSER table)
  • Additional properties of other objects associated with the job (for example, displaying the client name instead of the client code, displaying the contact name instead of the contact code, or contact and address details)
  • Aggregated statistics from any child table of the PROFJOB table, but grouped on the job number (for example, a dynamic count of the number of samples in a job, or a dynamic count of the samples not validated, or the number of tests in the job that belong to a particular laboratory section).

The application still maintains the links to other CCLAS EL tables, for example, CLIENT_CONTACT, PROFJOB_SCHEME.

Where the Data Entry application displays clients in a tree-view, then the PROFCLIENT_CLIENT and PROFCLIENT_PROJECT tables are joined to the defined view also.

Enable the ListViewByClient Program Settings - MDE to display clients in a tree-view in the Data Entry application.

Since the STAFFPROGRAMEDIT table defines the tables and views that are available from which to select the fields, these settings are not specific to the laboratory or staff code used. Therefore, if different views are used for different staff or laboratory codes, then the user must take extra care when selecting the fields for the browser column headings.

Set the browser of the specific CCLAS EL application to point to the view instead of to the PROFJOB table.

To configure the Data Entry application to point to a database view, set the BrowserView Program Settings - MDE to the name of the view. The view name from the Example Database View on the PROFJOB Table above is PROFILE_PJVIEW.

Set the columns on display in the browser to come from the view instead of from the PROFJOB table.

To configure the columns on display in the Data Entry application, set the Sub2ListViewColumnnProgram Settings - MDE to the names of the columns in the view, for example:
Sub2ListViewColumn1=LABCODE,100,Lab
Sub2ListViewColumn2=PRO_JOB,100,Job
Sub2ListViewColumn3=CLI_LABCODE,100,Client Lab
Sub2ListViewColumn4=CLI_CODE,100,Client
Sub2ListViewColumn5=PROJ_CODE,100,Project
Sub2ListViewColumn6=ORDERNO,100,Order Number
Sub2ListViewColumn7=RECEIVED,120,Received,!YYMMDD HH:MM
Sub2ListViewColumn8=REQUIRED,120,Required
Sub2ListViewColumn9=PRODUCTCODE,100,Product
Sub2ListViewColumn10=JOBTYPE,50,Type
Sub2ListViewColumn11=CLI_NAME,150,Client Name
Sub2ListViewColumn12=NOUNKS,50,Number of Samples

The CCLAS EL application then looks for these fields in the view for display in the browser.

Using Barcodes to Locate Jobs

Where job sheets, samples or racks have barcode labels, to set up a barcode reader to locate jobs for workbook:

Create the barcode VBScript file in the folder defined by the ConfigDirectory Program Settings—GLOBALSETTINGS for CCLASMGR, and set the BCScriptFile Program Settings - MDE to the name of the script file. Code the GotBarcode (sJobNumber As String) subroutine in the script file to modify the clsMDEOpen object and call the clsMDEOpen.OpenMDESheet subroutine which displays the scanned content in the toolbar for the user to then open the associated job.

Note: Contact your CCLAS support representative for coded barcode script files.

Data entry often requires custom forms which are tailored to a laboratory's requirements, typically used to locate jobs for a workbook session using custom criteria. To add the name of a custom form to the Command main menu option in the Data Entry application:

The user only has access to custom forms where they also have access to the nodes in the Data Entry application.

Configuring the Scoping of Workbook Sessions

The ReportLDFn Program Settings - MDE defines the list of workbook job report templates available for generating a report of jobs located from a workbook search.

After jobs are opened for a workbook session, the list of schemes from those jobs are presented to the user. Where the ExpandAllOnOpen Program Settings - MDE is enabled, then all schemes are expanded by default, to show each analyte.

Where the UseShortname Program Settings - MDE is enabled, then analyte codes are displayed using the analytes' short names.

Where a scheme's Group code is defined, then only staff members of that staff group may access the scheme for data entry, otherwise any staff member has access to the scheme.

A data entry session is run in one of the following modes to configure the type of data that is entered or captured:

  • Weight/Volume mode—A CUID's weight and volume, used to determine final results by applying weight/volume corrections to raw results.
  • Raw results mode—A CUID scheme analyte's Raw value which is uncorrected for weight and/or volume. Dilutions are entered in this mode, if required.
  • Final results mode—A CUID scheme analyte's Final value which is corrected for weight and/or volume, and used to determine the CUID scheme analyte's FormattedValue.

The ResultType Program Settings - MDE defines the default result mode for a data entry session.

For Raw results mode, enable the RawFinalModeChecked Program Settings - MDE is enabled to allow the user to load both raw and final values into the spreadsheet.

The workbook for a data entry session can display in default style with samples down the page and analytes across the page, or with analytes each row with for each sample. The DefaultStyle Program Settings - MDE defines the default data entry style for a data entry session.

Customising the Scoping of Workbook Sessions
  • In situations where the user needs to filter the samples from the selected jobs for loading into the spreadsheet:

    • Where the MDESampleUserQuery.SCR script file is located in the folder defined by the ConfigDirectory Program Settings—GLOBALSETTINGS for CCLASMGR and:

    • The AfterTreeUpdate subroutine exists within the script file, when a node in the tree is checked, then the script is run to perform custom logic.
    • Where the CanEdit subroutine exists within the script file, before a workbook is opened, then the script is run to determine whether the data entry session can proceed, and can cancel the data entry session, if required.
    Configuring the Spreadsheet Presentation

    The column width of an analyte defaults to the scheme analyte's Column Width.

    Set the Edit1ListViewColumnn Program Settings - MDE to specify the columns of sample details displayed for a workbook presented in the default style.

    Set the Edit2ListViewColumnn Program Settings - MDE To specify the columns of sample details displayed for the workbook presented in analytes each row style.

    Where the AllowTrailers Program Settings - MDE is enabled, then extra empty columns appear on the spreadsheet, for use in calculations. Otherwise, the F1Book contains the only a column for each loaded analyte.

    The Extra Rows Program Settings - MDE defines the number of empty rows added to the bottom of the spreadsheet.

    Customising the Spreadsheet Presentation
  • In situations where the user needs to filter the samples from the selected jobs for loading into the spreadsheet:

    • Where the MDESampleUserQuery.SCR script file is located in the folder defined by the ConfigDirectory Program Settings—GLOBALSETTINGS for CCLASMGR and:

    • The AfterTreeUpdate subroutine exists within the script file, when a node in the tree is checked, then the script is run to perform custom logic.
    • Where the CanEdit subroutine exists within the script file, before a workbook is opened, then the script is run to determine whether the data entry session can proceed, and can cancel the data entry session, if required.

    Where the VBScript defined by the AutoRunAfterLoad Program Settings - MDE is located in the folder defined by the ConfigDirectory Program Settings—GLOBALSETTINGS for CCLASMGR, then it is run to typically configure the clsSheet object.

    Note: Contact your CCLAS support representative for coded script files.

    Configuring the Display and Entry of Data into the Spreadsheet

    The colour of a spreadsheet cell indicates the state of the underlying data. The various colours are configurable with program settings.

    Cell status Description Cell background colour
    Locked A cell is locked if the sample analyte status matches a status in the list of statuses defined by the AnalyteLockedStatuses Program Settings - MDE. Note that non-level 3 users cannot overwrite locked cells. Defined by the LockedBackgroundColour Program Settings - MDE.
    Check required A sample/scheme/analyte requires a check when the result is over the upper limit for the scheme, or below the detection limit for the scheme and CheckLDL Program Settings—GLOBALSETTINGS for CCLASMGR is enabled. Defined by the DetLimitFailureColour Program Settings - MDE.
    Non-validated result A sample/scheme/analyte is non-validated when it is denoted to be Status active and the data has not been validated. Defined by the NVLResultBackgroundColour Program Settings - MDE.
    Quality control warning A sample/scheme/analyte quality control warning can be raised if it is a quality control sample out of range of acceptable limits, but within warning limits, or an unknown sample out of product specification warning limits. Defined by the QCWarningColour Program Settings - MDE.
    Quality control failure A sample/scheme/analyte quality control failure can be raised if it is a quality control sample out of range of acceptable warning limits, or an unknown sample out of product specification limits. QC failure colouring has precedence over formula colouring. Defined by the QCFailureColour Program Settings - MDE
    Quality control ignored A sample/scheme/analyte quality control status can be ignored. Defined by the IgnoredBackgroundColour Program Settings - MDE.
    Formula Cell contains a formula used to calculate the displayed result. Defined by the FormulaBackgroundColour Program Settings - MDE.
    String or OLE data type Cell has a string or OLE (object linked or embedded) data type. Defined by the StrOleTypeBackgroundColour Program Settings - MDE.
    Date data type Cell has a date data type. Defined by the DatTypeBackgroundColour Program Settings - MDE.
    Missing The sample is not profiled for the scheme and analyte, that is, the PROFJOB_CUID_SCHEME_ANALYTE record does not exist. Blue

    To automatically lock cells that contain a formula, enable the AutoFormulaLock Program Settings - MDE.

    Often it is a requirement that non-level 3 users are limited as to which data can be modified, based upon the status of the data. Set the AnalyteLockedStatuses Program Settings - MDE to contain the list of statuses where access is barred for specific users. When those users view data in the Data Entry module, then the cells that have a status matching one of the barred statuses is locked. For example, to ensure non-level 3 users cannot modify validated, IGN, NA, IS or LNR sample analytes, the program setting is set to CPL,IGN,NA,IS,LNR.

    Enable the AllowUnlock Program Settings - MDE to permit level 3 users to unlock locked cells.

    Enable the UnlockOnlyLocked Program Settings - MDE to unlock only locked cells upon cell unlocking.

    Enable the ClearAllStatusOnUnlock Program Settings - MDE to clear the worklist status on all unlocked cells, or disable the setting to only clear the worklist status on cells that contain a LNR, IS or NA worklist status.

    A scheme analyte's Formatting mask defines the default format of the spreadsheet column containing that analyte.

    Where samples or racks have barcode labels, to set up a barcode reader to locate samples in a spreadsheet:

    • Enable the BCScan Program Settings - MDE.
    • Set the BCTerminatorCode Program Settings - MDE to the ASCII character of the barcode input terminator.
    • Create the barcode VBScript file in the folder defined by the ConfigDirectory Program Settings—GLOBALSETTINGS for CCLASMGR, and set the BCScriptFile Program Settings - MDE to the name of the script file. Code the GotBarcode (sCode As String) subroutine in the script file to launch another VBScript to code spreadsheet manipulation commands, for example, SheetBCScan.VBS which processes all barcode inputs to operate on the spreadsheet, for example, to move to a particular cell.

    Note: Contact your CCLAS support representative for coded barcode script files.

    Enable the ShowCellTips Program Settings - MDE to allow for the toggling on of cell tips to display expected results when the user hovers over a cell in the spreadsheet.

    Enable the AlphanumericMode Program Settings - MDE to allow for the toggling on of alphanumeric data entry for cells where the analyte's Data type is Number.

    To configure the entry of weights:

    • Enable the CheckWeightRange Program Settings - MDE to provide out-of-range warnings when a weight that is out of range for the method is entered by the user, allowing them to either accept the value or enter an in-range value.

    To configure the entry of raw results:

    • Enable the WeightToVolumeCorrect Program Settings - MDE to calculate a CUID scheme analyte's Final value using the CUID's weight and volume and the CUID scheme analyte's Raw value and Dilution.
    • Where an analytical result is taken from an instrument without using the default weights and volumes (for example, an instrument that measures mg/L in the test tube but not mg/g in the sample), then enable the WeightToVolumeCorrect Program Settings - MDE to apply the following correction to produce a CUID scheme analyte's Final value:
      • Final = Raw * (ExpWt/ActWt) * (ActVol / ExpVol) * ExpVol / ExpWt * AnalyteDil
      • Example: Final = 1730 * (0.25 / 0.26) * (250 / 250) * 250 / 0.25 * 1 = 1730 * 0.962 * 1 * 1000 * 1 = 1664200
    • Where an analytical result is taken from an instrument that is calibrated using the default weights and volumes (for example, the instrument measures mg/g in the sample, not mg/L in the test tube), then disable the WeightToVolumeCorrect Program Settings - MDE to apply the following correction to produce a CUID scheme analyte's Final value:
      • Final = Raw * (ExpWt/ActWt) * (ActVol / ExpVol) * AnalyteDil
      • Example: Final = 1730 * (0.25 / 0.26) * (250 / 250) * 1 = 1730 * 0.962 * 1 * 1 = 1664.201

    To configure the use of formulas:

    To configure the screen file used to enter user notes, set the UserNotesEntry Program Settings - MDE to the form heading, full path and file name of the screen file.

    Customising the Display and Entry of Data into the Spreadsheet

    Where the clsSheet.FireCellSelection property is true, then the VBScript file contained in the clsSheet.CellSelectionScriptFile is run when a cell on the spreadsheet is selected.

    Where the clsSheet.FireValueChanged property is true, then the VBScript file contained in the clsSheet.ValueChangedScriptFile is run after the content of a cell on the spreadsheet is changed.

    Where the clsSheet.FireAfterChange property is true, then the VBScript file contained in the clsSheet.AfterChangeScriptFile is run after the content of a cell on the spreadsheet is changed.

    Configuring the Collection of Data from Instruments

    Whilst data entry can be via the keyboard, an instrument can be selected for data capture. Defining the source of the data, the protocol (if any) used to interact with the instrument, and how the data is decoded, is managed by an Instrument Format File (IFF) located in the IFFScripts folder. The instrument format files contain VBScript language.

    • The Instrumentn Program Settings - MDE for the current user's staff account contains the IFFs and hence the available instruments.
    • If no such program settings exist for the user's staff account, then the same settings for the CCLASMGR staff account contains the IFFs and hence the available instruments.
    • If no such program settings exist for the CCLASMGR staff account, then the list of IFFs in the IFFScripts folder is used.

    The mode of the data from the instrument is presumed to be as that selected for the workbook session: weights and volumes, raw values or final values.

    The Main subroutine in the IFF runs upon instrument selection to load the instrument string decoding details.

    Testing the IFF for Data Capture

    Use the Data Entry application to test the IFF for data capture. Where required, the IFF can be edited directly from a workbook session in the Data Entry application.

    Edit the IFF from a data entry session

    Calculating Results

    Formulas are used to calculate results from entered data and parameters. The default formula for a cell is set by the scheme analyte's Formula.

    Defined names are used to assist with formula definition. Where the spreadsheet is not opened in read-only mode, defined names can be added, deleted or modified.

    When a sheet is initially loaded, the list of defined names contains the following definitions:

    • CUID
    • EXTERNALIDENT
    • SAMPLEIDENT
    • SAMPLETYPE
    • Analyte1
    • Analyte2
    • etc...
    • Scheme1.Analyte1
    • Scheme1.Analyte1
    • etc...

    When any new variables are used in formula or cell validation rules, they are automatically added to the defined names list.

    Manage the list of defined names

    Logical and error values are usually the result of a formula, although entering these values can be useful for testing formulas. The logical values that can be entered are TRUE and FALSE. The error values that can be entered are #N/A, #VALUE!, #REF!, #NULL!, #DIV/0!, #NUM!, and #NAME?.

    Configuring the Assessment of Results

    During detection limit assessment of results for unknown samples, results are always assessed against the upper detection limit, however, enable the CheckLDL Program Settings—GLOBALSETTINGS for CCLASMGR to assess the result against the lower detection limit as well.

    Products can have product rule scripts created for a scheme. The script is run to perform custom logic upon product assessment against the limits for the scheme.

    Configuring the Ignoring of Quality Control Failures

    During result validation, it may be necessary that a user ignores a QC failure. Enable the AllowIgnore Program Settings - MDE to permit a user to do this.

    Customising the Ignoring of Quality Control Failures

    Set the IgnoreScript Program Settings - MDE to the VBScript file to be run after quality control (QC) failures are ignored.

    Configuring the Validation of Results

    Enable the AllowValidate Program Settings - MDE to permit a user to validate results.

    Customising the Validation of Results

    Set the (ValidateScript Program Settings - MDE to the VBScript file to be run after results are validated.

    Configuring the Saving of Results

    Enable the AutoStoreFormattedValue Program Settings—GLOBALSETTINGS for CCLASMGR to automatically calculate and store the formatted value when final values of analytes are saved.

    Enable the UpdateJobStatus Program Settings - MDE to automatically update the job status after data is saved.

    Customising the Saving of Results

    Set the AutoRunBeforeSave Program Settings - MDE to the VBScript file to be run after a save is requested, but before the sample results are saved.

    Set the AutoRunAfterSave Program Settings - MDE to the VBScript file to be run after data is saved.

    Configuring Spreadsheet Printing

    Enable the PrintPageOrientationLandscape Program Settings - MDE to print reports in landscape orientation, or disable the setting for portrait orientation.

    Set the PrintPageHeader Program Settings - MDE to the report page header.

    Set the PrintPageFooter Program Settings - MDE to the report page footer.

    Set the PrintPageMarginTop Program Settings - MDE to the margin at the top of the page.

    Set the PrintPageMarginBottom Program Settings - MDE to the margin at the bottom of the page.

    Set the PrintPageMarginLeft Program Settings - MDE to the margin on the left-hand side of the page.

    Set the PrintPageMarginRight Program Settings - MDE to the margin on the right-hand side page.

    Set the PrintPageMarginHeader Program Settings - MDE to the margin below the header.

    Set the PrintPageMarginFooter Program Settings - MDE to the margin below the footer.

    Enable the PrintPageOptionsColumnHeadings Program Settings - MDE to print column headings, or disable the setting otherwise.

    Enable the PrintPageOptionsRowHeadings Program Settings - MDE to print row headings, or disable the setting otherwise.

    Enable the PrintPageOptionsGridlines Program Settings - MDE to print grid lines on the page, or disable the setting otherwise.

    Adding Custom Forms to Data Entry

    Data entry often requires custom forms that are tailored to a laboratory's requirements.

    Set the MDERunn Program Settings - MDE to the custom forms to be run from the Command main menu of the Data Entry application.