/
Dynamic Worksheets

Dynamic Worksheets

Owned by Mark Elkomos
May 11, 2022
11 min read


Applies to QESTLab

This article describes how to map and use dynamic worksheets within QESTLab. 

Contents

Overview

Dynamic worksheets are a powerful tool in the QEST Platform that allow system administrators to configure customized test data entry screens and calibration entry screens within the product using Excel.

A Dynamic Worksheet is a special class of External Document within the QEST Platform. Dynamic Worksheets are Excel based templates that can be associated with user documents, reduced tests or equipment items in order to broaden the scope of calculations available for the specific test or calibration.
Dynamic worksheets may include:

  • Worksheets containing calculations that push results to user documents or reduced tests.

  • Worksheets and other documents for which a hard copy is not required.

  • Worksheets for test methods not implemented natively in the QEST Platform.

  • Worksheets for equipment calibrations not implemented in the QEST Platform.

Excel Template

The Excel worksheet that will be used for test calculations should first be created in Excel. Then tested for the correctness of the calculated results and formatting before mapping it as a dynamic worksheet. 

QEST Platform XSD File

The XSD file includes the structure of the user document and any related QEST Platform objects in an XML schema. This file is necessary when mapping QEST Platform fields to Excel files to create dynamic worksheets.

This file can be generated by system administrators from the QEST Administrator Console by navigating to the user document of interest, right clicking on it and selecting Export to XSD from the context menu.

This will bring up a dialog with a multi-select list showing all licensed objects. The user document from which the action was initiated will be selected by default and this will include the structure of the object, along with any parent objects, in the XSD file.

If there is a desire to include other documents (such as other test types) in the XSD file these can be selected at this stage. Selecting multiple documents at this stage allows dynamic worksheets to be created which are able to import data from or export data to multiple tests on the same sample. In most cases, selecting only the user document is sufficient.

Clicking OK will bring up a file save dialog. The XSD file should be saved somewhere on the filesystem where the Excel template mapping will take place which usually is a local machine (work computer) of the system administrator or super user performing the mapping.

Map Excel Template

The XSD file and Excel template are used together with the QESTLab Plugin for Excel to map fields of interest to the Excel template. Mapped fields can either pull data in from the QEST Platform database or push values back (but not both). To map an Excel file as a dynamic worksheet, the QESTLab ribbon in Excel is used after installing the QESTLab Plugin for Excel.

Please contact Spectra QEST for access to the QESTLab Plugin for Excel.

Set XSD File

After opening the Excel file and navigating to the QESTLab ribbon, clicking on the Set XSD File button will open the XSD pane.

Clicking on the button with ellipses in parentheses “(...)” next to the XSD File Location field will show a file open dialog, allowing the user to browse to the previously exported XSD file.

Navigating to the appropriate XSD file, selecting it and clicking Load will load the XSD file structure into the document.

If the document already has an XSD file associated with it, the user may be prompted to replace it with the selected file. Replacing an existing XSD file may cause field mappings to be lost if the new XSD file contains a different set of documents to the originally loaded XSD file.

Add Fields

It is now possible to add fields to the Excel template.

Mapped fields can either pull data in from the QEST Platform database in which case they are called import fields, or push values back in which case they are called export fields. A field cannot be both an import and export field.

Good candidates for import fields from QESTLab to the dynamic worksheet are identifying pieces of information such as client and project names as well as sample or work order IDs.

Good candidates for export fields from the dynamic worksheet to the QESTLab User Document are calculated test results or directly entered reportable results.

Clicking on the Add Fields button in the QESTLab Ribbon will open the QESTLab Fields menu which allows for fields to be mapped to the Dynamic Worksheet.

The first step is to locate the field that needs to be added to the dynamic worksheet. For example, if a client name should be imported into the dynamic worksheet the field must first be located in the QESTLab Fields menu. The client name is set on the work order level so the Work Order (101) row is selected in the bottom section of the QESTLab Fields menu:

The client name is then located in the list of available fields to select in the middle section of the QESTLab Fields menu:

Finally, the attributes of the field in the top part of the pane should be checked. In this example, no further attributes need to be set since this is an import field. Each of the properties will be discussed in more detail in the section below.

The selected fields can then be added to the document in two ways:

  1. Drag and drop the selected field onto the document at the point it should appear.

  2. Double-click the selected field to insert it at the currently selected position / cell.

In Excel, dragging and dropping onto a cell will replace the contents of the cell. If an export field is being added and the existing formula of the cell should be kept, the double-click method should be used instead.

Mapping a field will:

  • Add a Data Only worksheet to the Excel workbook (if one does not already exist).

  • Add a field in the Data Only worksheet representing the mapped QEST Platform field.

  • If the field is an export field, the cell representing the field in the Data Only tab will be populated with a formula referencing the mapped cell in the template worksheet.

  • If the field is an import field, the cell in the template will be populated with a formula referencing the cell representing the field in the Data Only tab.

The Data Only worksheet will be the tab that the QEST Platform interacts with when transferring data to or from the template.

Specifications

It is possible to display a specification that has been applied to a field, rather than the value of the field itself. This can be achieved through the use of the Show Spec Limit property.

Setting this field to True on the field of interest changes what is mapped to the dynamic worksheet. Now when the field is mapped, it will display the specification, rather than the value itself.

Images

Mapped Excel templates may have images imported from QESTLab. These fields are typically found on reports and are Accreditation Image, Laboratory Logo Image and Signature Image.

When these are mapped to a document, a placeholder image will also be inserted into the document. This can be resized as required; the final signature will automatically size to fit within the placeholder image, but will retain its original relative proportions.

Multiple Samples

Dynamic worksheets can be made to work across multiple samples. To do this, drag the field list on the DataOnly worksheet down (using the handle at the bottom right of the list set) to include the number of samples that are expected to appear in the document.

The screenshot below shows some fields entered for a California Bearing Ratio report, with a maximum of 6 samples expected. Data from one sample will be included on each row.

Records

If there are records in the QESTLab data, it is recommended that you place these separately to the sample records. You will also need to drag this down to include as many records as you are expecting to appear in the document.

The screenshot below shows Records fields entered for a California Bearing Ratio report, which has 26 records, one for each possible depth. One record will be entered on each row.

Protecting the Template

The spreadsheet used for the template should be protected such that the users can only edit the fields required such that the format of the report itself is therefore in no danger of being accidentally modified.

Select the cells that you want to make editable, right-click and select Format Cells.... On the Protection tab, uncheck the Locked checkbox.

In addition to protecting the spreadsheet, the Data Only tab should be hidden.

Associating Dynamic Worksheets

Associating a Dynamic Worksheet to a user document, reduced test or equipment item in QESTLab allows the worksheet to be made available for use to users viewing the user document, reduced test or equipment item. Administrators can create new external documents and associate these to document types via the Administration Console. External documents are associated by using the Manage External Documents option in the administrator console.

From this management window existing external documents can be modified or a new document can be added. To associate a dynamic worksheet with a specific test method, user document or equipment item first select the document in the left hand column and then click the Add Document button. This will automatically add a new External Document on the right viewing pane which needs to be mapped to the document selected on the left viewing pane.

Alternatively the user can select the radio button Group By External Document and click multiple documents located in the right viewing pane and link them to the dynamic worksheet located in the left viewing pane.

Once a new document is created the Details tab will be available to enter a document name and upload the external document. "Dynamic Worksheets" must be selected as the Class when adding a dynamic worksheet.  

Once all details have been entered and the appropriate document selected, click Save before closing the window. 

More information on this window is provided in the image below.

Using Dynamic Worksheets

On User Documents and Reduced Tests

At the document level, the dynamic worksheet is accessible by clicking on the Excel button in the header.

Multiple Sample Dynamic Worksheets

The dynamic worksheet may also be available via the work order, if it applies to multiple samples. 

Multiple sample dynamic worksheets are accessible by clicking on the Excel button in the sample grouping on the bulk entry screen.

Opening Dynamic Worksheets 

Clicking on the Excel button  will open the dynamic worksheet in Excel. Citrix users may instead be prompted to save a local copy of the file. Dynamic worksheets, if mapped appropriately, will prepopulate key data into them when they are opened, such as laboratory details, client details, sample IDs etc.

Once the dynamic worksheet is opened the user is free to make changes to the worksheet as required.

Worksheets can be modified during creation to lock specific fields in order to prevent users from making unnecessary changes. The administrator may have disabled editing of certain parts of the file, but the user should be able to enter data where required in the worksheet.

When the Excel worksheet is complete, the workbook should be saved before heading back to QESTLab to re-upload the document.

Re-uploading Dynamic Worksheets

After the edited Excel file is saved the data entered on the worksheet can be re-uploaded into QESTLab by clicking on the Re-upload button. The Excel/Word document itself will also be stored in the QESTLab database and the mapped data extracted from it.

Depending on how the administrator configured the dynamic worksheet, some key data may also now appear on test reports generated from QESTLab.

Dynamic Worksheet Equipment Calibration Documents

Custom calibration worksheets can be defined in Excel and made into dynamic worksheets in the QEST Platform to map calibration values back to appropriate equipment items. The advantage to dynamic worksheet calibration documents is that the calibration values will import directly from the attached worksheet and do not need to be entered manually.

Dynamic worksheets for equipment calibration documents must be mapped to a user document that has the Document Type set to Equipment Calibration and has the Parent Document of the equipment type to be calibrated as shown below.

A dynamic worksheet is mapped to the user document in the same process as described above in the section Mapping Dynamic Worksheets and the Class must be set to Dynamic Worksheets as shown below.  

After the dynamic worksheet has been created and the correct permissions enabled the user will see the new dynamic worksheet calibration available under the Worksheet drop down when calibrating the equipment. 

 See Equipment Calibration Values for more information on calibrations.



Related content

Integrity | Curiosity | Empathy | Unity

The content of this page is confidential and for internal Spectra QEST use only. Do not share, duplicate or distribute without permission.