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 MDE program setting to configure user access to the Data Entry application.

Enable the ShowTipOfTheDay MDE program setting 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 GLOBAL setting, then set the TipofTheDayFile MDE program setting to the folder and file name, for example %CCLASRootDirectory%TIPS\MDE.TIP.

Enable the MainListViewGrid MDE program setting 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:

• Database view—Set the BrowserView MDE program setting to the name of the database view and disable the ListViewByClient MDE program setting.
• Database table(s)—Leave the BrowserView MDE program setting undefined.
  • Using the PROFJOB table directly—Disable the ListViewByClient MDE program setting.
  • Using the CLIENT, CLIENT_PROJECT and PROFJOB tables in a drill-down—Enable the ListViewByClient MDE program setting.

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

• To display a query form on the main screen:

  • Disable the LoadBrowser MDE program setting.

  • Create the JobUserQuery.SCR script file and place it in the folder defined by the ConfigDirectory GLOBAL setting.

  • Code an event subroutine in the script file, then set the QueryEvent MDE program setting to the name of the event subroutine.

• To filter workbook jobs based on field criteria:

  • Enable the LoadBrowser MDE program setting.

  • Set the CriteriaPROFCLIENTn MDE program settings to specify the column filtering properties using the CLIENT table, and set the CurrentCriteriaPROFCLIENT MDE program setting to hold the current criteria.

  • Set the CriteriaPROFCLIENTPROJECTnMDE program settings to specify the column filtering properties using the CLIENT_PROJECT table, and set the CurrentCriteriaPROFCLIENTPROJECT MDE program setting to hold the current criteria.

  • Set the CriteriaPROFJOBnMDE program settings to specify the column filtering properties using the database view or PROFJOB table, and set the CurrentCriteriaPROFJOB MDE program setting to default the current criteria.

  • To enable the Properties main menu option to allow users to update the criteria, enable the AllowProperties MDE program setting.

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 MDE program settings 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 MDE program settings to specify which columns from the CLIENT_PROJECT table are presented in the grid.
• Jobs node—Set the Sub2ListViewColumnn MDE program settings 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 MDE program setting 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 MDE program setting 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 Sub2ListViewColumnnMDE program setting 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:

• Enable the BCScan MDE program setting.
• Set the BCTerminatorCode MDE program setting to the ASCII character of the barcode input terminator,

Create the barcode VBScript file in the folder defined by the ConfigDirectory GLOBAL setting, and set the BCScriptFile MDE program setting 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:

• Set the MDERunn MDE program settings to specify the custom forms.

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 MDE program setting 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 MDE program setting is enabled, then all schemes are expanded by default, to show each analyte.

Where the UseShortname MDE program setting 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 MDE program setting defines the default result mode for a data entry session.

For Raw results mode, enable the RawFinalModeChecked MDE program setting 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 MDE program setting 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:

• Create the MDESampleUserQuery.SCR script file exists in the folder defined by the ConfigDirectory GLOBAL setting. Code an event subroutine in the script file, then set the QueryEvent
• The MainSampleFilterColumnn MDE program setting define the columns of the sample grid.

Where the MDESampleUserQuery.SCR script file is located in the folder defined by the ConfigDirectory GLOBAL setting 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 MDE program settings to specify the columns of sample details displayed for a workbook presented in the default style.

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

Where the AllowTrailers MDE program setting 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 MDE program setting 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:

• Create the MDESampleUserQuery.SCR script file exists in the folder defined by the ConfigDirectory GLOBAL setting. Code an event subroutine in the script file, then set the QueryEvent
• The MainSampleFilterColumnn MDE program setting define the columns of the sample grid.

Where the MDESampleUserQuery.SCR script file is located in the folder defined by the ConfigDirectory GLOBAL setting 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 MDE program setting is located in the folder defined by the ConfigDirectory GLOBAL setting, 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 MDE program setting. Note that non-level 3 users cannot overwrite locked cells.	Defined by the LockedBackgroundColour MDE program setting.
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 GLOBAL setting is enabled.	Defined by the DetLimitFailureColour MDE program setting.
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 DE program setting.
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 MDE program setting.
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 MDE program setting
Quality control ignored	A sample/scheme/analyte quality control status can be ignored.	Defined by the IgnoredBackgroundColour MDE program setting.
Formula	Cell contains a formula used to calculate the displayed result.	Defined by the FormulaBackgroundColour MDE program setting.
String or OLE data type	Cell has a string or OLE (object linked or embedded) data type.	Defined by the StrOleTypeBackgroundColour MDE program setting.
Date data type	Cell has a date data type.	Defined by the DatTypeBackgroundColour MDE program setting.
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 MDE program settings.

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 MDE program setting 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 MDE program setting to permit level 3 users to unlock locked cells.

Enable the UnlockOnlyLocked MDE program setting to unlock only locked cells upon cell unlocking.

Enable the ClearAllStatusOnUnlock MDE program setting 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 MDE program setting.
• Set the BCTerminatorCode MDE program setting to the ASCII character of the barcode input terminator.
• Create the barcode VBScript file in the folder defined by the ConfigDirectory GLOBAL setting, and set the BCScriptFile MDE program setting 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 MDE program setting 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 MDE program setting 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 MDE program setting 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 MDE program setting 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 MDE program setting 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 MDE program setting 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:

• Enable the ResolveExternalFormulas MDE program setting to allow cross-scheme calculations to take place.
• Enable the SupportOldFormulaNames MDE program setting to support old-style formulas.

To configure the screen file used to enter user notes, set the UserNotesEntry MDE program setting 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 MDE program settings 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 GLOBAL setting 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 MDE program setting to permit a user to do this.

Customising the Ignoring of Quality Control Failures

Set the IgnoreScript MDE program setting to the VBScript file to be run after quality control (QC) failures are ignored.

Configuring the Validation of Results

Enable the AllowValidate MDE program setting to permit a user to validate results.

Customising the Validation of Results

Set the (ValidateScript MDE program setting to the VBScript file to be run after results are validated.

Configuring the Saving of Results

Enable the AutoStoreFormattedValue GLOBAL setting to automatically calculate and store the formatted value when final values of analytes are saved.

Enable the UpdateJobStatus MDE program setting to automatically update the job status after data is saved.

Customising the Saving of Results

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

Set the AutoRunAfterSave MDE program setting to the VBScript file to be run after data is saved.

Configuring Spreadsheet Printing

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

Set the PrintPageHeader MDE program setting to the report page header.

Set the PrintPageFooter MDE program setting to the report page footer.

Set the PrintPageMarginTop MDE program setting to the margin at the top of the page.

Set the PrintPageMarginBottom MDE program setting to the margin at the bottom of the page.

Set the PrintPageMarginLeft MDE program setting to the margin on the left-hand side of the page.

Set the PrintPageMarginRight MDE program setting to the margin on the right-hand side page.

Set the PrintPageMarginHeader MDE program setting to the margin below the header.

Set the PrintPageMarginFooter MDE program setting to the margin below the footer.

Enable the PrintPageOptionsColumnHeadings MDE program setting to print column headings, or disable the setting otherwise.

Enable the PrintPageOptionsRowHeadings MDE program setting to print row headings, or disable the setting otherwise.

Enable the PrintPageOptionsGridlines MDE program setting 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 MDE program settings to the custom forms to be run from the Command main menu of the Data Entry application.