This document describes the steps to configure Versago forms using the latest version form configuration tool, Forms (v2). The reader will find it helpful to read this documentation in its entirety as the configuration for v2 forms is considerably different from v1 forms provided in previous versions of Versago.

This document is intended for Versago administrators. End-users do not have access to this function.

The following documents contain additional information that may be helpful with understanding these processes.

• Database Connections

• Unless otherwise noted, “forms” refers to Forms (v2).
• Drag and drop refers to the standard Windows process of the same name. Select an item and hold down the left mouse button. While holding down the button, “drag” (move) the item to the new position. Release the button to “drop” the item in the new positions.

• Forms require a database connection to allow captured information to be saved. A connection must be defined before beginning the form creation process.
• Many forms will use Versago reports to look up information to populate fields. If those report requirements have been identified, the reports should be created in advance of developing the form. However, reports can also be created later and added to the report as needed.

• Click on the Admin link in the profile section of the Versago home page to access the Administration console.
• Forms (v2) configuration is found under the Dynamic Content category of the menu.

Forms (v2) is the second generation of Versago forms. Forms are used for user-entered data capture in the Versago environment.

v2 forms are not interchangeable with v1 forms. While some of the initial configuration process is similar, the underlying design functionality is quite different. When working with forms be sure to select the correct version of the configuration tool in the Administration menu.

The Forms maintenance tool provides two functions: creation of new forms and maintenance of existing forms.

• A listing of existing forms is displayed when the function is opened.
• Click the [Add New Form] button to create a new form. Detailed information is found here.
• Click the link for the menu in the Form Name column to edit an existing form.
• Click the [Delete] button (the X) to remove a form.
  • When a form is deleted in the Admin Console, its references in Versago are removed as are associated SQL Stored Procedures in the database. However, the associated data table(s) are left in place. If these tables are no longer needed they must be removed manually.
• Click the [Clone Form] button to create a copy of the current form. The cloning process is discussed here.

Form Layout is the process of positioning fields for optimal end-user interaction. This process in v2 Forms is quite different than in previous versions. The form layout process is described here.

A form layout consists of rows as described above. Each row can consist of one to twelve columns. Form input controls are then placed (drag and drop) in the columns. The controls provide the mechanisms for data input presented to the end user. Form controls have various properties that define their presentation and behavior.

Control Types are discussed here.

Control Properties are discussed here.

Standard Buttons are discussed here.

The process of managing controls is discussed here.

Creating database tables using Versago is described in the following sections. However, keep in mind one key caveat:

Database tables created using the Versago application use the “Quick Entry Form” function. This function is automatically displayed when the “Create New Table” option is selected as the Data Source for a new form.

Both Standard and Header/Detail forms will have a “header” table.

A table name is automatically generated from the form name by adding underscores in place of spaces. The table can be renamed at this point if desired. See the Detail Table section below for additional information when the form type is Header/Detail.

Data columns are added using the [+Add Field] button to open a new line in the grid.

1. FieldName is the column name used in the database, not what appears to the user on the form
   1. The name can use letters, numbers, and the underscore (_) character. Spaces and other special characters are not permitted.
2. Data Type is selected from the drop-down option list.
   1. Use of the Binary option is not supported in Versago Forms (v2) and should not be used.
   2. Date is always created in the database as a DateTime data type.
   3. The data type for Number is set based on the Format (below).
3. Format is used for data types of Date and Numeric.
   1. Date data type
      1. The Date data type is always created in the database as a DateTime data type, irrespective of format.
   2. Number data type
      1. Format of Integer is always created in the database as an Integer (whole number) data type.
      2. Format of Decimal is always created in the database as Decimal data type.
   3. Text data type
      1. Text format is created as nvarchar data type.
      2. Text(max) is created as nvarchar(max) data type.
4. Size is the number of characters of the column in the database.
   1. For Text fields this is the maximum number of characters that can be entered.
   2. The size can be changed for Numeric/Decimal but leaving the default is recommended.
5. Control Type indicates the type of control that will be assigned by default when this column is used in the form. It can be changed here, or in the form layout process.
6. Display Order only applies to Forms (v1) and can be disregarded.
7. Primary Field is the unique value that will identify each new record.
   1. The primary field must be numeric/integer.
   2. Only one primary field should be identified.
8. Precision indicates the number of decimals for Numeric/Decimal columns.
   1. All decimal columns are created with six (6) decimal points. The precision value is only used as a starting point in the form layout process when a control for this column is added.
      
      The values in the following image are typical basic columns for a Versago form. The names are not significant, but the data captured is relevant. Names for all other data can be created as desired. However, standard SQL Server naming restrictions will apply.
      

A detail table is only used when the form type is Header/Detail. The definition process is the same as shown for the header table. The only differences are the table name and the definition of the “key” column.

As noted in the Header section, a table name is automatically derived from the form name for the header table. The table name for the detail table must be entered manually. A good way to do this is to name the header table with “_Hdr” at the end. Then use the same name for the detail table except with “_Dtl” at the end. So, you might have tables “Demo_Form_Hdr” and “Demo_Form_Dtl”.

The detail table must have a ‘key” value that links back to the header table. This is named the “Linked Field.” The Linked Field must be the same name, data type, and format and the Primary Key in the header table.

The following images illustrate this setup.

In the detail table the column that links to the key value in the header is named “Linked Field.”

In some cases, you may want to create a table, or tables, directly in SQL. There is a specific way this needs to be done. The basic SQL statement are shown below.

Header Table

CREATE TABLE [dbo].[TestTable](
 
[vgoRecNum] [INT] NOT NULL,
 
[vgoCreatedBy] [nvarchar](50) NULL,
 
[vgoCreateDate] [datetime] NULL,
 
[vgoCreatedById] [INT] NULL,
 
-- Your columns here. Comma after the last column definition
 
[Your_Last_Column] [Your_DataType],
 
PRIMARY KEY CLUSTERED
 
(
 
[vgoRecNum] ASC
 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
) ON [PRIMARY]

Detail Table

CREATE TABLE [dbo].[Test_Table_Dtl](
 
[vgoRecNum] [INT] NULL,
 
[LineNum] [INT] NULL,
 
[ItemName] [nvarchar](100) NULL,
 
[ItemCode] [nvarchar](50) NULL,
 
-- Your columns here. **No comma after the last column definition**
 
[Your_Last_Column] [Your_DataType]
 
) ON [PRIMARY] 

To use these tables, select the “Existing Table” option for the Data Source. The application will determine the primary key value for the header table based on the table structure. You will need to identify the linked key value for the detail table as described in the “Create New Table” section above.

Lookups (also called Lookup List) are used when the list of values is extensive and is prone to change on a regular basis. An example where a lookup might be used is to show a list of customers. When a lookup list is used, the user can only select from these values, so consistency of input data is ensured. A field with a lookup list displays a “filter” indicator at the right end of the field.

Lookups use Versago reports as the data source. The report must exist before configuring the lookup list. See the “Versago Reports – Developer’s Guide” document for information on creating a Versago report. There are some basic rules for reports used for lookups.

• The report should include not only the value that will be selected by the user, but any other values that may need to be captured (into the form) when the value is selected.
• The report should not include sub-reports, actions, or charts.
• The report may include User-Applied Filters. If this type of parameter is provided the user can type characters in the field and press <Tab> or the <Filter> icon. The report results are filtered using these characters reading from left to right. For example, if the user enters “De”, only records where the corresponding field starts with these characters is returned for selection.
• Parameters that are provided by a previously selected control may be used. See the “Examples” section of this document for how this might be used.
• User Profile Filters in the report as a filtering mechanism are acceptable.

1. On the “Control Settings” tab, select “Textbox” as the Control Type.
2. Select the report to be used from the Lookup Report menu
3. The [ …] ellipsis button to the right of the Lookup Report menu is used to define Input Fields (i.e. form controls (values) that can be used as input filters to the lookup report) and OutPut Fields (i.e. form controls that will receive their input value from fields in the user-selected record in the lookup report) . See the “Samples” section of this document for how these elements might be used.
   1. At least one Output field must be configured. This will be the control where the lookup is being applied and should be the value to be displayed to the user.
   2. Additional output fields may also be defined. These are typically other values associated with the lookup value. See the simple example that follows.
   3. Click [Submit] to save the values and return to the Control Properties screen.

In this example, we use a lookup to present a list of U.S. states. We want the user to select the state by name, but we want to capture both the name and the associated code.

In this example the value “Name” is placed in the Column (control) named “Lookup1” and the associated code is placed in the Column (control) named “Lookup2.”

Note that the controls that receive values do not need to be visible on the form. In this example the “Lookup2” is not displayed to the user even though the value is captured.

The configuration of a Dynamic DDL is more complicated than a Static DDL in that it uses a Versago report as the source for the list of values. It also allows values to be placed in other controls as needed.

There are two types of Dynamic DDLs. The first type is like a Static DDL in that it only saves the value that is displayed to the in the database. The second displays one value to the user but saves a different value to the database.

An example of a “one value” Dynamic DDL might be a list of states that come from a database table. In this case the two-character state code is displayed to the user and is saved to the database.

An example of a “two value” Dynamic DDL might again be a list of states. But in this case, we display the full state name to the user for selection while the two-character state code needs to be saved with the Versago record.

Here are some basic rules for reports used for DDLs.

• The report should include only one or two values, depending on the data to be saved. One value can used when the displayed value is also the value saved to the database. Two values are used when the displayed value and the saved value are different.
• The report should not include sub-reports, actions, or charts.
• The report should not include User-Applied Filters.
• Parameters that are provided by a previously selected control may be used.
• User Profiles as a filtering mechanism are acceptable.

For the following examples we will use the same report in the two different ways. The two columns in the report are Code (the two-character code) and Name (the full name).

1. On the “Control Settings” tab, select “Drop-downlist” as the Control Type.
2. Select “Dynamic” as the Lookup List type.
3. Use the “Lookup Report” DDL to select the desired source report.
4. Two new fields are displayed: “DataTextField” and “DataValueField.”
   1. DataTextField defines which source report field value is presented to the user for selection.
   2. DataValueField defines which source report field is saved to the database.
5. Select the same value for both fields as shown in the image above.

1. Follow steps 1 – 4 for the Single Value DDL described above.
2. Select the report value to be displayed to the user for “DataTextField.”
3. Select the report value to be saved to the database for “DataValueField.”

The [ …] button to the right of the Report selection drop-down can used to set up additional values to be captured and, if appropriate, the control(s) that provide input to any report parameters. This process is the same as described in Lookup Reports.

Custom form buttons are action buttons that are added to the form in the layout section. These are different from the standard Save, Save and Close, etc. buttons found on every form.

• Only one action can be assigned to a button.
• The actions on the Control Events tab are not used with buttons and should be ignored.
• Calls to web services (APIs) can only be used if call is either a GET or a POST transaction and any authentication information that is needed can be passed in the URI.
• There is currently no way to display information returned from a web service.
• Custom buttons cannot currently be used to call a Versago report.

1. Drag and drop a Button control to the desired position in the layout.
   1. Due to the way buttons are positioned it is recommended that the column for the button be no larger than two (2) units wide. Adjusting column widths is discussed earlier in this document.
2. The Control Settings dialog is displayed.
3. See the Configuring a Button section that follows to add functionality to the button.

Click the [Action] button to open the configuration dialog.

1. Enter an Action Name.
   1. This is for information only but should be descriptive of what the action does.
2. Select the Action Type.
   1. Select URL for a website.
   2. Select Service to call a web service such as Bizweaver.
3. Select Primary Action as the Action Trigger.
4. Click [Add/Update] button to capture these changes.
   1. If the action is new it is displayed at the top of the page.
5. Click [Save] to save the configuration changes.

1. Open the Control Properties for the button, then open the Actions.
2. Click the Action Name link in the upper section of the page.
   1. The action information is displayed.
3. Update the action information as needed.
4. Click [Add/Update] button to capture these changes.
5. Click [Save] to save the configuration changes.

1. Open the Control Properties for the button, then open the Actions.
2. Click the X to remove the action.

A custom button can be used to let a user initiate a Bizweaver workflow from a Versago form. See the Invoking a Bizweaver Workflow Using POST page for full information on how to structure the call to the Bizweaver web service.

There are four types of default values that can be defined. These are static, user information, date, and header-to-row.

A static default value is simply a value that is used to fill in a field. The value can be a text value or a number.

Static values can also be used with drop-down lists. For example, assume you have a drop-down list for “status” with the values “Open” and “Complete.” When a user creates a new record, you want the value in the status field to be set to “Open.” Set the default value for the control to “Open” and the field will be populated. Be sure that the value you choose as the default is one that is included in the list of static values.

“UserProfile” values are related to the current user. “Id” and “UserName” are typically captured to identify which user created the record.

• {UserProfile.$Id} – returns the internal user ID code (integer)
• {UserProfile.$UserName}- returns the email address (user name)
• {UserProfile.$FirstName} – returns the user’s first name
• {UserProfile.$LastName} – returns the user’s last name

• $GETDATE() - returns current date

This process can be used to use a value from a control in the header section as the default for a control in each new row in the grid (row) section.

• {formdata.$control_name} - where $control_name is the source control

• $control_name value is case sensitive.

Formulas can be used for a variety of purposes. Examples include calculating subtotals and totals, and doing date calculations. Details for creating formulas are found here.

Versago Custom procedures are SQL stored procedures that are executed every time a form action that interacts with the database occurs. This includes both Save (new and update) and Delete actions. Details of how to utilize these functions are found here.

Bizweaver is a companion product to Versago that is used for data management and integration. The processes that execute the work to be done are called workflows. A Bizweaver workflow can be initiated using a Versago form button action. This process uses a web service (API) provided by Bizweaver. The button can either be a standard form button (Save & Close, Save & New, Save, Delete) or a custom button.

A Bizweaver workflow is initiated using a POST transaction. Information on using this method is found here.

Information on using this method is found here.

Read-only forms can be used to present the form information in its original format while preventing the user from making any changes. The easiest way to create a read-only form is to use the Clone a Form function. Once the original form has been cloned the new form can be changed as necessary. To make the form read-only, select the “Read Only” checkbox on the Form Setup page.

Keep in mind that a read-only form is an individual entity, even if it was created by cloning another form. A form created by cloning will not reflect changes made to the form from which it was cloned.

Files can be associated with, or “attached,” to a Versago form. The contents of an attached file is stored in a database table named TWBS_VGO_FormAttachments. This table is automatically created in any database where tables for forms are located. The file is converted to binary data and cannot be read directly from the table.

There are four key columns that are used when referencing records in the table:

1. “ID” is unique numeric value assigned to each record in the table. The application sequentially assigns this number.
2. “ObjectID” is the number of the form to which the file is attached.
3. “RecordID” is the record number of the record to which the file is attached.
4. There may be multiple records for an “ObjectId” + “RecordID” combination since multiple files can be associated with a record within a form.

In the following example, there are five “attachment” records.

• ID records 1 -3 are associated with form (ObjectID) 10. Records 4 – 5 are assocated with form (ObjectID) 12.
• Record 1 (RecordID 1) of form 10 (ObjectID 10) has one “attachment,” ID 1.
• Record 2 (RecordID 2) of form 10 (ObjectID 10) has two “attachments,” IDs 2 & 3.
• Record 1 (RecordID 1) of form 12 (ObjectID 12) has two “attachments,” IDs 4 & 5

“Attachment” files can be converted back to their original form and stored on disk using Bizweaver. Information on this process can be found here.

Create a Basic Form

Create a Header/Detail Form