OpenClinica User Manual/UsingRegexpForTimefield

Using a regular expression to create a time field
Every now and then you may be in need of a time field. But, however flexible OpenClinica may be with dates and partial dates, there is no standard time-field-type. This can be solved by using a regular expression that checks all entries in a field.

The regular expression
We would like our time-field to accept input like 01:26 or 11:00 or 19:59 or 20:00 or 23:59 but to reject entries like 88:99 or 4:6. Let's first concentrate on time values before 20:00. The regular expression would be [0-1]\d:[0-5]\d meaning more or less: first part of the hours is 0 or 1 ([0-1]), followed by a digit (\d), then the colon followed by the minutes as something in the range from 0 to 5 ([0-5]) followed by a digit (\d). Then the time from 20:00 till 23:59. The regular expression would be quite similar: 2[0-3]:[0-5]\d and that translates to: start with a 2 (2), followed by anything in the range from 0 to 3 ([0-3]), then the colon and the minutes, as described before. The last thing to do is combine these two with a pipe(|): [0-1]\d:[0-5]\d|2[0-3]:[0-5]\d

Testing the regular expression with regexpal
Testing a regular expression in OpenClinica is not an easy task: you have to put the regexp in your XL-sheet and upload it. The syntax is validated, but not the result. In other words: your expression may be correct but accepts the wrong input or rejects the right input. Checking this in OC would require to add the CRF to a study event and then try different values. Then modify the regexp, upload a new version etc, until the results are OK. Fortunately there are tools to do this for you and one of these is http://regexpal.com/. The interface consists of two text areas: one for your regular expression and one for your input. As you can see in the screenshot below I copied the regular expression in the first field and the values I would like to test in the second field. Every bit of the text-string that matches the regular expression is highlighted in yellow or blue and you can see right away that this expression is working fine.

Emulating how regexps work in OpenClinica
If you want to test your regexps using regexpal in a similar way to how they are used in OpenClinica, you can wrap them in the text "^$": ^(regexp here)$ This text restricts how the regexps are processed, ensuring that the match takes up the whole of one line (a similar restriction occurs in OpenClinica).

So, a regexp like this in OpenClinica: regexp: /[0-9]/ Would become this for testing in regexpal: ^([0-9])$ Which would match: 4 but not: d4 (as 4 isn't the only character on the line)

Putting the regular expression in your XL-sheet
Now you can use the regexp in your XL-sheet. Add an item in the usual way and use for RESPONSE_TYPE (col. N) "text", for DATA_TYPE (col. T) "ST" for string. Your regexp goes in VALIDATION (col. V), preceded by "regexp:" and enclosed in slashes. And in col. W goes your VALIDATION_ERROR_MESSAGE. And there you have it: your own time-field.

An alternative way to add a timefield
When you need to compare times in your CRF, you must have numeric data. But in the case described above, the time field is of type ST and that means you can not do anything with it. But if you're willing to sacrifice the time-format, you can more or less achieve this. The trick is to use a field of type REAL and then add a regular expression to it. Can you do that? Well, surprisingly you can. The regexp is used before the data is written to the database.

The other expession
The regexp we will use is regexp: /|[0-1][0-9]\.[0-5][0-9]|2[0-3]\.[0-5][0-9]/

Is works in the same way as the previous regexp, but now the separator is a dot. Because a dot has a meaning in regular expressions, you must precede the dot with a backslash. Now you can use the regexp in your XL-sheet. Add an item in the usual way and use for RESPONSE_TYPE (col. N) "text", for DATA_TYPE (col. T) "REAL" for real. Your regexp goes in VALIDATION (col. V) and in col. W goes your VALIDATION_ERROR_MESSAGE.

And there you have it: a time-field to calculate with.