Chemical Inventory Management Solutions from CambridgeSoft

1. Introduction

Managing a chemical inventory consists of tracking the usage, disposal, and ordering of substances that are at the heart of an enterprise’s endeavors. It also includes creating, editing, and organizing data on these substances, the “containers” that represent the actual, physical containers, and the locations where they are stored. To be truly useful, this information should be stored in a shared database on a networked server, accessible to users at remote desktops.

CS Inventory is an application designed to manage the chemical and reagent tracking needs of laboratories and research centers. The system manages data associated with both commercially procured and internally produced chemical substances, from their procurement or initial production, through their depletion and disposal. To meet the needs of institutions of all sizes, Inventory comes in two Desktop editions, a Workgroup edition, and an Enterprise edition.

Inventory Pro is an all-inclusive desktop product. It includes Microsoft Desktop Engine^® (MSDE), the redistributable database engine for SQLServer 2000. No additional licensing is required. It is suitable for single-user installations, as well as small to medium sized groups.

Inventory Ultra is the same MSDE-based product, but also includes the DVD version of ChemACX, CambridgeSoft's catalog of chemical products and suppliers.

Inventory Workgroup is a thick-client SQLServer- based product. Suitable for larger organizations that don't have the ability (or don't want) to maintain an Oracle server, it requires, but does not include, a SQLServer license.

Inventory Enterprise is an Oracle-based, ChemOffice Enterprise product. Designed for large organizations, it has a number of features not included in the desktop/workgroup versions. These additional features include:

• Plate Management
• Global Searching over Inventory, Registration Enterprise, and ChemACX
• Oracle Role Based Security
• 21CFR11 Audit Trail

All versions of the Inventory applications use identical schemas, which allow for seamless migrations between versions.

2. Application Design

The three primary entities in an Inventory system are Locations, Containers, and Substances.

• A Location is defined as any physical location where a container, plate, or another location can be stored.
• An inventory Container represents a physical container capable of storing chemical substances.
• An inventory Substance represents a chemical entity which can be expressed as a two dimensional chemical structure drawing.

Flexible Hierarchical Location Structure

Users or administrators configure a network of locations, which represent locations in an organization. Each location, regardless of type, is arranged in a tree hierarchy. For example, a lab contains a refrigerator, which contains a rack.

Container/Substance Management

Containers are created to represent actual containers in your facility. Each container is assigned a unique barcode, which can be printed, using a customized template, from the Inventory interface. Each container stores a Substance. Additional text fields are available to track other chemical contents such as the solvent, and custom fields may also be defined. Typical containers include: bottle, vial, tube, cylinder, box, etc.

To keep track of Substances, the system maintains its own internal chemical structure database containing unique substances that can be associated with inventory Containers. Advanced duplicate checking is incorporated in the system. Aside from structure and name, substances can be identified via unique registry numbers such as CAS or ACX ID. Custom fields may also be defined.

FIGURE 1. Web-based Inventory Enterprise and Inventory Desktop 

FIGURE 1. Web-based Inventory Enterprise and Inventory Desktop 

3. Main Features

a) Application Settings

Custom Fields 

Users with administrative privileges can add custom fields to compound and container records. Compounds can have up to five custom fields. Containers can have up to ten custom fields and five custom dates. Custom fields and dates can be designated as required fields. Custom compound fields can be marked for duplicate checking.

Structure Searching 

Structure searching can be by substructure, full structure (exact), or similarity. Each user can set preferences to modify the search. For example, similarity can be set from 20-100%; stereochemistry can be set to match tetrahedral or double bond stereochemistry, or both. Solvents (known as “Extraneous Fragments” in search parlance) can be included or excluded.

Oracle Role Based Security (Inventory Enterprise) 

Inventory Enterprise gives complete control over user permissions for different tasks by assigning each user to a user class, and defining permissions for each class. Both User lists and Role definitions are customizable by the administrator.

b) Location Management

The location hierarchy is represented as nested folders on a tree control analogous to that found in the Windows file explorer. Locations may be created, moved, or deleted by the user by filling out a form.

Inventory comes with five special locations – Root, Disposed, Missing, On Order, and Trash Can – which may be edited, but not deleted. A Report engine can produce a Location Report listing all containers at a location, either with or without chemical structures.

c) Container/Substance Management

An Inventory Container represents a physical container capable of storing chemical substances. Containers are created by filling in a form. Required fields identify the Container name, type, size, unit of measure, amount, etc. Additional text fields are available to track other information, such as purchase date. Custom fields may be added, including custom dates, and these may be specified as required or optional. Each Container may be associated with no more than one Substance. (You can use Inventory to track laboratory supplies, as well as chemicals, by not assigning any substance to a container.) Containers may be moved between locations as necessary, edited to record usage, and discarded when they are no longer needed.

FIGURE 2. New Container Form 

FIGURE 2. New Container Form 

Some of the fields — for example container type, unit of measure, or supplier — are entered from tables by using drop-down menus for rapid entry. These tables are customizable, though entries cannot be deleted if they are in use.

An Inventory Substance represents a single pure chemical entity which can be expressed as a two dimensional chemical structure drawing. Structure drawings are created with the ChemDraw toolbar, which is activated by clicking inside a structure field in the New/Edit Substance form.

FIGURE 3. Create/Edit Substance form with ChemDraw Toolbar 

FIGURE 3. Create/Edit Substance form with ChemDraw Toolbar 

The system maintains its own internal chemical structure database, containing unique substances that can be associated with Inventory Containers. Highly advanced duplicate checking is incorporated into the system to verify that each substance is only entered once in the database. Aside from structure and name, substances can be identified via unique registry numbers such as CAS or ACX ID. Custom fields may also be defined.

d) Searching Inventory

Searches may be performed on any field or combination of fields in a Container or Substance record. For Containers, three levels of search are available: simple, advanced, and structure. The simple search form is a subset of the most commonly used text fields of the advanced search, and is adequate for most purposes. Container barcodes may be scanned into the search form or entered manually.

Structure searches may be substructure, full structure (exact), identity (full structure with specified stereochemistry), or Tanimoto similarity. Additional text fields may also be specified to narrow the search. Structure searching may be done in two situations: to locate a container by the substance it contains, or to locate a substance in the chemical structure database in order to edit it.

FIGURE 4. Substructure Search forms for Containers and for Substances 

FIGURE 4. Substructure Search forms for Containers and for Substances 

The Report engine can produce full reports of search results (see below for more on reporting).

Searching Plates (Inventory Enterprise) 

Plate searching is similar to Container searching, but with added fields for Plate type, format, status, or group, freeze/thaw cycles, molar amount, and other pertinent information.

e) Material Safety Data Sheets

An important feature is the ability for users to add Material Safety Data Sheets (MSDS) to the database, as well as to search both the Inventory and the ChemACX databases for relevant MSDS. The ChemACX database has over 20,000 MSDS that are searchable from the Inventory interface. MSDS may also be created manually or scanned in, and can be in any standard format – ASCII, DOC, PDF, etc. This feature allows users to have instant access to material safety data for a given container.

FIGURE 5. MSDS in Inventory and ChemACX 

FIGURE 5. MSDS in Inventory and ChemACX 

f) Printing

The Inventory interface allows for printing labels as well as generating elaborate reports. Inventory Desktop/Workgroup uses a report engine that incorporates wizards that allow for the quick creation of simple report/label templates that can be shared across an organization. A user has the ability to design a label based on templates for a number of commercially available label sheets (e.g. Avery Dennison). Checkboxes on the New Container form allow auto-generation of barcodes and immediate printing of the label.

FIGURE 6. Reports and Labels 

FIGURE 6. Reports and Labels 

Default templates allow printing labels and reports with or without barcodes and structures. There is a design wizard to help create custom labels and reports. The report engine also has the ability to print to file. It can output reports directly in ASCII text, HTML, XML, PDF, MS Excel, RTF, and several standard graphic formats. You can also email a report directly from the print routine. Discuss barcode printing (any type of barcode or printer). Report designing and printing … location based reports

4. Inventory Enterprise

Inventory Enterprise additionally has a number of features not included in the desktop/workgroup versions. It is a ChemOffice Enterprise application that can be tightly integrated with CambridgeSoft’s Registration Enterprise and chemical procurement ChemACX Database. This application uses Oracle as a backend, and captures both stockroom and reagent needs as well as high-throughput discovery.

Audit Trails

Each change to the database is documented in the Inventory Enterprise 21CFR11 Audit Trails. Electronically filter the data to see all changes made by a particular user, or only the changes made in a particular location between two dates.

FIGURE 7. Standard Audit Trail 

FIGURE 7. Standard Audit Trail 

Managing Oracle Role Based Security

Inventory Enterprise defines Users and Roles which determine what each User can or cannot do at each point in the system. Both tables can be customized by the administrator to give different classes of users the permissions they need, and administrators highly granular control over the database.

FIGURE 8. Users and Roles 

FIGURE 8. Users and Roles 

Plate Management

When integrated with BioAssay Enterprise and BioSAR Browser, Inventory Manager manages plate information, much like other containers. Plate location and contents can be viewed from BioAssay Enterprise and BioSAR Browser interfaces, but this information is stored in the Inventory Manager database. There are a number of options for creating plates in the system including importing information from pipette logs, or creating plates from a SD file allowing for both single compound and mixture plates. Much like the tools used to manipulate other containers, Inventory Enterprise steps you through the process of creating new plates, moving existing plates, creating daughter plates, and dithering plate contents.

FIGURE 9. Plate management

FIGURE 9. Plate management

EHS data handling

Inventory Enterprise can store Environmental Health and Safety data in the substance database.

FIGURE 10. EHS data display 

FIGURE 10. EHS data display 

Administrators can custom-configure the tab—the figure above shows one example. EHS data is created in the substance database, but can be edited (by users have suitable permission) from the container display.

5. Summary

CambridgeSoft offers an inventory option for any sized organization. From the MSDE-based Pro version shared by up to five users, to SQL-based Inventory Workgroup for medium sized organizations, to the Oracle-based Enterprise version for very large organizations, CS Inventory can meet any organization’s current and future needs for reagent and laboratory supply tracking.