Creating Dynamic User Friendly Web-Based Data Collection Forms

Introduction

Being members of a clinical research team, we recognized the inevitable need for accurate data collection. Standard forms with structured fields are usually described in the clinical research study protocol at the Case Report Forms (CRFs) section.  

CRFs completeness and accuracy are maintained through a set of drop-down menus and validation scripts coded into the software that is used to submit the collected data. Usually, clinical research electronic data capture software are either built "in-house" by the institution's IT department, purchased from a commercial vendor or adopted from opensource clinical research software consortia. The used software either implements the "classic" normalized database tables or the entity-attribute-value (EAV) model.

Although adoption of a ready-made open source electronic data capture software might seem tempting, many issues might arise due to the restriction of choice to a specific platform, database or web-server. The customization of the software to exactly fit your teams needs will also be limited and dependent on the community or consortium support. Moreover, some consortia puts conditions and legal restrictions that may not suit the clinical research study team. The same issues applies for purchased software, but, moreover, you will have to pay for it.

Our experience in clinical research pointed out the need for an electronic data capture software that allows the authorized clinical research staff to easily design, create, edit and fill CRFs online without the need for any programming experience. The resulting CRF's fields should contain (according to the preference of the designer) Regex validated text boxes and drop-down lists, but also shows a tool-tip with a custom message chosen by the designer. To increase accuracy, a "skip-logic" should exit for some fields. So, we decided to build our own electronic data capture web application fulfilling the needs previously described using VB.NET.

The database used in this sample is MS Access. However, using SQL server as back-end will only need slight modifications in the code and connection strings.

The database is composed of 3 tables: maintab, FollowUp and CRF.

The "maintab" containing the patient name, medical record number (or study number) as well as some "free text note" about the patient. This table serves two purposes: first, the enforced referential integrity will prevent the submission of data for a wrong or missing MRN. This table also act as "in-situ" verification, as it tells the user that he submitted the data for the correct patient name and MRN immediately after form submission.

The "FollowUp" table will contain the data of the submitted forms, where the field "FU" represents the Item of the case report form such as Name, Age, Gender, Diagnosis, etc.. and the field "FUData" will contain the value for that specific item such as "13 years" for Item "Age" or "female" for Item "Gender". 

The table "CRF" will contain the data used by the application to build the data entry form. The items required to be filled by the user will go in the field "Item".  

There are two modes in the application: the "design mode" and the "data submission" mode. The design mode allows the user to create and edit data collection forms through a web interface. This is performed through "add" and "update" queries writing on the "CRF" table.

The "type" of control is either "text" for textbox or "DDL" for drop-down lists. The application uses data from "Item" and "Type" to dynamically create textboxes and drop-downlists with the object name "Item". The field "options" will be filled with list items and respective correspondant values separated by a symbol for the code to identify where to separate list items from values, in our case we used a "$". 

In case of textboxes, the field "validation" is the source of the regex that is used by the programatically created client side validation control, with the property "ControlToValidate" consumed the data from the "Item" field. Other properties of the control such as tooltips or dependent skip logic will also be added programmatically to the object in question. 

Most clinical protocols will tell the designer exact specifications of the forms field names, type of control, if the field is required or not and even the validation conditions for each field. The designer will have to simply "translate" the protocols recommendations into values to be filled into the CRF design web interface, no programming or database design required.  

In the data submission mode the user will select the required CRF from a drop-down menu. The application will store the selected CRF name in a session and redirects the user to the data entry page. the session stored will be used in a select query to extract the data needed to build the form from the table "CRF".

The loaded form will show the properties assigned to each indiviual field as called from the "CRF" database table. The required field validator and the dynamically created regex validation controls will preserve data accuracy and completion while the tooltip pops up at the specified fields to guide the user how to submit correct data. 

Using the code

The solution is composed of two parts: the first part is concerned with data display, search, export to excel and audit trail. To save time we implemented a licensed commercially available ASP.Net code generator to create the aspx pages for the previously mentioned functions. The second part, that we will address in the following lines, is the logic behind the creation of the CRF and insertion of the submitted data into the database.

The Default page of the solution contains the following components: "Label1", "InsertData" button, "PlaceHolder1" and "CRFds" datasource. 

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<div>
<asp:Label ID="Label1" runat="server" style="font-weight: 700; color: #516B92; font-size: large"></asp:Label>
<asp:Button ID="InsertData" runat="server" Text="InsertData" Width="85px"
style="height: 26px" />
</div>
<asp:PlaceHolder ID="PlaceHolder1" runat="server">
</asp:PlaceHolder>
<asp:AccessDataSource ID="CRFds" runat="server"
SelectCommand="SELECT [Item], [type], [options], [ID], [validation], [Tooltip], [required],[IndepControl],[IfValueEquals],[IsVisible] FROM [CRF] WHERE ([formname] = @formname)" 
DataFile="~/App_Data/DataBaseName.mdb">
<SelectParameters>
<asp:ControlParameter ControlID="Label1" Name="formname" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:AccessDataSource>
</asp:Content>

The "Page Load Event" will trigger the Sub "Initialize CRF"

Private Sub InitializeCRF()
    Dim CRFgrid As New GridView
    Session("itemcount") = CRFgrid.Rows.Count
    buildCRF(CRFgrid, CRFds, PlaceHolder1)
End Sub    

which then will pass the following variables to the function "buildCRF" to build the Form with the associated skip-logic and client side validation controls. 

Public Shared Sub buildCRF(ByVal CRFgrid As GridView, _
        ByVal CRFds As DataSourceControl, ByVal placeholder1 As PlaceHolder)

    CRFgrid.DataSource = CRFds
    CRFgrid.DataBind()

    Dim ItemCount As Integer = CRFgrid.Rows.Count


    'build data table using asp table

    Dim table1 As New Table
    table1.ID = "table1"


    For I = 0 To (ItemCount - 1)

        'build table row

        Dim myRow As New TableRow

        Dim itemname As String = CRFgrid.Rows(0 + I).Cells(0).Text.ToString

        Dim ItemNamelabel As New Label
        ItemNamelabel.Text = "" & itemname
        ItemNamelabel.Font.Bold = True

        'build cells inside each row
        Dim c1 As New TableCell()
        Dim c2 As New TableCell()
        c1.VerticalAlign = VerticalAlign.Top
        c2.VerticalAlign = VerticalAlign.Bottom



        Select Case (CRFgrid.Rows(0 + I).Cells(1).Text.ToString).ToLower
            Case "text"
                Dim ItemNameBox As New TextBox
                ItemNameBox.ID = itemname
                'This code is here to initialize skip logic

                If CRFgrid.Rows(0 + I).Cells(7).Text <> " " Then
                    'These nested loop will search for the independentent variable and make its postback = true
                    For Each Rw As TableRow In table1.Rows
                        For Each CL As TableCell In Rw.Cells
                            Dim IndepControl As New Control
                            For Each c As Control In CL.Controls
                                'If the independent control is found, enable its autopostback, and add attributes to the cell
                                If LCase(c.ID) = LCase(CRFgrid.Rows(0 + I).Cells(7).Text.ToString) Then
                                    'The previous IF condition is similiar to Findcontrol method
                                    IndepControl = c
                                    If TypeOf (IndepControl) Is TextBox Then
                                        CType(IndepControl, TextBox).AutoPostBack = True
                                    ElseIf TypeOf (IndepControl) Is DropDownList Then
                                        CType(IndepControl, DropDownList).AutoPostBack = True
                                    End If

                                    'depvar = independant control (gender); depval = condition (is female ); depresult = true visible or not
                                    c2.Attributes.Add("DepVar", CRFgrid.Rows(0 + I).Cells(7).Text.ToString)
                                    c2.Attributes.Add("DepVal", CRFgrid.Rows(0 + I).Cells(8).Text.ToString)
                                    c2.Attributes.Add("DepResult", CRFgrid.Rows(0 + I).Cells(9).Text.ToString)
                                End If
                            Next
                        Next
                    Next
                End If

                'End of skip logic initialization

                c1.Controls.Add(ItemNamelabel)
                c2.Controls.Add(ItemNameBox)

                If itemname.ToString.Equals("MRN") Then
                    ItemNamelabel.ForeColor = Drawing.Color.Red
                    ItemNameBox.BackColor = Drawing.Color.Yellow

                    Dim myrequired As New RequiredFieldValidator
                    myrequired.ControlToValidate = ItemNameBox.ID
                    myrequired.ForeColor = Drawing.Color.Red
                    myrequired.ErrorMessage = "required !"
                    c2.Controls.Add(myrequired)

                End If
                If CRFgrid.Rows(0 + I).Cells(6).Text.ToString = "1" Then


                    ItemNamelabel.ForeColor = Drawing.Color.Red
                    ItemNameBox.BackColor = Drawing.Color.Yellow

                    Dim myrequired As New RequiredFieldValidator
                    myrequired.ControlToValidate = ItemNameBox.ID
                    myrequired.ForeColor = Drawing.Color.Red
                    myrequired.ErrorMessage = "required !"
                    c2.Controls.Add(myrequired)

                End If
                Dim tmpv = CRFgrid.Rows(0 + I).Cells(4).Text.ToString

                Dim tooltipme = CRFgrid.Rows(0 + I).Cells(5).Text.ToString

                If tooltipme <> "" And tooltipme <> " " Then
                    ItemNameBox.ToolTip = tooltipme.ToString
                End If

                If tmpv <> "" And tmpv <> " " Then
                    Dim myValidator As New RegularExpressionValidator
                    myValidator.ValidationExpression = tmpv
                    myValidator.ControlToValidate = ItemNameBox.ID
                    myValidator.ErrorMessage = "wrong data type !"
                    c2.Controls.Add(myValidator)
                End If

                myRow.Cells.Add(c1)
                myRow.Cells.Add(c2)
                table1.Rows.Add(myRow)

            Case "ddl"

                Dim ItemNameBox As New DropDownList
                ItemNameBox.ID = itemname

                Dim tooltipme = CRFgrid.Rows(0 + I).Cells(5).Text.ToString

                If tooltipme <> "" And tooltipme <> " " Then
                    ItemNameBox.ToolTip = tooltipme.ToString
                End If
                Dim opts() As String = Split(CRFgrid.Rows(0 + I).Cells(2).Text.ToString, "$")
                Dim optsLabels() As String = Split(opts(0), ",")
                Dim optsVals() As String = Split(opts(1), ",")
                For t = 0 To UBound(optsLabels)
                    ItemNameBox.Items.Add(optsLabels(t))
                    ItemNameBox.Items.Item(ItemNameBox.Items.Count - 1).Value = optsVals(t)

                Next

                'This code is here to initialize skip logic

                If CRFgrid.Rows(0 + I).Cells(7).Text <> " " Then
                    'These nested loop will search for the independentent variable and make its postback = true
                    For Each Rw As TableRow In table1.Rows
                        For Each CL As TableCell In Rw.Cells
                            Dim IndepControl As New Control
                            For Each c As Control In CL.Controls
                                'If the independent control is found, enable its autopostback, and add attributes to the cell
                                If LCase(c.ID) = LCase(CRFgrid.Rows(0 + I).Cells(7).Text.ToString) Then
                                    'The previous IF condition is similiar to Findcontrol method
                                    IndepControl = c
                                    If TypeOf (IndepControl) Is TextBox Then
                                        CType(IndepControl, TextBox).AutoPostBack = True
                                    ElseIf TypeOf (IndepControl) Is DropDownList Then
                                        CType(IndepControl, DropDownList).AutoPostBack = True
                                    End If

                                    'depvar = independant control (e.g. gender); depval = condition (e.g. is female ); depresult = true visible or not
                                    c2.Attributes.Add("DepVar", CRFgrid.Rows(0 + I).Cells(7).Text.ToString)
                                    c2.Attributes.Add("DepVal", CRFgrid.Rows(0 + I).Cells(8).Text.ToString)
                                    c2.Attributes.Add("DepResult", CRFgrid.Rows(0 + I).Cells(9).Text.ToString)
                                End If
                            Next
                        Next
                    Next
                End If

                'End of skip logic initialization
                
                c1.Controls.Add(ItemNamelabel) : myRow.Cells.Add(c1)
                c2.Controls.Add(ItemNameBox) : myRow.Cells.Add(c2)
                table1.Rows.Add(myRow)
        End Select
    Next
    placeholder1.Controls.Add(table1)
End Sub 

 The "InsertData" Button Click will pass the following variables and trigger the "insertCRFdata" function. Note that the "MRN" is the Medical Record Number and in the unique identifier for the patient in a clinical trial.

Public Shared Sub insertCRFdata(ByVal mydb As OleDbConnection, _
          ByVal myitemcount As Integer, ByVal placeholder1 As PlaceHolder, _
          ByVal label1 As Label, ByVal activeuser As String)
    mydb = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data " & _ 
       "Source= |datadirectory|DataBaseName.mdb;Persist Security Info=True")
    mydb.Open()
    Dim part2 As Integer = myitemcount
    Dim myMRN As String = ""
    Dim myTable As New Table
    For Each T As Control In placeholder1.Controls
        If T.ID = "table1" Then myTable = CType(T, Table)
    Next
    For Each Rw As TableRow In myTable.Rows
        For Each CL As TableCell In Rw.Cells
            For Each C As Control In CL.Controls
                If C.ID = "MRN" Then myMRN = CType(C, TextBox).Text
            Next
            For Each C As Control In CL.Controls
                If TypeOf (C) Is TextBox Or TypeOf (C) Is DropDownList Then
                    If C.ID = "MRN" Then GoTo 10
                    Dim sqlstring = "insert into [followup] ([mrn], [fu], [fudata], " & _ 
                      "[formname], [enteredby]) values (@mrntxt, @mylabel, @mydata, @myformname, @myenteredby);"
                    Dim mydbcommand As New OleDbCommand(sqlstring, mydb)
                    mydbcommand.Parameters.Add("@mrntxt", OleDbType.VarChar).Value = myMRN
                    mydbcommand.Parameters.Add("@mylabel", OleDbType.VarChar).Value = C.ID
                    If TypeOf (C) Is TextBox Then
                        mydbcommand.Parameters.Add("@mydata", OleDbType.VarChar).Value = CType(C, TextBox).Text
                    ElseIf TypeOf (C) Is DropDownList Then
                        mydbcommand.Parameters.Add("@mydata", OleDbType.VarChar).Value = CType(C, DropDownList).SelectedItem.Text
                    End If
                    '-------------
                    mydbcommand.Parameters.Add("@myformname", OleDbType.VarChar).Value = label1.Text
                    mydbcommand.Parameters.Add("@myenteredby", OleDbType.VarChar).Value = activeuser
                    '------------
                    mydbcommand.ExecuteNonQuery()
10:                 End If
            Next
        Next
    Next
    mydb.Close()
    
End Sub

Since the data stored in the EAV table isn't usable “as-is”, we needed to find a way to process the data into a meaningful table with the “classic” fields and records to be then processed by statistical software.

Using the “advanced” search page available in the application, the user choose the range of data to be extracted from the EAV table “Followup” using web enabled filters. The user then exports the EAV data table into an MS Excel file. Using the following line of VBA, the EAV table is “pivoted” into a “classic” table.

Sub Button1_Click()
Dim srcworksheet, trgtworksheet As New Worksheet
Set srcworksheet = Sheets(1)
Set trgtworksheet = Sheets(2)
Dim CurrentTargetRow, CurrentTargetColumn As Integer
CurrentTargetRow = 1
CurrentTargetColumn = 1
For i = 2 To 1E+21
    currMRN = srcworksheet.Cells(i, 2).Value
    If currMRN = "" Then Exit For
    currField = srcworksheet.Cells(i, 8).Value & "_" & srcworksheet.Cells(i, 3).Value
    currFieldDat = srcworksheet.Cells(i, 4).Value
    UniqueID = Str(srcworksheet.Cells(i, 5).Value) & " " & Str(srcworksheet.Cells(i, 6).Value) & " Entered by: " & srcworksheet.Cells(i, 7)
    'Search for the current MRN in the target sheet. If found, record its row number.
    'If not, then the row number should be set to the maxrownumber + 1 (i.e. new row)
    
    For t = 2 To 1E+21
        If Val(currMRN) = Val(trgtworksheet.Cells(t, 1).Value) Then
            If trgtworksheet.Cells(t, 2).Value = UniqueID Then
                CurrentTargetRow = t
                Exit For
            End If
        End If
        If trgtworksheet.Cells(t, 1).Value = "" Then CurrentTargetRow = t: Exit For
    Next
    
    'Search for the current field in the field labels of the target sheet.
    'If found, just add the value, if not, then add this label and increase the LastTargColumn count
    
    For r = 3 To 1E+21
        If currField = trgtworksheet.Cells(1, r).Value Then
            CurrentTargetColumn = r
            Exit For
        End If
        If trgtworksheet.Cells(1, r).Value = "" Then CurrentTargetColumn = r: Exit For
    Next
    
    'Set the values
    trgtworksheet.Cells(CurrentTargetRow, 1).Value = currMRN
    trgtworksheet.Cells(CurrentTargetRow, 2).Value = UniqueID
    trgtworksheet.Cells(1, CurrentTargetColumn).Value = currField
    trgtworksheet.Cells(CurrentTargetRow, CurrentTargetColumn).Value = currFieldDat
    
Next
End Sub

For large Case Report Forms, we enabled the off-line composition of an excel file containing the CRF data. The user then will simply upload the file, then some lines of code will perform the insert query. 

We also enabled the “un-pivoting” of classic tables to help the user importing his existing patient data into the solutions database. The user shall use the following VBA code in an excel sheet to convert the classic table into EAV.

Sub Button4_Click()
Sheets("Sheet2").Cells(1, 1).Value = "MRN"
Sheets("Sheet2").Cells(1, 2).Value = "FU"
Sheets("Sheet2").Cells(1, 3).Value = "FU Data"
MaxRows = 999999
MaxColumns = 9999
x = 2
For i = 2 To MaxRows
    
    d = Sheets("Sheet1").Cells(i, 1).Value
    If d = "" Then Exit For
    
    For t = 2 To MaxColumns
        s = Sheets("Sheet1").Cells(1, t).Value
        e = Sheets("Sheet1").Cells(i, t).Value
        If s = "" Then Exit For
        Sheets("Sheet2").Cells(x, 1).Value = d
        Sheets("Sheet2").Cells(x, 2).Value = s
        Sheets("Sheet2").Cells(x, 3).Value = e
        x = x + 1
    Next
Next
End Sub