Documentation

Publish Date:  November 5, 2007
Version 3.0

Overview

Web Data Xpress is a web-based utility that allows users to add and edit records in a database. Rather than having a separate web program for every questionnaire, Web Data Xpress uses a database driven system to display and store many different types of forms. The user selects the desired form, enters the primary key information for the record and can then begin entering data. Web Data Xpress has the following features:

bullet

A general-purpose web-based system for adding and editing records in a database.

bullet

A method for organizing forms into projects for easier user management.

bullet

A method for creating forms and altering their appearance.

bullet

A method for editing user permissions for those projects.
 

Using Web Data Xpress

I.     Getting Started

II.    Setting Up User Accounts
       
Viewing User Accounts
       
Editing User Accounts

III.   Working with Forms
       
Creating a New Form
       
Setting Form Properties
       
Editing Forms
       
Branching

IV.  Data Entry
        Selecting a Form
        Setting Primary Keys
        Creating a New Record
        Editing an Existing Record
        Entering Data
        Canceling or Deleting Records

V.   Important Information
        Designing Forms:  Best Practices
        Using “Shorthand” to Obtain Previously Entered Form Values
          Using Default and Dynamic Default Values
          Formatting Q_Text, Q_TextShort and Validation Text
          Using Column Descriptions as Q_Text
          Form Examples

 Branching

 Combining Data Using Checkboxes

 Data Validation for Dates

 LookUpSQL

VI.  Glossary

VII. Support


I.  Getting Started

Before you begin using Web Data XPress, you must first contact the Office of Academic Computing so that a project can be created for you.  Once a project has been created, you can then begin to create data entry forms as well as set up user accounts.


II.  Setting Up User Accounts

Note:   Only Supervisors have permission to edit User Accounts.

Viewing User Accounts

A project is an organization of forms. Forms belong to projects, and each project has its own list of users and permissions. Supervisors can add and drop users to projects through Web Data Xpress To view accounts for a specific project, click on the "Manage User Accounts" link on the left navigational menu.  The User Accounts page will look similar to the following:

Select a project that you want to edit user accounts for by selecting it from the drop-down list.   A table with a list of users will appear. The table is made up of three columns: 

Account Name

This is the NT user login name. You must specify the user’s proper NT login name (the same that is used to log into the network) in order to give them permission to use forms belonging to a project. A user must first have a NT login to be able to use Web Data Xpress.

Account Role

The account role can have the following values:

General User

This user can enter and edit data in all the forms of a project.

Data Manager

This user can enter and edit data as well as make changes to all the forms of a project.

Supervisor

This user can enter and edit data, make changes to all the forms of a project, and can create user accounts for a project.

Account Status

The account status can have the following values:

Active

The user now has full permissions according to their role.

Add

The user is scheduled for receiving permissions. They currently do not yet have permissions to the project.

Drop

The user is scheduled to be dropped from the project. If a user has this status, they will not appear in the user list. Permissions for the project are immediately revoked.

Error!

Something went wrong with either adding or dropping the user. You can drop the user again to remove the account, or contact your Database Owner (DBO) for more assistance.


Editing User Accounts

This is an example of what your screen may look like when adding an account to Web Data XPress:

You can add and drop user accounts by using the following controls:

Click this button to add a user to the project.  After clicking on Add, the following controls appear:

 

Account Name

This must be the exact NT username the user uses to login.

 

Account Role

This is the role the user will have. You can select "Data Manager," "General User" or "Supervisor."  The default is set to "General User."

 

This will schedule the user to be added to the project. The user should be active within fifteen minutes.

 

This brings you back to viewing the accounts.

Select a user by clicking on their name link. Their name should appear highlighted. Click on “Drop” to remove the user. The user will no longer be able to access that project. It takes fifteen minutes for the user to be fully dropped.

Note:  If you wish to change the Account Role of a user, you must Drop the user, and then Add the user again with the new Account Role. The account must have finished being dropped before it can be added again. This usually takes fifteen minutes.


III.  Working with Forms

Creating a New Form

Note:  Only Supervisor or Data Manager accounts have permission to create new forms.

To create a new form, click on the "Manage Forms" link on the left navigational menu.  Then, click on the "Create New Form" button.  This will take you to the Create New Form page:

The following parameters will need to be entered in order to create a new form.  Once this information has been entered, click on "Go."

Database

Enter the name of the database for the data table.

Table

Enter the name of the destination table. The owner of the table must be specified (owner.tablename)

Project

Select from the drop-down list which project the form will belong to.

Driver Table Name

Enter the name of the driver table that will run the form (i.e., drv_tablename). It can be an existing driver table, or a new one.  We recommend the use of the "drv_" prefix so that the driver table is easily recognizable, however, it is not necessary and will not cause errors if you choose not to use the "drv_" prefix.  If the table with the same name already exists, you will be asked if you wish to use that driver table. Driver tables made for different data tables will be incompatible and cause errors on the web page.

Form Short Name

Enter the name of the form.  This must be unique.

This will create the form.

This button resets all values on the page.


If the driver table was created successfully, you will be ta
ken to the Form Properties page.  If not, you will see a warning message. You can try changing the values of the properties and try again.

Setting Form Properties

Note:  Only a Supervisor or a Data Manager can set the properties of a form. 

Forms are identified by their short name, which must be unique in the database.  After successfully creating a new form, you have the option to set most of the default values of the form.  The form's Short Name and Driver Name cannot be changed.  The following is an example of what a Form Properties page might look like:

Each form has the following properties:

Short Name

A brief label for the form. This is the unique identifier for the form and cannot be changed through Web Data Xpress once it has been created.

Destination Name

This is the table where the data is stored. The user can change this value to a different data table, provided that the current driver table is compatible with the new destination table. Otherwise it will cause page errors. The user edits this value at his or her own risk.

Questions Per Screen
 

This is the number of questions that the user will see on each screen during data entry.  This value can range from 1-100 and is based on the Q_Num of your question.  Since Q_Num is a float value, questions can be numbered using decimals.  For example, suppose you have Q_Nums 1.111, 1.112, 2, and 3, and you specify "3" questions per screen, you will see questions 1.111, 1.112, 2 and 3.  This is because 1.111 and 1.112 is considered as one question.  But, if you have Q_Nums 1, 2, and 3, you will see question numbers 1, 2, and 3.

 

If you want your form to look exactly like your paper form with certain questions grouped on the same page, float notation (i.e., 1.111, 1.112, 1.113) can be used along with setting the "Questions Per Screen" to 1.  This will ensure that all questions with "1._" will be grouped together, giving you more control over what questions are displayed on each page of your form.  The default value is "10."

 

Web Data XPress is a dynamic program that assigns questions on a page based on several surrounding factors; the chosen questions per screen, the Q_Nums of the questions, and any branches that occur.  Web Data XPress always tries to put the maximum number of questions possible on the screen.

 

Note:  If backing up from a branch record or a submit page, the questions may not appear in the same grouping as they previously did.  This is because Web Data XPress recalculates the number of questions per screen based on where the user backed up on the form.  Also, if "Questions Per Screen" is set for example to 10 and you choose to have a branch record on the 5th question, this will force a page break for that question, making the 5th question the last question on the form.

Verbose

The questions on a form can be in either long (verbose) or short format.  The text used for long and short questions is determined by the settings in the Q_Text and Q_TextShort fields of a form.  The default value is "Yes," which indicates that the questions are in long format.  If you wish to use short questions, you will need to change the default value to "No."  For more information, please refer to the Editing Forms section of this document. 

Key Label

This will display the primary key(s) under the title on each page of your form.  If you do not want the primary key information displayed such as when using social security numbers or any other information that may be confidential or not relevant for the user, you will need to  change the default value to "No." 

Show Table

This will allow the "View Table" button to be visible during data entry.  If the user clicks the "View Table" button before the primary key(s) are entered, the entire table will be visible.  If the "View Table" button is clicked after the primary key(s) are entered, the records shown will be filtered on the primary keys entered.  If you do not want the table to be visible during data entry, you will need to change the default value to "No."

Jump to Submit

The "Jump to Submit" button will take the user directly to the submit page from any question page on the form.  This allows the "Jump to Submit" button to be visible during data entry and questions to be skipped.  If you do not want the "Jump to Submit" button to appear on your form during data entry, you will need to change the default value to "No."

Page Numbers

This puts navigational page numbers on the form allowing the user to skip questions by jumping to non-succeeding pages.  If you want to ensure that the user answers the questions in succeeding order, you will need to change the default value to "No."

Submit Page Info

This will allow you to include any comments or instructions on the Submit page of your form.  Note:  The information on the Submit page will come from the parent form's "Submit Page Info."  If there is a branch to a child form, the child form "Submit Page Info" will not be shown on the Submit page.  For more information, please refer to the Branching section of this document.

Driver Name

This is a table describing the mechanics of the form. The user cannot change the name of the driver name.  

Form Title

The form title can be changed in this text box.  When selecting a form for data entry, the form title (if Active) will appear in the drop-down list of available forms.  The form title will also appear at the top of all Data Entry pages. 

Project Name

This is the name of the project that this form belongs to.  Use this drop-down list to change what project a form belongs to.  Permissions to a form are based on what project the form is attached to.  Each form can only belong to one project at a time. If you want a data table to belong to two different projects, you will have to make two separate forms for that data table.

Status

This is the current state of the form.  You may change the status of a form by using the drop-down list.  Switching a form to Development removes it from the drop-down list of forms on the main Data Entry page.

 

Active

The form is in production and can be used to enter data into the database by any user who has permissions to the project. The name of the form will appear in the drop-down list on the Data Entry page.
 

ActiveHidden

The form is in production and can be used to enter data into the database by any user who has permissions to the project. The name of the form will be hidden from all users in the drop-down list on the Data Entry page.  This status can be used for child forms that will be branched to from other forms.
 

Development

The form is in the database but not in production. Only Data Managers and Supervisors can enter and edit data using this form. This status offers Data Managers a chance to test and make changes to the driver table or the form properties before going online.  A form that has just been created will have this status.
 

ReadyForActive

The form is scheduled to go into production. The system will change the owner of the driver table to DBO, allowing others to be given access to the form. Once the system has processed the form, its status will be set to "Active."
 

ReadyForActiveHidden The form is scheduled to go into production.  The system will change the owner of the driver table to DBO, allowing others to be given access to the form. Once the system has processed the form, its status will be set to "ActiveHidden."
 

Drop

The form is scheduled for removal. The form cannot be edited nor can it be used for entering data. If all forms that use a single driver table have been dropped, then the driver table is removed as well.
 

Error!

There was a problem with either adding or dropping a table. Please contact the WPIC Office of Academic Computing for assistance.

In addition, the Form Properties page has the following controls:

This saves your current changes to the form.  This will return an error if you do not have permissions to edit the form.

This will schedule the form for deletion.  The form will be deleted by the system.  When a form is dropped, it immediately disappears from the drop-down list on the Data Entry page and cannot be seen by other users.

This takes you to the Edit Form page.  Data types, SQL lookups, validation rules and other attributes can be edited there.

This takes the user to the Data Entry page.  The user can use this option to test data entry in a new form that is not yet available to other users.  This will enter information into the actual database.

This button displays a view of all records in the current table. 

 

Editing Forms

Note:  Only Supervisor or Data Manager accounts have permission to add a new form or make changes to existing forms.

To edit an existing form, click on the "Manage Forms" link located on the left navigational menu.  On this page, you can view the status of all forms pertaining to your project(s) in the database along with their properties.  In addition, you may also create a new form as described above.

The following is an example of what the Forms Management page might look like:

You can edit most properties of a form, except for the Driver Table and Short Name. To edit an existing form, click on the form's Short Name. You will be redirected to the Form Properties page.  On this page, click the "Edit Form" button which will redirect you to the Edit Form page.  The Edit Form page allows you to change many attributes of each question in the form.  This page also has controls that let you manipulate the form. 

If your form is an Active or ActiveHidden form, you should first verify that no one is currently using the form and switch your form status to Development before editing your form.  Editing a form while it is in use will result in errors for anyone using the form.  If you edit a form that has records in the hold table, the time and username of the person who entered the records will be displayed along with a message indicating that the records will be cleared before editing can be continued.  If you click "yes" to continue, then the records in the hold table will be deleted.  Please use this with caution because these records cannot be retrieved.

The following is an example of what an Edit Form page might look like:

When you choose to edit a form, you will be taken to the first question of the form. Each question is a record entry into the form's driver table.  When a driver table for a form is created, the FieldName, Q_Num, Q_Text, Q_TextShort, Q_Type, LookUpSQL, ValidationRule, ValidationText, Required, Submit, DefaultValue, and Branch fields are automatically created using the default values from the destination table.  Any default values for the form may be changed on the Edit Form page. 

Note:  If you do not want default values for a particular field name to be changed during data entry, a "read-only" data type should be used.

The following controls let you manipulate the form. 

Jump to Q_Num

Use this drop-down list to jump to a particular field. When you skip to a field, the current one is validated and saved before continuing. If the field is invalid, you will not be able to skip to another field until the current problem is fixed.


These buttons cycle to the previous or next fields. When you cycle to a field, the current one is validated and saved before continuing. If the field is invalid, you will not be able to skip to another field until the current problem is fixed.

When you reach the last field, click on "Done" to return to the Form Properties page. The last field will be saved.

This resets all information on the page to their previous values before the last save.

This brings up a clean page that allows you to enter a new record into the driver table. This could be when a new column is added to the destination table.  A field can be created in the form without it being in the destination table as long as "Submit to Table" is checked "No." This is useful for labels or navigational type fields where the field is not going to be entered into the table.

 

The creation page is similar to the current page with the following additions:

Field Name

This is the new field name. It must match a current column in the destination data table.

This inserts the new record into the driver table.

This cancels the creation of the new record.

This removes the record from the driver table. Values entered in the destination table will either be their default values (as defined in the destination table) or will be NULL if no default exists. Some data types, such as binary time stamps, cannot accept user data other than NULL and should be removed from the driver table.

The following attributes may be changed with the exception of FieldName:

Field Name

This is the name of the column as it appears in the destination table. It cannot be changed.

Q_Type

This is the data type of the record.  The data type determines how the data is handled in regards to how it is validated and sent to the destination table. It must be compatible with the destination table's data type. This should be changed with caution, since an incorrect type will cause errors on the page.

 

Text

The data is a string of length up to 4000 characters.  This will appear as a one line text box on the screen.

Text Area

The data is a string of length up to 4000 characters.  This is the similar to the "Text" Q_Type above, except for the larger text area that allows several lines of text to be shown at once.

DropDown - Text

The data is a string of length up to 4000 characters. The data is selected from a drop-down list of choices. The options for this list are determined by the LookUpSql.  

RadioButton, Vertical - Text

The data is a string of length up to 4000 characters. The data is selected from a radio button list of choices and will be displayed vertically on the screen. The options for this list are determined by the LookUpSql.  

RadioButton - Horizontal - Text

The data is a string of length up to 4000 characters. The data is selected from a radio button list of choices and will be displayed horizontally on the screen.  When designing forms, be aware that screen resolution can affect how your user sees a form.  For example, if your user has a smaller screen resolution than the screen used to design the form, it may not appear as intended,  having the user scroll to the right in order to see all of the options.  The options for this list are determined by the LookUpSql.  

Check Box - Text The data is of text type and can be in either an "on" (true) or "off" (false, null) state. Clicking on a check box changes its state from "on" to "off," or from "off" to "on."  The options for this list are determined by the LookUpSql.  

DateAndTime

The data is a date and time in the form of MM/DD/YYYY HH:MM:SS AM/PM. The time can be entered before the date but is always displayed after the date. AM/PM can be denoted after the time, or can be entered as Military Time. The time is always displayed as Military Time. The SQL column in the destination table can be either a datetime or a character based type.

DateOnly

The data is a date in the form of MM/DD/YYYY. The user can enter a time as well but this information is discarded. The SQL column in the destination table can be either a datetime or a character based type.

TimeOnly

The data is a time in the form of HH:MM:SS AM/PM. AM/PM can be denoted after the time, or can be entered as Military Time. The time is always displayed as Military Time. The SQL column in the destination table can be either a datetime or a character based type.

TimeStamp

The data is a binary time stamp. The user cannot enter a value for a time stamp, and SQL does not allow a non-NULL value to be entered into the destination table. It is recommended that TimeStamp records be removed by dropping the field.

Read Only - Text

This data is a text type that can be of any value.  Since this data type is read only, no changes can be made to the text shown.  For example, this can be used to display the status of an item.

Hidden - Text This data is of text type and can be used to hide fields that you do not wish the user to view.  This can be useful for fields that are not entered by the user but submitted to the table such as dates, times, IDs, or calculated fields.

Password - Text

This data is a text type and when used, dots will appear when you type rather than text.  This can be used to simply not show sensitive information on the screen when entering data.  You can also use this Q_Type to work as a password.  Note For this to work as a password, an underlying table (including usernames and IDs) must be created so that values entered in the form can be checked against the values in the destination table.  Once the username and ID have been verified, data entry can begin.

For example, if many people use a single account to access Web Data XPress, you may use the password Q_Type for the first field of a form to prevent unauthorized users from accessing your form.  Another option is to create a new form to be used specifically for password purposes.  Once the user has been granted access, they can branch to another form to begin entering their data.  Please refer to the Branching section below for more information.

Numeric

The data is an integer value. The data is entered via a text box. The control will not allow decimal values.

Float

The data is a decimal value. The data is entered via a text box.

DropDown - Numeric
 

The data is an integer or a decimal value.  The data is selected from a drop-down list of choices. The options for this list are determined by the LookUpSql.

RadioButton, Vertical - Numeric
 

The data is an integer or a decimal value.  The data is selected from a radio button list of choices and will be displayed vertically on the screen. The options for this list are determined by the LookUpSql.

RadioButton, Horizontal - Numeric
 

The data is an integer or a decimal value.  The data is selected from a radio button list of choices and will be displayed horizontally on the screen.  When designing forms, be aware that screen resolution can affect how your user sees a form.  For example, if your user has a smaller screen resolution than the screen used to design the form, it may not appear as intended, having the user scroll to the right in order to see all of the options.  The options for this list are determined by the LookUpSql.

Check Box - Numeric The data is of numeric type and can be in either an "on" (true) or "off" (false) state. Clicking on a check box changes its state from "on" to "off," or from "off" to "on." 

The options for this list are determined by the LookUpSql.

Slider The data is of numeric type and is useful for choosing a value within a range of values. To set a default value for the slider, simply add a value to the Default Value.  If there is no value, the cursor will not show.  Once the user clicks on the slider the cursor will appear.  If the user does not click on the slider, the value will be null.  To set custom values for the range and to add descriptive text, LookUpSql must be used.
Read Only - Numeric The data is an integer or decimal that can be of any value.  Since this data type is read only, no changes can be made to the number(s) shown.  For example, this can be used to display a calculation based on a previous answer.
Hidden - Numeric This data is of numeric type and can be used to hide fields that you do not wish the user to view.  This can be useful for fields that are not entered by the user but submitted to the table such as dates or calculated fields.

Password - Numeric

The data is of numeric type and when used, dots will appear when you type rather than text.  Possible uses for this type are similar to that of the Password - Text described above. 

Label

The is for display only.  The text entered in the Q_Text box is displayed and none of the other attributes are used.  This gives the option of having items such as instructions appear on the form.  When using this Q_Type, "Required" and "Submit to Table" should be checked "No" because typically there is no corresponding field in the destination table.

Image This is for display only.  This gives the option of having images appear on the form.  When using this Q_Type, "Required" and "Submit to Table" should be checked "No" because typically there is no corresponding field in the destination table.  The images are selected by the LookUpSql.

Q_Num

This is a float value that determines the order of the questions.  A negative value indicates that the record is a primary key in the destination table. 

 

Since Q_Num is a float value, questions can be numbered using decimals.  For example, suppose you have Q_Nums 1.111, 1.112, 2, and 3, and you specify "3" questions per screen, you will see questions 1.111, 1.112, 2 and 3.  This is because 1.111 and 1.112 is considered as one question.  But, if you have Q_Nums 1, 2, and 3, you will see question numbers 1, 2, and 3.

Required

This is set to "Yes" if the field cannot be NULL.

Submit to Table

This indicates that the field should be submitted to the destination table.  All Q_Types can be submitted to a destination table except for the "Label" and "Image" Q_Types since there is no data associated with them.  An example of when "Submit to Table" would be "No" would be for a navigational question or a password check question where the data from the user is not stored in any destination table.  The default value for this option is "Yes."

Branch

This option allows you to branch to other question(s) or form(s) based on a user's response.  For example, a form may have a question, "Do you Smoke?   Yes or No."   If the respondent answers "Yes," there may be further questions.  If the respondent answers "No," then the user may skip the smoking questions and continue with the remaining questions.  The default value for this option is "No."  For more information, please refer to the Branching section below.

*Q_Text

This is the Verbose (i.e., long) version of the question text as it will appear on the page.  The text that appears in this field can come from two different places, either the column description or the column name of your destination table.  If you would like your form to use column descriptions, you will need to ensure that the "GetColumnDescription" function is located in your destination database before the form is created.  The person creating the form will need dbo permissions for the destination table in order for this to work.  Please refer to the Important Information section of this document for more information.

*Q_TextShort

This is the shortened version of the question text as it will appear on the page.  If the Data Manager specifies "Verbose" = "No" when editing the form properties, this version of the question text is displayed.  When a new form is created, this value will default to the column name of the destination table This field should be as descriptive as possible since this is the value that is shown when a type validation is invoked.  For example, if you have a required field that is left blank or a data type that is incorrect, then this value will be used in the validation text to help you identify errors.

DefaultValue

This is the default value of the question.  If there is a default value in the destination table, it will automatically be placed  here when the form is created.  It can be simple text or any valid SQL command, such as current_user() or getdate(). The user must be careful to ensure that the data returned by a function (such as Current_User()) is compatible with the Q_Type used.

 

Dynamic Default

This is used for data that is linked to a previously entered value using using shorthand ([ShortName[FieldName]]) or values that are not entered by the user (i.e., calculated values or values that always stay the same).  This value will change if the previous value is changed and will not reflect what the user answers for that (dynamic) question.  The "{dynamic}" option must be placed at the beginning of the default value and enclosed in curly braces.  It is most useful with "Hidden" or "Read Only" Q_Types since it is dynamically linked to other values on the form.  Please refer to the Important Information section of this document for more information.

LookUpSql

This is a SQL SELECT statement that fills a radio button, drop-down, checkbox, image, and slider Q_Types with a list of options.  For the radio button and drop-down lists, the SELECT statement must return two values, the first being the data value, and the second being the descriptive text that you wish to display for that data value.  For checkBoxes and images, the SELECT statement should only return one record since a Checkbox or Image can be either one value or is null.  For the slider, the SELECT statement can return a range, descriptive text, or both range and descriptive text.   If the Q_Type is not a radio Button, drop-down, checkbox, image or slider then this statement is ignored.  For examples on how this feature can be used, please refer to the Important Information section of this document.

This control validates the LookUpSql statement.

ValidationRule

This is a SQL boolean operation that places a constraint on the data. The symbol "[%]" is used in place of the value to be checked.  For example, an integer that must be greater than zero but less than five will be represented as "[%] > 0 and [%] < 5."  Or if a date must be before today and after 1960, it can be represented as "[%] < getdate() and [%] > cast('1/1/1960' as datetime)." Implicit casting of data types is not supported, so constants (such as '1/1/1960') must be cast to the type they will be checked against.  For another example on how this feature can be used, please refer to the Important Information section of this document.

This item is ignored if empty.

The user can put a test value in the small text box and test the rule. An error will be returned if the rule is invalid or if the entered text does not abide by the rule.

*ValidationText

This is the error message that is displayed if the data entered does not abide by the validation rule. 

You may also apply formatting to the above items indicated with an asterisk (Q_Text, Q_TextShort, and ValidationText).  These formatting options include:  bold,  underline, italics, spaces, line breaks, as well as hyperlinks to other documents.  In addition, you can use the column descriptions that are defined in your destination table to appear as Q_Text on your form.  For more information, please refer to the Important Information section of this document.
 

Branching

There may be instances where you will want to branch to other question(s) or form(s) based on a user's response.  You may set up branching options during the form creation process or while editing a form. 

Depending on your needs, there are many different combinations that can be used when creating a branch.  You may branch to other questions on the same form (parent form), from a parent to a child form, and from a parent to a child and back to a parent form. 

To create a branch, first select the Q_Num from the drop-down list that you wish to create a branch on and select the "Yes" option.  The default value for this field is "No."  Once "Yes" is selected, an "Edit Branch" button will appear. 

Next, click on the "Edit Branch" button to edit or add branching options for your form.  If previous branch options exist, a table listing all of your options will appear.  Otherwise, if it is a new branch, Web Data XPress will indicate that there are no branch records set up for the field that you selected:

If you click on "New," a screen will appear allowing you to add rules for the Field Name that you selected. 

The Edit Branch page accepts the following parameters:

Option Number

This is the order in which the rules are checked and works similar to a SQL case statement.  Web Data XPress will check the rules in numeric order and once it finds a true condition, it will branch as directed by that option number.

Value Rule

This is a logical SQL statement that when true directs the branching and works similar to a SQL case statement.  The symbol "[%]" is used in place of the value to be checked.  For example, an integer that must be greater than zero but less than five will be represented as "[%] > 0 and [%] < 5."  Or if a date must be before today and after 1960, it can be represented as "[%] < getdate() and [%] > cast('1/1/1960' as datetime)." Implicit casting of data types is not supported, so constants (such as '1/1/1960') must be cast to the type they will be checked against.  

 

Web Data XPress will check the option numbers in numeric order and once it finds a true condition, it will branch to the destination as directed by that option number.  If none of the options are true and there is a {default} value rule specified, then the user will be returned to the next question on the form.  Otherwise, if none of the options are true and no {default} value rule is specified, then the progression will stop and send the user back to the Submit page of the form.

 

For example, below is a SQL case statement:

 

            CASE

  WHEN Value > 0 AND Value < 5 THEN  (Goto) --Next QNum--

 

  WHEN Value > 5 AND Value < 8 THEN (Goto) QNum10

 

  ELSE (Goto) --Next QNum--

            END

 

In Web Data XPress, the above would translate to the following:

 

Option #1:  [%] > 0 and [%] < 5        FieldName--->--Next QNum--

 

Option #2:  [%] > 5 and [%] < 8        FieldName--->QNum10

 

Option #3:  {default}                       FieldName--->--Next QNum--

 

First, Web Data XPress will evaluate Option #1.  If true, the user will be taken to the next question on the form.  If Option #1 is not true, then Option #2 will be evaluated.  If Option #2 is true, then the user will be taken to question number 10 on the form.  If Option #2 is not true, then Option #3 is evaluated.  Notice that Option #3 is a "catch all" rule, and if none of the rules above are true, then the user will be returned to the next question on the form.  If no {default} rule is specified, then the user will be taken to the Submit page of the form.

Destination Field

If you are branching within a form (parent), the destination field is the field where you are branching to.  If you are branching to another form (child), the destination field is the field in the parent form that you will return to once the branch form is complete.  Select from the drop-down list the field that you wish to branch to.

 

The Destination Field can only be a field on the parent form and must be a question number that is question further down your form (higher Q_Num).  If you want to return to the next question on the parent form, select "--Next QNum--" as your destination field.  If you want to return to the end of the parent form, then choose {--End Form--} as your destination field.  You may not choose a previous question as a Destination Field.

Form

Unless you are branching to another form, this should be left blank.  Select from the drop-down list the form name that you wish to branch to.  Only the forms in the current project will show up in the drop-down list.

This will return to the Edit Form page where additional changes can be made to your form.

This will commit your branch entry to a sub driver table associated with the form.

This button resets all values on the page.

This button cancels all editing of the current branch.

Example of Branching to Questions on the Same Form (Parent Form)
In the following example, we will branch to fields only within the parent form. 
In this case, the Option Number, Value Rule and Destination Fields will be used.  Since we are not branching to another form, the Form field will be left blank.  When you are finished adding your rules, click the "Submit" button to commit your branch entry.  For more information on the the parameters shown on this screen, please see above.

The screen below shows the branch options set for the Customer Survey form.  You will notice that there are two options defined, and we are branching to fields only within the parent form.  Web Data Xpress will evaluate Option #1 first.  If the value is greater than 0, then it will return the user to the next question number on the parent form.  If Option #1 is not true, then Web Data Xpress will evaluate Option #2.  Option #2 is the {default} option meaning that if none of the previous options are true, then the user will be taken to the end of the parent form {--End Form--}, skipping all questions not applicable to the user.  If neither option is true, the user will be directed to the form's Submit page.

Note:  You may edit or delete any of the branch records by clicking on the option that you wish to change.

If you wish to delete a branch, Web Data Xpress will ask if you want to delete the branch record specified before removing as shown below.

Example of Branching From a Parent to a Child Form
There may be times where you will want the user to complete the entire parent form, branch to a child form, then submit the form after the child form is complete.  In order to do this, you will need to create a branch on the last field of your parent form.  Below is an example of a branch option being set for a parent form (Customer Survey Example) going to a child form (Authors Example).  Notice that the Value Rule for this field is {default} because in this case, we will always branch to the child form after the parent form is complete.  Also, the "Authors Example" Form has been selected on the drop-down list instructing Web Data XPress to branch to that specific form.  The destination field is set to "{--End Form--}" meaning that Web Data Xpress will return to the end of the parent form when the child form is complete.

Example of Branching From a Parent to a Child and Back to a Parent Form
There may also be instances where you will want to branch from a parent form to a child form and back again to a parent form but not to the next question, rather a question further down on your form.  The example below is similar to above, except you will need to select a question further down on the form:


IV. Data Entry

Selecting a Form

The first thing you will see on Web Data Xpress is a drop-down list of active form titles and a control marked "Go."  You will only see forms that you have permissions to use. If the form does not appear, you may not have permissions for the project it belongs to, or the form is not currently "Active."  For more information on activating a form, please refer to Setting Form Properties above.

Select a form to enter data into by choosing one from the drop-down list and clicking on "Go." This will take you to the Primary Keys page.

Setting Primary Keys

The Primary Keys page is where you can create or start a new record. At the top of the page is the title of the form, as well as a list of controls for entering the primary key values. The controls you see are dependant on which form you have selected, and are typically text boxes, radio buttons and drop-down lists.  For example:

For information on the buttons that appear on the bottom of the screen, please refer to the table below.

Creating a New Record

Enter the new primary key information in the controls provided and click "Next." You will be taken to the Data Entry pages. Any default values for the form will already be filled in.

Editing an Existing Record

Enter the primary key information of an existing record and click "Next."  Web Data Xpress will first pull any existing data for the record from the temporary hold table.  If no data exists in the hold table, it will then pull any existing data from the destination table.  If no data exists in the hold table or destination table, Web Data XPress will create a new record.

Next, you will be taken to the Data Entry pages with the existing record's current values.  Note:  You cannot change any primary key information once the record exists in the destination table. If a primary key value is incorrect, you will have to contact a Database Owner (DBO).  However, if the wrong primary key information is entered during data entry, the user may simply cancel the record and start over as long as the record doesn't already exist in the destination table.

Only one user can edit the same record at any one time. If you get an error stating that another user is editing that record, that user must either cancel or submit their changes. If problems persist, contact your DBO to remove entries in temporary hold table (tbl_DataHold) pertaining to that record.

This is an example of a one page form after the primary key(s) have been entered.  Notice that the the record's current values are shown. 

Entering Data

After entering the primary keys for the record, you will be taken to a series of screens to enter data into.   The look of the form is dependant on the driver table for the form. You will see a series of web controls such as text boxes, radio buttons, or drop-down lists.  If the form does not appear as you wish, see the Editing Forms section.

Once the record has been created, any default values will already be filled in.

Some text boxes can have formatting constraints placed on them. They will appear at the right of the text box and can be any of the following:

(hh:mm)

The value should be in the form of hours and minutes. You can enter either military time, or append the entry with an AM or PM specifier.

(mm/dd/yyyy)

The value should be in the form of a date with a four digit year.

(mm/dd/yyyy hh:mm)

Both date and time can be added, with the time following the date. You can enter either military time, or append the entry with an AM or PM specifier.

In addition to the form controls, the following buttons appear at the bottom of the page:

This button takes you to the next page and validates your responses. If you have an invalid entry, you cannot advance to the next page. If the data is valid, the page will be saved to the temporary hold table.

These buttons take you back to the previous page and validate your responses. If you have an invalid entry, you cannot advance to the previous page. If the data is valid, the page will be saved to the temporary hold table.

This button removes all responses on the current page.

This button cancels editing. All of your changes will be lost. Once a record is canceled, you will be redirected back to the Primary Keys page where values will be filled in with those of the canceled record.

This button deletes the current record in the destination table and in the temporary hold table.  This button will only appear once the primary keys have been entered.  Anyone without permissions to delete data in the destination table will receive an error and will need to contact their DBO to have the record removed.

This button will take you to the last page of the form so that your data can be submitted.  If the data on the page is invalid, you cannot advance to the end of the form until the data has been corrected.

This button only appears on the last page. This will commit your changes to the database. Until the user submits the data, no changes will be made to the database.  Once a record is submitted, you will be redirected back to the Primary Keys page where values will be filled in with those of the previous record.

This button will be visible only if this option was selected during form set-up.  If the user clicks the "View Table" button before the primary key(s) are entered, the entire table will be visible.  If the "View Table" button is clicked after the primary key(s) are entered, the records shown will be filtered based on the primary keys entered.

Note:  During data entry, do not use the "Back" and "Forward" controls on your browser.  Doing so may cause unwanted errors.  Instead, use the Web Data XPress  "Previous," "Back," and "Next" controls.

Page numbers for a form may also appear at the bottom of the screen.  The user can navigate to a specific page by clicking on one of these numbers.  The current page will also be checked for invalid values.  If the current page has invalid values, the user will be unable to navigate to another page until those values are fixed.

If a form is not submitted during data entry (either the user closes the form or the user stops entering data and the session times out), they can still pick up where they left off by entering the same primary key information on the Primary Keys page as long as the user returns within one week. The values previously entered will still be in the temporary hold table.  

Canceling or Deleting Records

If a user clicks "Cancel," all values are removed from the temporary hold table and no changes will be committed to the database.  A user should only click "Cancel," if the data being entered should not be saved.  If the user clicks "Delete Record" during data entry, then the current record is removed from the destination table as well as in the temporary hold table in the SQL database.  Users who do not have permission to delete data from tables will need to contact their DBO to perform this operation.
Important!  Data entered will not be deleted unless you click the “Delete Record” or “Cancel” buttons.

Note:  After one week, data in the temporary hold table will be removed and placed in an archive table that is stored indefinitely.  To retrieve data that is stored in the archive table, please contact the WPIC Office of Academic Computing.


V. Important Information
 

Designing Forms:  Best Practices

When designing forms it is best to utilize Web Data XPress' check boxes, drop-downs and radio buttons.  These options are more user friendly giving the user a limited number of responses resulting in less errors.  It will also limit any confusion as to the type of answer that is expected.  Unless there is a field for comments or items such as names, fields that limit the number of responses are generally best.

Also, the person designing the form needs to be aware that the resolution set on their screen may differ from that of the user, thus affecting how the form is displayed.
 

Using “Shorthand” to Obtain Previously Entered Form Values

[ShortName[FieldName]] is shorthand for variables where the data has already been entered into the form. [ShortName] is the Short Name of the desired form and [FieldName] is the field that you wish to pull data from within the form.  Shorthand can be used in any of the fields on the Edit Form page as well as in the value rule of a branch record.  Please keep in mind that the page containing the shorthand notation must be on a different page than the value that it is referencing since this value comes from the temporarily stored data for the form and data on the same page will not have been stored.
 

Using Default and Dynamic Default Values

Default Value
When specifying default values in your forms, please be sure that valid SQL statements are used and that any text will need to be in single quotes (' ').  The example below illustrates a form field that uses 'Yes' as the default value.

Dynamic Default Value

Dynamic Default is used for data that is linked to a previously entered value using [ShortName[FieldName]] or values that are not entered by the user (i.e., calculated values or values that always stay the same).  This value will change if the previous value is changed and will not reflect what the user answers for that (dynamic) question.  Since it is dynamically linked to other values on the form, it is most useful with Hidden or Read Only Q_Types.  The {dynamic} option must be placed at the beginning of the default value and enclosed in curly braces.  The example below illustrates a form field using a numeric field as the dynamic default value.

The result is shown below.  This is a Read Only Q_Type that displays the store ID chosen by the user.

Note:  If you have records in your destination table with existing values and would like these values to be replaced with the default value as defined in Web Data Xpress, you must either use {dynamic} in front of your default value or be sure that "Submit to Table" is "No."  Otherwise, Web Data XPress will use the values that are already in your destination table for your records.
 

Formatting Q_Text, Q_TextShort, and ValidationText

During form creation or while editing forms, you can apply several formatting options to your Q_Text, Q_TextShort, and ValidationText.  These formatting options include:  bold, underline, italics, spaces, line breaks, as well as hyperlinks.

Bold Text
To turn ON bold, insert [_B_] at the point where you would like bold text to begin.
To turn OFF bold, insert [_/B_] at the point where you would like bold text to end.

Underline
To
turn ON underline, insert [_U_] at the point where you would like underlining to begin.
To turn OFF underline, insert [_/U_] at the point where you would like underlining to end.

Italics
To
turn ON italics, insert [_I_] at the point where you would like italics to begin.
To turn OFF italics, insert [_/I_] at the point where you would like italics to end.

Spaces
Use &nbsp; to add leading or trailing spaces.

Carriage Returns (i.e., line breaks)
For carriage returns, insert [_BR_] at the point where you would like the carriage return to occur.

Hyperlinks
[_a href="http://www.PutYourLinkHere" target="_blank">Link Description[_/a_]
The document or website to link to is inserted in the quotes above.  The target="_blank" should be used so that your link will open in a new browser window.  This will avoid problems staying within the Web Data XPress application. 
Hyperlinks to websites or documents (such as a .pdf file) are useful if you would like users to view other information while completing a form.

Below is an example of bold, underline, italics, spaces, carriage returns, as well as hyperlinks being applied to Q_Text.

The result is shown below.


Using Column Descriptions as Q_Text

As stated above, in order to have column descriptions from your destination table appear as Q_Text on a form, you must have the "GetColumnDescription" function in your destination database.  In order for this to work, you must do this before any forms are created.  Also, "public" will need select permissions for this function.

If column descriptions are not set in the destination table, the form will use column names for the fields when the form is created. 

GetColumnDescription
CREATE FUNCTION dbo.GetColumnDescription (@U as varchar(500), @T as varchar(500))
RETURNS table AS
RETURN (SELECT objname as VariableName, cast(value as varchar(2000)) as DescriptionText FROM ::fn_listextendedproperty ('MS_Description', 'user', @U, 'table', @T, 'column', default)
 

Form Examples

Branching

This example illustrates four things that can be done with forms that are used in branching.

Using an "ActiveHidden" Form Status for Child Forms
There may be times when you will want your users to begin with a particular form (parent) and then branch to another form(s) if certain criteria are met (as defined in your branching options).  Thus, you will not want your child form to appear in the list of available forms in the drop-down list on the Data Entry page.  By changing the status of your form to an "ActiveHidden" status, this will ensure that your child form is "hidden" and will only be available if the conditions defined in your branch options are met.

Using "Key Labels" to Hide Primary Keys on Child Forms
Users also have the option of displaying "Key Labels" on their forms.  When branching to a child form, you may not want primary key information displayed under the form title.  If that is the case, you will want to leave the default setting as "No" ensuring that only relevant information is displayed to the user.

Changing the form status and key label option is done on the Form Properties page as shown on the BRANCH2 EXAMPLE (child) form below.

Using a Hidden Field to Pass Primary Key Values to Child Forms
You may also want to pass values (such as primary keys) to a form that your are branching to.  This will ensure that the user doesn't select a different primary key resulting in fewer errors.  This can be done by using a Hidden or Read Only Q_Type.  In the BRANCH2 EXAMPLE form below, notice that the Q_Type is "Hidden - Text" and the DefaultValue of au_id, is set as a {dynamic} default.  This means that the au_id value from the parent form (BRANCH EXAMPLE) will be the default value of the child form (BRANCH2 EXAMPLE).

Notice that the data entry screen below does not show the Key Label under the form title.  Also, the primary key au_id is passed to the BRANCH2 EXAMPLE form and is hidden from the user.

Using Shorthand in a Value Rule of a Branch Option
Shorthand can be used in the Value Rule of a branch record to pull previously entered data.  In the BRANCH EXAMPLE (parent) form below, option #1 illustrates the use of shorthand in a branch record.  If the gender is male (males are represented by the number 0) and if the fruit selected in the previous question was an apple (apples are represented by the number 0), then the user will be taken to the BRANCH2 EXAMPLE (child) form.  Therefore, the only time a user will branch to the child form is when the gender is male and the fruit selected is an apple.  Any other combinations will result in the user being directed to the "FruitsPerDay" destination field on the BRANCH EXAMPLE form as shown in Option #2 below.


Combining Data Using Checkboxes

This example illustrates how you can combine several checkbox fields into one field to be sent to the destination table. 

Some questions on a form may require the selection of multiple answers.  To make it appear as if the user has the option of selecting multiple answers, a separate question can be created for each checkbox.  Then, by adjusting the "Questions Per Screen" or Q_Nums, have them appear on a single page.  A Hidden or Read Only Q_Type can then be used to concatenate the data fields from the individual checkboxes to be sent to the destination table.  For information on how to populate a checkbox, please refer to the LookUpSQL section below.

 

To combine all values from checkboxes into one field, you will need to set up a combined field.  "{dynamic}" should be placed in front of statement, ensuring that the value in this field will change automatically if any of the fields referenced in the statement changes.  This field must be on a different page than the checkbox fields.  This can be done by adjusting the "Questions Per Screen" or Q_Num settings for the "checkboxdata" field as indicated above.  Another option is to create a branch for the "checkboxdata" field.  This can be done by setting the Branch option to "Yes,"  forcing a page break so that the question appears on another page.

 

The example below illustrates how the combined field "checkboxdata" is set up. This field concatenates checkbox1, checkbox2, and checkbox3 and puts them into a read only field to be submitted to the destination table. 

 

 

The screen below indicates that the user has selected checkbox1, checkbox2, and checkbox3.

 

 

The result of the combined "checkboxdata" field is shown below.  Note that all three values selected above are in this combined field which will be sent to the destination table.

 

 

Data Validation for Dates

Validation Rules can be set up for form fields during form creation or while editing a form.

To create a simple validation rule for a date, the following fields on your form can be populated similar to the following example.  To test your rule, enter a date in the "Test Validation Rule" box.  "Okay" will appear if your rule is valid.

If an invalid date is entered, you will get an error message as indicated below:

LookUpSQL

The only way to populate a Radio Button, Drop-Down, CheckBox, Image or Slider question on your form, is to provide a SQL SELECT statement using the LookUpSql attribute.  This can be done either during form creation or while editing a form.  Note:  In order to use LookUpSql, your Q_Type must be a Drop-Down or Radio Button List, Checkbox, Image or Slider; otherwise this statement is ignored. 

The following examples will show how to populate these question types using LookUpSQL:

Populate Radio Button and Drop Down
To populate
radio button and drop-down lists, your SELECT statement must return two values, the first being the data value, and the second being the descriptive text that you wish to display for that data value.  Also, please ensure that the Q_Type is either a drop-down or radio button list.

After you enter your SELECT statement, click on the "Test LookUpSQL" control.  If your statement is valid, the message "Valid SQL Lookup" will appear. 

The screen below shows the items appearing in a drop-down list.

Populate Radio Button and Drop Down Based on Value of a Previous Question
Shorthand can be used in the LookUpSQL to obtain values from a previous question to be used later in the form.  In the example below, we want question number 5 (Q_Num 5) to determine the choices given for the upcoming question 6.1 (Q_Num 6.1).  Note:  The question (Q_Num - 6.1) must be on a different page than the value that it is referencing (Q_Num 5).  This can be accomplished by adjusting the "Questions Per Screen" or Q_Nums; otherwise this will not work.  Another option is to create a branch for question number 5.  This can be done by setting the Branch option to "Yes,"  forcing a page break so that question 6.1 appears on another page.

Your SELECT statement will need to include '[ShortName[FieldName]]' from your form.  Also, please ensure that the Q_Type is either a drop-down or radio button list.   After you enter your SELECT statement, click on the "Test LookUpSQL" control.  If your statement is valid, the message "Valid SQL Lookup" will appear. 

This is shown on the screen below.

The following two screens show the questions as they appear on the form using the "Radio Button, Horizontal - Text" Q_Type.  For question 5, if the user selects psychology as the type of books that they enjoy, then the choices they are given in question 6.1 will be only the psychology type.

This is Q_Num 5.

This is Q_Num 6.1.

When designing your forms with radio button lists, especially using the horizontal orientation, be aware that the text may not line up as intended if the answers are not all of the same length (see above).   Also, radio buttons will not wrap to the next line.  Thus, if there are many answers to choose from, the user will have to scroll over.  Below is an example of how Q_Num 6.1 appears with the "Radio Button, Vertical - Text" Q_Type:

Populate CheckBox
To populate a
checkbox, your SELECT statement should return only one record since the checkbox is either one value or null.  Also, please ensure that the Q_Type is a checkbox.

After you enter your SELECT statement, click on the "Test LookUpSQL" control.  If your statement is valid, the message "Valid SQL Lookup" will appear.  Below are all examples of valid LookUpSQL statements that can be used when populating a checkbox on your form.

This is checkbox1.  An "A" will appear in your checkbox as a choice.

This is checkbox2.  The last name (au_lname) that is associated with the chosen primary key (au_id) in the "exampleauthors" table (in the pubs database) will appear as a choice.

This is checkbox4.  The discount value that is in the first row of the "discounts" table (in the pubs database) will appear as a choice.

The following two screens show the checkboxes as they appear on the form.


Populate CheckBox Based on Value of a Previous Question
Shorthand can be used in the LookUpSQL to obtain values from a previous question to be used later in the form.  Note:  This checkbox field must be on a different page than the field that it is referencing.  This can be done by adjusting the "Questions Per Screen" or Q_Num settings for the "checkbox3" field.  Another option is to create a branch for the "checkbox3" field.  This can be done by setting the Branch option to "Yes,"  forcing a page break so that the question appears on another page.

Your SELECT statement will need to include '[ShortName[FieldName]]' from your form.  Also, please ensure that the Q_Type is a checkbox.  After you enter your SELECT statement, click on the "Test LookUpSQL" control.  If your statement is valid, the message "Valid SQL Lookup" will appear.   This is shown on the screen below.

This is checkbox3.  The primary key (au_id) that was chosen at the beginning of your form will appears as a choice.

The following screen shows checkbox3 as it appears on the form.

Adding Images
To add an image to your form, your SELECT statement should return only one record since the image is either one value or null.  Also, please ensure that the Q_Type is an image.

After you enter your SELECT statement, click on the "Test LookUpSQL" control.  If your statement is valid, the message "Valid SQL Lookup" will appear. 

The screen below shows the logo for the selected ID as it appears on the form.

Setting Slider Values and Adding Descriptive Text
If you do not wish to use the default minimum and maximum values (0 to 100) of the slider, the LookUpSQL can be used to set custom values for the slider.  However, this is not necessary for the slider to work. 

For sliders, your SELECT statement can return a range, descriptive text, or both a range and descriptive text. The format of the SELECT statement is shown below.

Select range=MINVALUE,MAXVALUE,text=’MINTEXT[,]MAXTEXT’

The "range" is defined as the values that can be submitted.  The "text" is the description that the user will see under the slider.  Text for the slider can be defined without a range and a range can be defined without text.   As indicated above, to set a default starting value for the slider, simply add a value to the default value.  If you do not set the default value, the start value will be null and the cursor will not appear.  Also, please ensure that the Q_Type is a slider.

After you enter your SELECT statement, click on the "Test LookUpSQL" control.  If your statement is valid, the message "Valid SQL Lookup" will appear.   This is shown on the screen below:

After changes to your form have been saved, the "slider2" field will appear on your form as shown on the screen below.  Notice that the slider's default value is "20" and the range and description are under the slider.  To enter a value for the slider, simply click anywhere on the slider.  Once activated, move the slider to the desired location and then click the slider again to set the value.


VI. Glossary

Accounts Management Page

This page allows a Supervisor to add and delete users from their projects.

Create Form Page

This page allows a Data Manager or Supervisor to create a form and may generate a new driver table if it does not use an existing one.

Data Entry Pages

This page allows users to add and edit data of a selected form. Responses are sent to the database after the user selects "Submit."

Data Manager

A user who can enter and edit data in a form.  This user also has the ability to add and drop forms in a project.

Edit Form Page

This page allows a Data Manager or Supervisor to edit form questions and their attributes.

Forms Management Page

This page allows a Data Manager or Supervisor to view the status of all forms in the database.

Form Properties Page

This page allows a Data Manager or Supervisor to edit most default properties of a form, drop a form, activate a form for general use, and specify the data table of the form.

General User

A user who can enter and edit data in all the forms of a project but has no other database privileges.

Primary Keys Page

This page allows users to enter primary key information before entering data into a form.

Supervisor

A user who has all the permissions of a Data Manager.  This user also has the ability to create user accounts for their projects.


 


VII. Support

If after reviewing this documentation and help is still needed, please contact the WPIC Office of Academic Computing.