BioRails Utilities¶

• BioRails Utilities
• Installation
• Dependencies
• Deployment
• Functionality
• Data Import
• Catalogue Integration
• Template Management

The utilities in this package are unsupported, an experimental code stream. Like google apps they will be matured to a supported product if the demand is there and the quality is high enough. Whilst these utilities are currently being written by the BioRails team, we would be delighted to accept contributions.

Installation¶

Download InstallPack from the files section

Dependencies¶

• MS Office XP, 2003 ¹, 2007
• BioRails access
• BioRails4Office 3.0
• BioRails 3.0

Deployment¶

The utilities are packaged as a single, unprotected and uncertified XLA. To install,

1. Download the distribution
2. Unzip to a local drive
3. Move the XLA (or a short cut to it) to your XLSTART folder or a folder in which XL looks when starting up. Alternatively, add a shortcut to the file on your desktop and double-click this file when you want to use it.
4. Restart Excel

A new command menu should appear when you restart Excel although you are likely to be prompted with macro security warnings

Functionality¶

The BioRails Utilities package has been developed to simplify the process of automating Excel and extending the basic functionality of BioRails4Office ². The three key areas are

1. Data import from plate readers and mass spectrometers
2. BioRails Catalogue Integration
3. Template Management

Data Import¶

The data import utilities provide a generic methods of importing files using parameters that describe the data file being imported. These parameters are stored in import definitions which are persisted as YAML files. Eventually these will also be persisted as records within BioRails.

There are two broad categories of file to import we are supporting: plate reader files and files used to seed and process sample files from spectrometers

Import Reader Data

These files contain readings of counts per minute or optical density etc. The data for one plate may be contained on one file or a single file holds many pates worth of data.

1. Click the Import Reader Data button
2. Browse an import definition file
   1. You will be prompted to add an import sheet if one does not already exist
   2. A preview of the import file format is shown and you are prompted for a file to import
3. The data from the file is transformed to a set of columns on a sheet named BRImpData. Each data area is also identified as a named range (BTImpData[n])

As the sheet name is consistent, its is possible to map this sheet into a template and therefore automate the processing of the data.

Manage Import Definitions

Import definitions can be created and modified.

1. Click Manage Import Definitions
2. Select an import definition, cancel this selection and you will be prompted to save a new import definition
3. The Import Definition is now loaded and can be edited.

Import Definition Toolbar

Button	Purpose
New	New import definition
Open	Open existing import definition
Save	Save import definition
Save As	Save import definition as
Reload	Reload import definition
Preview	Print Preview of layout of import file

Options

Option	Description
File Pathname	Full pathname to the import definition file
Name	Name of the import definition (by default taken from the filename)
Description	Textual escription of the import definition
Default Folder	Default folder in which to browse for the import file
File format	File format of the import file, Either CSV, TXT or Excel
File Headers Count	Number of header rows in the document before the data areas start
Header Info Column	A column in the file headers containing some text that needs to be recovered
Header Info Row	A row in the file headers containing some text that needs to be recovered
Data Keyword	Locate a data area by keyword (Todo)
Data Headers Count	Number of header rows in each data area
Data Header Info Col	A column in the data headers containing some text that needs to be recovered
Data Header Info Row	A row in the data headers containing some text that needs to be recovered
Data Areas Count	The number of data areas in the file
Data Rows Count	The number of data rows in a file
Data Areas Horizontal	Check if data areas are layed out left to right
Data Column Start	The column in which the data areas start
Data Columns Count	The number of column in each data area
Data Rows Count	The number of rows in each data area
Data Read Across	Check is the data is to be read across the columns then down (matrix format)
Data Footers Count	The number of footers with each data area

Clear Imported Data

This button simply clears the data from the import sheet, along with the named ranges

---

Create Sample List (TODO)

Functionality is provided to generate a list of sample identifiers based on the conditions of the sample.

Import Sample Data (TODO)

Using the sample ID as a key, the data file is parsed and written out to a flat report suitable for import to Graphpad Prism and Parsight WinNonLin.

Manage Sample Definitions (TODO)

Set up the parameters for sample list generation and sample file import

Catalogue Integration¶

BioRails has a central catalogue of dictionaries. The look-ups defined in this catalogue are often implemented as parameters in assay definitions and then in process definitions. When a Task is implemented against a process using dictionary based parameters, the dictionaries can be automatically linked in to provide the user with drop-downs within Excel. This has the benefit of ensuring the user only uses registered terms when capturing data and improves the usability and speed of adding information to a Task in Excel. Infact, this functionality replicates the functionality presented in a BioRails Task Sheet tab.

Add Task Dictionaries

In Excel, if the active sheet is a Task Sheet or Task Template, this button will retrieve the dictionaries used to Build that Task and persist them in a hidden (CATALOGUE) sheet. It will then work through the Task Values, adding list based validation rules to each of the cells that requires a look up. This is done using the comment field automatically written out to the sheet when the task was created.

The drop downs will contain only the first 25 values in the dictionary, therefore the cells allow free text to be entered which can be validated as a separate function, see below.

Validate Dictionary Entries

Once data has been added to a cells that includes dictionary integration, validation can be run over the cells to check that the values are available in BioRails and the data will therefore be posted correctly. The function runs over the active sheet which does not need to be a Task Sheet or Task Template.

The routine will modify the dictionary linked cells in one of three ways

State	Result
The Cell value exists as a dictionary value and is unique	The cell is filled green
The cell value does not exist as a value in the parent dictionary	The cell is filled rose
The cell value starts with text that has multiple values in the parent dictionary	The cell fill remains pale yellow and up to 25 values starting with the same text

Add Dictionary to a Cell

Once a dictionary has been added to the workbook from a Task Sheet/Template, it can be added as cell validation to any cell within the workbook. In future this functionality will allow the user to select any lookup in BioRails.

Validate Selected Cell

Applies the same level of validation as above but for a single cell. This is useful for looking for a specific value in a cell, reducing the number of values until the correct value is selected.

Show/Hide Dictionaries

The dictionaries are persisted locally in a hidden sheet called CATALOGUE. This can be shown and hidden.

Add dictionaries right-click menu

Code can be added to the active workbook to add dictionary functions to the right click function of a cell. This has to be used with caution as it requires that security levels are reduced and can leave the workbook vulnerable to malicious software.

Template Management¶

These routines provide simple methods of handling workbook protection and exposing hidden sheets etc. A password is registered and this is then used throughout the BioRails Utilities to handle protection making the templates written in Excel to process BioRails data more robust.

Password

A password can be registered with the BioRailsUtilities XLA. This password is then used to handle all protection (and un-protection) within workbooks managed by the utility.

Open Protected Workbook

Using the stored password, a workbook protected at the file level can be browsed and opened in a single step. The workbook and its pages will still be protected however. This feature is useful for opening legacy templates for example, as long as the password for the legacy template is known.

Unprotect Sheets

Using the stored password, the workbook and sheets are unprotected, removing the password protection.

Protect Sheets

Using the stored password, the workbook and sheets are protected, adding password protection.

Unhide Sheets

Using the stored password, the workbook and sheets are unprotected and hidden sheets made visible.

¹ Main supported platform

² Disclaimer: BioRails Utilities is unsupported and should be considered an experimental code stream. It is not recommended for use in production systems.