Content

Overview Datasources Overview Worksheet Types Workitem DataSet QCBatch SDI Status Management Options Field Binding Overview Binding Mode Binding Keys About Binding and Locking Bind Error Basic Mechanics of Field Binding   Creating a Worksheet Form Creating Worksheets Workitem DataSet QCBatch SDI Example of Basic Worksheet Form Worksheet Assignment and Analyst Certification Worksheet Assignment Analyst Certification

 

Overview	Top

A "Worksheet Form" is a special type of Form... "special" in the sense that a Worksheet Form can be bound to database records. In general, a "Worksheet" refers to a Document that is based on a "Worksheet Form". Worksheet Forms provide:

•	Automatic Datasource creation to retrieve and load database records for selected result sets.
•	Binding of Form Fields to database records through the Datasource, providing: • Saving to SDI Primary, SDIData, SDIDataItem, and SDIDataRelation records. • Standard Data Entry in SDIDataItem. • Display of SDI Primary, SDIData, SDIDataItem, SDIDataRelation, SpecParamLimits, QCBatch, QCBatchItems, and QCSampleType records.
•	"Live feedback" of entered data that has been checked against Parameter Limits, Specification Limits, Transformations, and Calculations (such as returning red text to identify a failed condition).
•	Automatic processing and saving of records without having to write special Form processing rules.
•	Creation options: • Automatic creation of Worksheets on system events involving: • Data Sets (on demand, when a Data Set is created, or when a Data Set is assigned to an Analyst). • Tests (on demand or when a Test is created) • QC Batches (on demand, when a QC Batch is created, or when a QC Batch is assigned to an Analyst). • Manual creation of Worksheets when managing Data Sets, Tests, and QC Batches. • Programmatic creation of Worksheets using the CreateWorksheet Action.
•	Ability to create and build Worksheets using the same tools as non-Worksheet Forms (Form Definitions, Form Builder, and eForm Manager).
•	Integration with the Simple Instruments, AQC, and Consumables modules.

Automatic Datasource creation, automatic processing and saving, and the automatic and manual creation options are available out-of-the-box (referred to as "Context-Driven" Worksheets). Context-Driven Worksheets provide a high level of automation in the Worksheet creation and saving process. All Form processing rules for Context-Driven Worksheets are automatic. No user-defined processing is allowed.

You can also create advanced and customized Worksheets manually with custom processing rules. These are referred to as "Adhoc" Worksheets. When you are ready for some advanced work, see Adhoc Worksheets in eForm Worksheets Advanced Concepts. Unless otherwise noted, all information presented here pertains to Context-Driven Worksheets.

Two fundamental concepts are key to understanding how Worksheets bind to database records:

•	Datasources
•	Field Binding

 

Datasources	Top

Overview

A Datasource is a Form object that retrieves and loads one or more sets of data from the database, effectively serving as a holding area for records. Form Fields use the Datasource to bind to the actual database records.

A Datasource is used only when creating a Worksheet Form. Datasources are not persisted and cannot be edited or deleted. The only exception to this is a SQL Datasource, which can also be used in non-Worksheet Forms.

Datasources can load data for these result sets (as shown below):

•	primary (SDI Primary data such as s_Sample)		•	dataspec (SDIDataItemSpec)
•	dataset (SDIData)		•	datarelation (SDIDataRelation, e.g., Consumable data)
•	dataitem (SDIDataItem)

When you create a Context-Driven Worksheet, Datasources are automatically created and loaded using the source object's keys, which are resolved and saved with the Document (the saved Document State is "Pending").

Worksheet Types

Context-driven Worksheets have an internal Datasource that loads whatever data is required for the source, as specified by the "Worksheet Type" (below). For example, if the Worksheet Type is "Workitem", the Datasource loads all records required to enter and display data for Workitems (Test Methods).

Workitem

"Workitem" types retrieve Data Sets for specific Workitems that are associated with one or more SDIs.

These Worksheets are linked to Test Methods and can be automatically created on Test Method events (see Creating Worksheets).

The Datasource properties specify the Workitem keys that are used to load the Datasource:

Key	Load Script for the Datasource
SDC Id	$G{params.sdcid}
KeyId 1, 2, 3	$G{params.keyid1}, $G{params.keyid2}, $G{params.keyid3}
Workitem Id	$G{params.workitemid}
Workitem Instance	$G{params.workiteminstance}

The Document Id is saved in SDIWorkitem.

DataSet

"DataSet" types retrieve specific Data Sets that are associated with one or more SDIs. These are more restrictive than Workitem types because the Datasource retrieves fewer Data Set and Data Item records. However, less Field binding is required. Multiple Data Sets (Items / Worksheet) can be shown on the Worksheet.

These Worksheets are linked to Parameter Lists and can be automatically created on Data Set events (see Creating Worksheets).

The Datasource loads data only for the Data Sets specified by the keys (Datasource properties):

Key	Load Script for the Datasource
SDC Id	$G{params.sdcid}
KeyId 1, 2, 3	$G{params.keyid1}, $G{params.keyid2}, $G{params.keyid3}
Parameter List Id	$G{params.paramlistid}
Parameter List Version	$G{params.paramlistversionid}
Variant Id	$G{params.variantid}
Dataset	$G{params.dataset}

QCBatch

"QCBatch" types retrieve Data Sets that are associated with a specific (single) QC Batch.

These Worksheets are linked to QC Methods and can be created on QC Batch events (see Creating Worksheets). As such, they contain three Datasources.

The Datasource properties specify the keys that are used to load each:

QCBatchDatasource

Key	Value/Script for Loading the Datasource
SDC Id	QCBatch
KeyId 1	$G{params.qcbatchid}

QCBatchItemsDatasource

Key	Value/Script for Loading the Datasource
QCBatchId	QCBatch
KeyId 1	$G{params.qcbatchid}

An additional readonly "dataset" result set is available only with the QCBatchItems Datasource. This is used for repeating Sections over the individual QC Batch Items.

QCBatchSampleTypeDatasource

Key	Value for Loading the Datasource
SDC Id	QCBatchSampleType
Query Clause	Query Params
Query From	s_qcbatchsampletype
Query Where	$G{"qcbatchid = '${params.qcbatchid}'"}

For more information about this and the Example Data provided with LabVantage, see QCBatches.

SDI

"SDI" types retrieve data for one or more SDIs.

These Worksheets are not linked to anything and cannot be automatically created on system events. They must be created using the CreateWorksheet Action.

The Datasource properties specify SDI keys that are used to load the Datasource:

Key	Load Script for the Datasource
SDC Id	$G{params.sdcid}
KeyId 1, 2, 3	$G{params.keyid1}, $G{params.keyid2}, $G{params.keyid3}

For more information about this and the Example Data provided with LabVantage, see SDI.

Status Management Options

The "Status Management" options are properties that let you specify how Field data is automatically saved for a given Datasource:

Option	Description
Auto Release Data	(Yes/No) Determines if AutoSave DataEntry data entry data should be auto released when saved (see Binding Mode).
Sync Primary Status	(Yes/No) Determines if the primary status value should be updated when data entry occurs. Specify the column in "Primary Status Column" (below).
Primary Status Column	Determines the primary status column for "Sync Primary Status" (above).
Sync AQC Status	(Yes/No) Determines if the AQC status value will be updated when data entry occurs.

 

Field Binding	Top

Overview

"Field Binding" is the process of linking a Form Field to a specific row and column in one of the result sets that is loaded into the Datasource.

Bound Fields are associated with "Datasource Binding" properties, which are essentially the inputs to the Datasource. The Datasource uses these properties to match the Field to specific records.

Datasource Binding properties fall into two functional groups: Binding Modes and Binding Keys.

Binding Mode

Binding Mode serves two purposes:

•	Determines whether the value entered into the Field is saved in a database column (AutoSave) or the Field displays the value of the column (Display).
•	Defines the result set loaded into the Datasource result set (primary, dataset, dataitem, dataspec, or datarelation).

There are two types of Binding Modes:

Type of Binding Mode	Description
AutoSave	Saves the entered value in a database column for these records: • SDIDataItem records (Parameter level values such as you would enter during Data Entry. • SDIData records (Data Set level such as Instrument details). • SDI primary data (such as Sample description). • Consumable Lot records (such as Lot Id and Container information). AutoSave fields are saved when the processing rules are executed, which is typically after a "Submit" if Document Approvals are not required.
Display	Displays the value of the column.

Five AutoSave modes and six Display modes determine the kind of data that is bound to the Field and define what happens in the Worksheet:

Binding Mode	Field Binds to...	Behavior	Description
AutoSave Primary	SDI Primary column	Saves the entered value to the column.	Example: s_Sample record.
AutoSave DataSet	Data Set column	Saves the entered value to the column.	SDIData record.
AutoSave DataItem	Data Item column	Saves the entered value to the column.	SDIDataItem record.
AutoSave DataEntry	Data Item	Saves the entered value as in standard Data Entry.	Allows standard Data Entry.
AutoSave Reagent	SDIDataRelation column	Saves the entered value to the column.	For Consumable Lot records.
Display Primary Column	SDI Primary column	Displays the column value.	Example: s_Sample record.
Display DataSet Column	Data Set column	Displays the column value.	SDIData record.
Display DataItem Column	Data Item column	Displays the column value.	SDIDataItem record.
Display Reagent Column	SDIDataRelation column	Displays the column value.	For Consumable Lot records.
Display Spec Value	Specification columns	Displays a Specification Limit.	Values in SpecParamLimits.
Display QCBatchItem Value	QCBatchItem columns	Displays a QCBatchItem column.	QCBatch, QCBatchItems, QCSampleType records.

 

Binding Keys

Binding Keys specify the SDI Keys and column values that are matched to the Datasource result set, and therefore will be bound to the Field. You need to specify only the keys you need to define the data. For example, when binding to a Data Set, you can specify only the ParamListId if that is all you require. The set of avaliable Binding Keys depend on the selected Binding Mode:

Binding Mode	Binding Keys	Description
AutoSave Primary Display Primary Column	Bind SDC Id	SDC.
	Column Id	Shows columns in the SDC specified by Bind SDC Id. Choose from this list.
AutoSave DataSet Display DataSet Column	Parameter List Id, Parameter List Version Id, Variant Id, Dataset	Data Set columns.
	Column Id	Shows columns in SDIData. Choose from this list.
AutoSave DataItem Display DataItem	Parameter List Id, Parameter List Version Id, Variant Id, Dataset, Parameter Id, Parameter Type, Replicate Id	Data Item columns.
	Column Id	Shows columns in SDIDataItem. Choose from this list.
AutoSave DataEntry	Parameter List Id, Parameter List Version Id, Variant Id, Dataset, Parameter Id, Parameter Type, Replicate Id	Data Item columns. The value is saved in SDIDataItem as in standard Data Entry.
AutoSave Reagent Display Reagent Column	Reagent Type Id, Reagent Component (choose from ReagentType, Lot, Container, Quantity, DefaultQuantity, Units, DefaultUnits)	Reagent columns in SDIDataRelation.
Display Spec Value	Parameter List Id, Parameter List Version Id, Variant Id, Dataset, Parameter Id, Parameter Type, Replicate Id, Specification Id, Specification Version Id, Display Expression (choose a defined Spec Condition from this list). Also see Customizing the Display Expression for Specifications.	Specification Limit defined in SpecParamLimits for a specific Data Item and Spec Condition.

About Binding and Locking

When a Worksheet is bound to SDIData records, the Document Id is saved and the affected Data Sets are blocked to prevent Data Entry (or any further binding) from being conducted on the same data. For example, suppose you create a Worksheet (Doument Id = "MyDoc"). "MyDoc" has Fields that bind to "ParameterList-A" on "Sample-001". The DocumentId ("MyDoc") is saved in "ParameterList-A", and all SDI Data Sets in "ParameterList-A" for "Sample-001" are blocked.

Bind Error

"Bind Error" what to do when a Field cannot be bound (such as when a record cannot found for the Datasource Binding properties):

Option	Behavior
Show Error	Generates a Field validation error.
Hide Field	Hides the Field on the Form.
Disable Field	Disables the Field on the Form (default).

Basic Mechanics of Field Binding

To illustrate the basics of the "Binding Mode" concept, consider the following two cases. Data are entered into a "Moisture Analysis" Worksheet. The "Worksheet Type" is "Workitem", so the Datasource loads records associated with a Test Method (in this case, the "Moisture Anaysis" Test Method).

Case 1: AutoSave DataEntry

In the first case, "Binding Mode" is "AutoSave DataEntry", so the value entered into "Wet Weight" will be saved to the database as it would be during standard Data Entry (SDIDataItem). Only one Binding Key (ParamId) is specified because that is all that is required to match to the Datasource result set.

Case 2: AutoSave Primary

In the second case, "Binding Mode" is "AutoSave Primary", so the value entered into "SampleDesc" will be saved to the database as SDI primary data.

 

 

Creating a Worksheet Form	Top

Begin creating a Worksheet Form as you would any other Form (using the Form Definition). In the Form Definition, enable Worksheet functionality by choosing these options:

Option	Description
Form Type	Set to "Worksheet". This enables the "Worksheet Type", "Items/Worksheet", and "Versionable" fields.
Worksheet Type	Set according to needs (as described in Worksheet Type). This creates a protected Datasource in the Form Builder.
Items/Worksheet	Maximum number of Workitems, Data Sets, or SDIs that can be loaded into the Datasource when the Worksheet is created (Multiple Items per Worksheet and Binding Fields to Multiple Data Records. This is disabled for QCBatch types since they use three Datasources (see Worksheet Types).
Versionable	Enables Versioning for Documents based on this Worksheet Form (only Documents based on Worksheet Forms can be Versioned). Versioning is available only for Worksheets in a "Pending", "Draft" or "PendingApproval" state. This exposes a "New Version" button in the eForm Manager that cancels the current Worksheet and regenerates a new Worksheet with the next Version number. The status of the cancelled Document Version transitions to "Expired". The status of the new Document Version is "Provisional". The Vesioning operation requires an ESig, but can be changed in page properties. The new Worksheet Version re-binds to the most current data in the database. It is therefore useful when new data must be bound into a Worksheet. For example, in a typical "Retest" operation, Data Sets and Data Items are modified, thus calling for a new Worksheet version. Worksheets with a Version history enable a "History" button in the eForm Manager Document Toolbar. You cannot edit Historical (expired) Worksheets.

After saving the Worksheet Form, the Worksheet Document can be opened in the eForm Manager. New Worksheet Forms cannot be opened in the eForm Manager.

 

Creating Worksheets	Top

After creating a Worksheet Form, you can create a Context-Driven Worksheet. These Worksheets can be created automatically ("On Creation" and "On Assignment") or manually ("On Demand"):

NOTE:

"On Creation" Worksheets are created asynchronously using the ToDo List. This allows the data definition (such as a Data Set) to be created after the data details (such as Specifications).

Workitem

If the Worksheet Type is "Workitem":

1.

Setup the Test Method: a. In the Test Method Maintenance page, set the Create Worksheet Rule to "On Demand", "On Creation", or "On Assignment". b. Select the Worksheet Form in the "Form" detail tab, then select the "Default" Worksheet Form (this is the one that will be created). Note that you can select only one "Default". Save the changes.

2.

How the Worksheet is created depends on the creation option you selected: • On Demand: a. Add a Sample, then add the Test (instance of the Test Method) to the Sample. b. In the SDI Workitem List page, select the Sample, then click one of these buttons: • "Create Worksheet" (creates the Worksheet). • "Create Worksheet and Assign User" (lets you assign the Worksheet to a User before creating it.) • "Create Worksheet and Assign Department" (lets you assign the Worksheet to a Department). Open the Worksheet in the eForm Manager. • On Creation: a. Add a Sample, then add the Test (instance of the Test Method) to the Sample. The Worksheet is automatically created. b. Click the Worksheet icon in the "Tests" detail of the Sample Maintenance page, or open the Worksheet in the eForm Manager. • On Assignment: a. Add a Sample, then add the Test (instance of the Test Method) to the Sample. b. In the SDI Workitem List page, select the Sample, then click one of these buttons: • "Assign Analyst" (creates the Worksheet when you assign an Analyst to a Test Method). • "Assign Department" (creates the Worksheet when you assign a Department to a Test Method). Open the Worksheet in the eForm Manager. Note that Workitem Worksheets can also be created programmatically using the CreateWorksheet Action.

DataSet

If the Worksheet Type is "Data Set":

1.

Setup the Parameter List: a. In the Parameter List Maintenance page, set the Create Worksheet Rule to "On Demand", "On Creation", or "On Assignment". b. Select the Worksheet Form in the "Form" detail tab, then select the "Default" Worksheet Form (this is the one that will be created). Note that you can select only one "Default". Save the changes.

2.

How the Worksheet is created depends on the creation option you selected: • On Demand: a. Add a Sample, then add the Data Set (instance of the Parameter List) to the Sample. b. In the Data Set List page, select the Sample, then click one of these buttons: • "Create Worksheet" (creates the Worksheet). • "Create Worksheet and Assign User" (lets you assign the Worksheet to a User before creating it.) • "Create Worksheet and Assign Department" (lets you assign the Worksheet to a Department). Open the Worksheet in the eForm Manager. • On Creation: a. Add a Sample, then add the Data Set (instance of the Parameter List) to the Sample. The Worksheet is automatically created. b. Click the Worksheet icon in the "Data Sets" detail of the Sample Maintenance page, or open the Worksheet in the eForm Manager. • On Assignment: a. Add a Sample, then add the Data Set (instance of the Parameter List) to the Sample. b. In the Data Set List page, select the Sample, then click one of these buttons: • "Assign Analyst" (creates the Worksheet when you assign an Analyst to a Data Set). • "Assign Department" (creates the Worksheet when you assign a Department to a Data Set). Open the Worksheet in the eForm Manager. Note that Data Set Worksheets can also be created programmatically using the CreateWorksheet Action.

QCBatch

If the Worksheet Type is "QCBatch":

1.

Setup the QC Method: a. In the QC Method Maintenance page, set the "Create Worksheet Rule" to "On Demand", "On Creation", or "On Assignment". b. Select the Worksheet Form in the "Form" detail tab, then select the "Default" Worksheet Form (this is the one that will be created). Note that you can select only one "Default". Save the changes.

2.

How the Worksheet is created depends on the creation option you selected: • On Demand: a. Add a QC Batch that is based on the QC Method. b. In the QC Batch List page, select the Sample, then click one of these buttons: • "Create Worksheet" (creates the Worksheet). • "Create Worksheet and Assign User" (lets you assign the Worksheet to a User before creating it.) • "Create Worksheet and Assign Department" (lets you assign the Worksheet to a Department). Open the Worksheet in the eForm Manager. • On Creation: a. Add a QC Batch that is based on the QC Method. The Worksheet is automatically created. b. Open the Worksheet in the eForm Manager. • On Assignment: a. Add a QC Batch that is based on the QC Method. b. In the QC Batch List page, select the Sample, then click one of these buttons: • "Assign Analyst" (creates the Worksheet when you assign an Analyst to a QC Batch). • "Assign Department" (creates the Worksheet when you assign a Department to a QC Batch). Open the Worksheet in the eForm Manager. Note that QCBatch Worksheets can also be created programmatically using the CreateWorksheet Action.

SDI

If the Worksheet Type is "SDI", you must create the Worksheet programmatically using the CreateWorksheet Action. For example, if you have built a Worksheet Form that allows data entry on a Parameter List column, you can:

1.	Add a "Create Worksheet" button to the Sample List page, with the button configured to call the CreateWorksheet Action for the desired Worksheet.
2.	Add a Sample, then add a Data Set to the Sample.
3.	Click the "Create Worksheet" button. You can then open the Worksheet in the eForm Manager.

Adhoc Worksheets

Leaving the "Worksheet Type" blank lets you create "Adhoc" Worksheets in the eForm Manager. These are more complex to create than context-driven Worksheets, but they support user-defined processing rules to let you design creative Worksheet workflows. See Adhoc Worksheets in eForm Worksheet Advanced Concepts.

 

Example of Basic Worksheet Form	Top

This example shows how to create a basic Worksheet Form that:

•	Displays Sample Id.
•	Allows data entry on a Test Method.
•	Displays calculated results with text formatting defined by the Specification Limit.
•	Displays the Specification Limits being checked.

Use this Example Data that is provided with LabVantage:

•	"Moisture Analysis" Test Method.
•	"Moisture Analysis" Specification: Add this to the "Moisture Analysis" Test Method.

Create a Form Definition. Set these Worksheet-specific options in the Form Definition as described in Creating a Worksheet Form:

•	Add a new Worksheet Form: Form Type = Worksheet Worksheet Type = Workitem Items/Worksheet = 1

In the Form Builder, create text fields in the Worksheet Form as shown:

Set these Datasource Binding properties for each Field (as described in Field Binding):

Field	Datasource Binding Properties
Sample Id	Binding Mode = Display Primary Column Bind SDC Id = Sample Column Id = s_sampleid
Dry Weight	Binding Mode = AutoSave DataEntry Parameter Id = DryWt
Wet Weight	Binding Mode = AutoSave DataEntry Parameter Id = WetWt
%Moisture	Binding Mode = AutoSave DataEntry Parameter Id = %Moisture
Specification	Binding Mode = Display Spec Value Parameter Id = %Moisture Display Expression = InSpec

Create the Worksheet using one of the methods described in Creating Worksheets: Workitem. Note that you must set the "Create Worksheet Rule" for the "Moisture Analysis" Test Method, and you must add the "Moisture Analysis" Specification to the Test Method.

 

Worksheet Assignment and Analyst Certification	Top

Worksheet Assignment

Only a Document Manager (DM) can assign Worksheets to an Analyst. Specify this in the "Document Manager Role" of the Form Definition.

Worksheet assignment can be performed on a single open Document or as a group operation on a Document list.

Worksheets can be assigned to another certified User (default) or any other User in the system.

By default, Worksheet assignment requires an ESig, but this can be disabled in the properties for the Maintenance List page.

Analyst Certification

To require User Certification for a Worksheet, check "User Training Required" in the Form Definition. When checked, Users can open the Worksheet Form only if they have a certification/training record for the Form. This can be done in Lab Admin → Certifications & Resources → By Form: "Maintain Trained Analyst" button.

To allow User training overrides, check "User Training Overrides" in the Form Definition. When checked, non-certified Users can open the Form even if "User Training Required" is checked. A dialog opens that informs you of the User training requirement, then lets you override it. The override event is recorded in the Document table (TrainingOverrideFlag column).