OpenClinica User Manual/AutomatingCRFs

Automating test data, range validation and content separation

The system described below is an extension to standard OpenClinica CRFs and attempts to make it easier for experienced OpenClinica developers to work with OpenClinica by automating and simplifying common tasks:

1. Automate test data
2. Automate range validation (and validation error messages) for numeric data
3. Automate variable name uniqueness and length checking (to help avoid OID issues)
4. Separate content from presentation in descriptions and left item names
5. Present the CRF sheet at a reduced, but usable size on a single monitor

Then, either:

1. You can manually enter the automatically generated test data into CRFs for testing

Or optionally:

1. You can set up a Word merge to merge test data into a Word version of the paper CRF to test the system
2. The Word merge also produces a marked-up CRF with code lists for data analysis

Contents of the system

1. A Calculations spreadsheet with sheets named Calculations and Merge (separate from the main OpenClinica spreadsheet)
2. Optionally, a Word version of the paper CRF to merge the test data into (using straightforward mail merge)

The data flows are:

1. Calculations spreadsheet (item data on Calculations sheet)-> OpenClinica spreadsheet
2. Calculations spreadsheet (test data on Merge sheet) -> either a Word CRF or a paper CRF

If multiple Openclinica CRFs are used to model a single paper CRFs, then an additional spreadsheet (with up to 256 data points) can be used to merge the merge sheets prior to the Word merge. If more than 256 points are needed, multiple Word merges must be done.

Downloadable documents

The example documents (for OpenClinica 3.1.2) below show the process detailed on this page:

1. Example OpenClinica CRF spreadsheet
2. Example Calculations spreadsheet
3. Example Word CRF with merge fields and comments

Advanced downloadable documents

If you have multiple OpenClinica CRFs representing a single paper CRF:

• Excel spreadsheet combining test data from multiple spreadsheets

Extra detail on merging the data (in case you need it):

1. Example CRF prior to inserting merge fields and comments
2. Example CRF with merge fields, but prior to inserting comments

The Calculations spreadsheet

There are two sheets in the Calculations spreadsheet:

• The Calculations sheet contains formulae to calculate test data, range limits and error messages (amongst other things).
• The Merge sheet references the test data in the Calculations sheet and updates automatically

The Calculations sheet

The sheet is most easily used with a wide screen, where the three main parts of the sheet can be visible at once. The three main parts are:

1. On the left, the content entry (in white), which forms the basis for the next two parts:
2. In the centre, the automatic test data (in orange and green) and:
3. On the right, the CRF code (in yellow)
4. Off-screen most of the time, the Error check section (pink) checks for errors

As you enter data in the Content Entry area, the test data is generated in the centre, and the values that OpenClinica needs are generated on the right. When you have finished entering data, you need to copy and paste as values the right (yellow) section from the Calculations sheet to the Items sheet in the OpenClinica spreadsheet before uploading it to OpenClinica.

The Error check section, hidden off-screen section that catches if ITEM_NAMES aren't unique and don't exceed a certain length (specified above the column). If an error is found, the title cell of that row is highlighted in red (scroll across to see the error)

Fields that differ from regular OpenClinica usage

• Title: Used as the basis for DESCRIPTION and LEFT_ITEM_TEXT.
• Short Name: Generally capitalised, with the addition of Prefix (below), this is directly translated to ITEM_NAME
• Prefix: A two letter prefix for the Short Name (see CDISC's CDASH for possible values)
• Min: The minimum test value acceptable. Used to automatically populate the test data section and the fields: RESPONSE_TYPE, RESPONSE_LABEL, DATA_TYPE, WIDTH_DECIMAL, VALIDATION and VALIDATION_ERROR_MESSAGE. Date test values should be entered as string literals (preceded by quote characters, e.g. '24 Apr 2011)
• Max: Same as the field above, but this is the maximum test value acceptable.
• Decimal places/DATE: Helps define the DATA_TYPE and WIDTH_DECIMAL. Possible values are ST, DAT, a positive integer or empty (equivalent to 0)
• Validation Override: If you would prefer to have a regexp validation for a field, rather than a range, you can specify the regexp here.
• Message Override: If you have overridden the validation (in the above Validation Override), specify the message you want to appear when the regexp is triggered
• Extra text under: Text that should appear under a field (appended to description as an additional paragraph)
• Extra text right: Text that should appear to the right of a field
• Section - synonym for SECTION_LABEL, except that you only need to mark the start of sections

Fields that are used identically to OpenClinica

• Group: synonym for GROUP_LABEL
• Units: as UNITS
• Col: synonym for COLUMN_NUMBER
• Page Number: synonym for PAGE_NUMBER
• Question: synonym for QUESTION_NUMBER
• RESPONSE_TYPE
• RESPONSE_LABEL
• RESPONSE_OPTIONS_TEXT
• RESPONSE_VALUES_OR_CALCULATIONS
• RESPONSE_LAYOUT
• DEFAULT_VALUE
• Req: synonym for REQUIRED
• Extra text right: synonym for RIGHT_ITEM_TEXT
• Header: as HEADER

Some fields are used infrequently, a manual change could be used when they need to be modified (don't forget to highlight this modification with formatting): SUBHEADER, PARENT_ITEM, PHI

What to copy

In the calculations sheet, select all yellow cells from row C to the last entry with a valid item name

Where to paste (as values)

In the Items sheet of the OpenClinica spreadsheet, select cell B1 and then paste as values

The separation of presentation and content as it appears in OpenClinica

The text under 'Test item 3' was entered as plain text in the 'Extra text under' column.

Maintaining the sheet

• As the white editable sections aren't protected, avoid dragging and dropping, or cutting and pasting to move cells there. Instead, use copy and paste, then delete old data
• If more than 256 rows are needed, the numbers of rows in the sheet can be extended by selecting the lowest row and filling rows further down the spreadsheet (see The Microsoft Excel Fill reference) after unprotecting the sheet.
• Formulae can be repaired by dragging and filling

What this sheet doesn't help you do

1. It doesn't create complex test data, sometimes you have to override specific cells - format them differently so that they aren't altered accidentally
2. It doesn't update the Section, Group or other sheets
3. It doesn't create test data for all possible select values, only the extrema
4. It doesn't create a test plan, either to test itself or to test rules or calculations

Updating the date of the test visit

The merge sheet has a manually entered '1 Visit date' column, where the date of the visit can be updated.

Merging the data into the Word CRF

1. Before trying to merge data, ensure that the topmost sheet in your test data workbook contains the data you wish to import (you may need to resave your workbook).
2. Open your Word CRF.
3. In Word 2010, select the Mailings tab, and click 'Select Recipients', then 'Use Existing List' and select the test data workbook.
4. When you are prompted for the data source, click 'Show All', and select 'MS Excel Worksheets via DDE (*.xls)'. DDE prevents accuracy issues with floating point numbers, where numbers such as 1.99 are displayed as 1.989999999 on merge.

If not already available as a data source type, DDE may need to be enabled. For example, in Word 2010, click File, Options, then select Advanced, and in the General section, tick the 'Confirm file formation conversion on open' checkbox.

Limitations of Word merge

Up to 256 fields are available to Word's merge facility (see How to design and set up a mail merge address list in Word), so if you have more than 256 data points, you will have to merge multiple workbooks into multiple CRFs.

Insert merge fields throughout the CRF

Open the example CRF document (above) to see examples of how merge fields appear.

Where necessary, use rules to replace items such as checkboxes

You can use Xs and Os to represent checked and unchecked tickboxes from paper CRFs.

Use mail merge preview to check your fields are working correctly.

To aid data analysis, you may want to mark up the document where rules have been used to highlight codelists

Add balloon comments to mark-up the document.

• To print the test data for data entry, hide the comments when printing, and perform a mail merge.
• The unmerged CRF works as a marked-up CRF for a data analyst

Merging test data from multiple workbooks for a single CRF

Word merge only takes a single, wide spreadsheet as input, so you may need to combine test data from the individual CRF workbooks. In this spreadsheet, data is collected as vertical columns, then transposed to rows for the Word merge.

1. First, create sheets in the 'test data.xlsx' workbook, and create references to the test data in the Calculations sheet of each of your OpenClinica CRFs.
2. Second, in the 'Combined' sheet, create dates for your visits, then transpose the data from the sheets

As test data in the OpenClinica CRFs is updated, the 'test data.xlsx' will also automatically update

Adding references to CRF workbooks in the 'test data.xlsx' workbook

Create sheets in the 'test data.xlsx' workbook for each CRF, then create references to the ItemName column and test data columns. See the 'Test data.xlsx' spreadsheet for examples. The data should look like this:

Transposing the CRF sheets to the Combined sheet

1. Switch to the Combined sheet and in the first blank cell on the top line start entering this formula '=TRANSPOSE('.
2. Switch to the CRF sheet, select the data to transpose, then close the brackets of the formula.
3. Your formula should look like this: '=TRANSPOSE('1 Visit'!A1:G10)'
4. Switch back to the Combined sheet, starting at the cell containing the formula you have just entered, select a destination area that is as wide as the source is long and is as long as the source is wide.

1. To transpose, select the area, then press F2, then CTRL-SHIFT-Return

Repeat this for all available CRFs.

Finally: When saving, please select the Combined sheet to be topmost. The Test Data workbook should now update as the CRFs updates.

Ideas for Future Improvements

• The calculations and merge sheets are currently stored in a separate spreadsheet, in an effort to keep this data separate from the data that OpenClinica sees. If we could be sure that OpenClinica wasn't affected by these additional sheets we could use a single CRF. Further, if the Items sheet could include calculations (which it currently can't), then the items sheet could just be references to the Calculations sheet, and no copying/pasting would be necessary
• Codelists (RESPONSE_LABELS and RESPONSE_OPTIONS) could be stored in a separate sheet, and the codelists could be included by using a simple text reference.
• The Required field could be amended to simulate rules, with a reference to other fields that trigger whether the test data is required, and with what value (e.g. when field A is 1, then the test data should be required).