From WinLIMS
Jump to: navigation, search

Page is restricted to employees of Quality Systems International Only


Introduction

The WinLIMS Bridge is a generic tool that allows data to be transferred from files (e.g. XML, CSV, XLS, TXT, etc.) or databases to a targeted database. The application was originally designed to seamlessly create and update records into WinLIMS to interface with analytical instruments and external systems (e.g. ERP & Process Control systems), but can be used as a general interfacing tool for vitually any SQL Server or Oracle database applications.

This document will focus on the use of the Bridge for creating and updating of samples and analytical results from external sources such as ERP systems, process control systems, analytical instruments, on-line instruments and virtually any other data source.

The WinLIMS Bridge option consists of a set of independent modules that can work independently or in concert to accommodate the wide variety of data sources that are encountered within the modern laboratory. The modules include:

  • INBOX Tables that include triggers and stored procedures that provide the logic to properly build and index records within the WinLIMS database.
  • A Windows application named XMLInbound that transfers XML files into the targeted SQL database tables. Within the WinLIMS application the targeted tables are the INBOX Tables. XMLInbound also has the ability to process and convert Character Separated Value (CSV) files.
  • A Windows service named XMLInbound Windows Service that transfers XML files into the targeted SQL database tables: the INBOX tables.
  • A WinLIMS function named XMLOutbound that allows the generation of XML files from any WinLIMS table for subsequent use by other applications.

The general concept of the WinLIMS Bridge is presented in the following illustration.


WinLIMS-Bridge-Overview.jpg


Much of the processing occurs within the database INBOX Tables and stored procedures. These contain core WinLIMS logic that allows samples and results to be created and updated from external sources into WinLIMS in a controlled manner. Having a single, standard procedure for getting data into WinLIMS should make programmers more efficient by eliminating or greatly reducing duplication of effort. The other modules are simply different means of populating the INBOX tables with data in a controlled manner from the wide variety of external sources that are frequently encountered. Users who possess the ability to directly insert records into the INBOX Tables do not need any of the other supporting modules.

Software Installation

Installation of the INBOX_SAMPLE table is quite simple. It consists of having your database administrator run the following script:


  • SQLServer_Inbox_Utility_v600.sql (for SQL Server databases)
  • Oracle_Inbox_Utility_v600.sql (for Oracle databases)


These files are located on your WinLIMS installation CD. If you've misplaced the CD or need to purchase the WinLIMS Bridge option, contact your local QSI office.

INBOX Tables

The WinLIMS Bridge option includes a series of tables that have triggers and stored procedures associated with them that are mapped to operational tables within WinLIMS. The INBOX table are 'flat' tables which makes them easily understood and easy to transfer data to from files and 3rd party applications. The table's triggers and stored procedure are used to convert the data within each of the 'flat' tables into the various 'relational' tables that are used by the WinLIMS software to appropriately organize the data by applying internal key values for efficient use.

The triggers and stored procedures that are associated with the INBOX data tables are dynamically designed to allow QSI (or you) to add fields to your system without the need to make any adjustments to them.

Other INBOX Tables are used to configure the system to accommodate your specific WinLIMS implementation. The following INBOX Tables are associated with the WinLIMS software:

INBOX_METHOD

INBOX_SAMPLE

INBOX_SAMPLE includes a trigger and associated stored procedures that will act upon records as they are inserted and updated into the table. The purpose is to accept new data that will be associated with samples and tests that originate from external systems such as ERP systems, Process Control systems, instruments or any other data source that might be used to generate new samples or results. The table will be configured to accept SQL statements from a variety of sources. These can be:


INBOX_SAMPLE Components

The INBOX_SAMPLE Table will include all of the standard WinLIMS SAMPLE fields to allow any of the OTS table’s fields to be used to identify samples that originate from outside of the WinLIMS application. You can add fields to the INBOX_SAMPLE table to accommodate any that have been added to your SAMPLE table either by QSI or by your company's internal IT staff.


An action field (named EVENT) is included within the INBOX_SAMPLE table to indicate the task (or event) that will be performed upon inserting or updating the record. The EVENT is essentially an instruction that identifies the appropriate stored procedure to execute. In cases where WinLIMS is customized, the additional fields are easily accommodated by adding them to the script that creates this table and the stored procedures that are associated with this function.


In addition to the standard fields from the SAMPLE table, additional fields will be included to allow the procedure to accept test assignments and results that originate from external systems. This is a traditional need when acquiring data from on-line process control analyzers. Of course the assigned METHODs and PARAMETERs must exist within the WinLIMS database in order to successfully append test-related information. It will be the responsibility of the user of this module to present the appropriate data into the table.


Additional supporting tables are also required in order to ensure the proper configuration and to provide a repository for tracking the status of each transaction. These tables are named:


INBOX_KEYS – used to allow each site to construct a logical means of identifying a secondary set of fields that encompass a unique identifier for SAMPLE records. These can differ for each EVENT type.


INBOX_ERROR_LOG – used to log any errors that occur whenever a record is processed using the INBOX_SAMPLE table and associated procedures.


INBOX_SAMPLE Fields

The fields that are included within the INBOX_SAMPLE table are used to identify samples, test methods that are assigned to samples (if applicable) and parameters/results that are assigned or associated with the sample (if applicable).

Field Name Type Description/Use
INBOX_SAMPLE_ID INT The identity column for records in this table.
EVENT INT This will consist of a numeric code that will dictate the transaction that will be triggered. The codes are described in a subsequent section of this document
TRANSACTION_STATUS Varchar(100) This will consist of value that indicates the success or failure of the transaction..
TRANSACTION_SOURCE Varchar(50) This will be used to record the application or source of the record (e.g. ‘QSI Web Service’, ‘QSI Windows Service’, ‘Company X Procedure’, ‘QSI InterLink’, ‘JD Edwards’, ‘Wonderware’, etc.)
SAMPLE_ID Varchar(20) The SAMPLE_ID will be provided whenever the event is used to update and existing SAMPLE or SAMPLEPARAM record.
SAMPLE_TYPE Varchar(20) When entered, this value must be associated with an approved record within the SAMPLETYPE table. If not, it will return the error message: “Invalid SAMPLE_TYPE”
LOT Varchar(10) Self-explanatory
REG_ON Datetime This value will be set to the system clock’s date and time.
REG_BY Varchar(20) Self-explanatory
TEXT7 Varchar(20) Self-explanatory
TEXT8 Varchar(20) Self-explanatory
TEXT9 Varchar(20) Self-explanatory
TEXT10 Varchar(20) Self-explanatory
DATE1 Datetime Self-explanatory
DATE2 Datetime Self-explanatory
DATE3 Datetime Self-explanatory
DATE4 Datetime Self-explanatory
REMARKS Varchar(254) Self-explanatory
MFG_DATE Datetime Self-explanatory
PROJECT_NO Varchar(20) Self-explanatory
NUMERIC1 Decimal(28,8) Self-explanatory
CUSTOMER_ID Varchar(20) Self-explanatory
METHOD_SUBCLASS Varchar(20) Self-explanatory
ST_TYP Varchar(10) Self-explanatory
HAZARDS Varchar(254) Self-explanatory
HANDLING Varchar(254) Self-explanatory
PROGRAM_CODE Varchar(30) Reserved for future use
METHOD_NAME Varchar(30) The name of the test method that will be assigned to the sample or used as a reference for updating a result that is supplied by the external source.
PARAMETER_NAME Varchar(60) The name of the parameter that will be assigned to the sample or used as a reference for updating a result that is supplied by the external source.
SRESULT Varchar(20) The result that is associated with the METHOD_NAME and associated PARAMETER_NAME value.
ENTERED_ON Datetime Only applicable when the record includes PARAMETER_NAME and SRESULT values. This represents the date and time on which the result was entered. If not provided by the source, the value will be set to the system date and time.
ENTERED_BY Varchar(20) Only applicable when the record includes PARAMETER_NAME and SRESULT values. This represents the source of the result. If not provided by the source, the value will be set to ‘INBOX_SAMPLE’.
INSTRUMENT Varchar(20) Only applicable when the record includes PARAMETER_NAME & SRESULT values. This represents the equipment that was used to generate the data. If not provided, the value will be null.


All you need to do is create a way to insert records into this table and you'll be able to create and/or update samples within WinLIMS from any external source. Examples of how records can be inserted into the INBOX_SAMPLE table are:


INBOX_SAMPLE Events

As previously mentioned, a special field named EVENT is used to identify the appropriate stored procedure to use. This is a very important setting because of the wide variety of ways samples need to be processed by data that is fed to WinLIMS from external sources. The INBOX_SAMPLE EVENT Values represent:


  1. Creation of a new WinLIMS Sample where tests and specifications are assigned from the SAMPLE_TYPE table (can also include results)
  2. Creation of a new WinLIMS Sample where tests and specifications are assigned from the source application (can also include results)
  3. Creation of a new WinLIMS Sample where tests and specifications are assigned from the SAMPLE_TYPE table and augmenting from the source application (can also include results)
  4. Creation of a Sample by duplicating an exsiting WinLIMS Sample and assigning tests and specifications from the SAMPLE_TYPE table without inheriting any results from the Source Sample
  5. Creation of a Sample by duplicating an exsiting WinLIMS Sample and assigning tests and specifications from the SAMPLE_TYPE table and inheriting the results from all non-failing parameters (results) from the Source Sample
  6. Creation of a Sample by duplicating an exsiting WinLIMS Sample and assigning tests and specifications from the SAMPLE_TYPE table and inheriting the results from all non-failing tests (where a test can include one or more parameters/results) from the Source Sample
  7. Updating results for existing samples from instruments

INBOX_SAMPLETYPE

INBOX_ERROR_LOG

INBOX_KEYS

XML Inbound Options

Three different tools for processing XML files are provided within the WinLIMS application. XML is used by QSI because it is a universal format that is well-documented and well understood by IT professionals and has been adopted as a de facto standard within virtually all modern applications where the ability to share information between applications is critical. The Inbound applications effectively provide the same functionality; they simply run within different architectural environments.


XMLInbound Application

The XML Inbound Application can be run on-demand on stand-alone PCs or from application and file servers. The application is used when end users want to start and stop the interface function on demand. The application is also used when implmenting new interfaces because it simplifies the debugging that may need to occur when creating the new setup.


XMLInbound Windows Service

Once an XML configuration has been debugged and is ready for use, it can be run as a Windows service on a local PC or on a file server. The advantage of running the XMLInbound function as a service is that it is always operational as long as the computer is running; hence, you never have to worry about starting or stopping it.


XMLInbound Web Service

Like the XMLInbound Windows Service, the XMLInbound Web service is always operational as long as the computer is running. The added benefit of using the Web Service is that 3rd party and user-written applications can provide the information to the Web service from any web page that is provided access to the web server.


XSL Transformation

XSL Transformation is a standard feature of windows that transforms XML files from the format of the "supplying" application to that of the 'consuming' application.

WinLIMS XML Import Formats

TODO: ...

Resources: XML and XSL

An enormous amount of information is available to introduce and educate one on XML and the XSL transformation process. Here are some sites that you can use to get yourself started.

World Wide Web Consortium (W3C) website: http://www.w3.org/

W3C XML website: http://www.w3.org/XML/Core/

W3C XSL website: http://www.w3.org/TR/xslt

XML Tutorials: http://www.google.com/search?q=XML+online+tutorials http://www.w3schools.com/xml/xml_syntax.asp

XSL Tutorials: http://www.google.com/search?q=XSL+online+tutorials http://www.w3schools.com/xsl/default.asp

XMLOutbound

The XML Outbound Option provides a means for flexibly selecting data for output from WinLIMS to 3rd party software that can process XML files. It is possible for the WinLIMS XMLInbound software (or service) to process the XML files generated by XMLOutbound in order to update databases that do not provide such a tool in order to provide a generic ‘closed loop’ system for interaction between your corporate databases.


InterLINK

InterLINK is a tool that is used to parse files that are produced by instruments into standard XML or CSV format for subsequent processing by the XMLInbound applications and services.