The Design Form panel supports the creation of "Forms" that allow for the editing of data found in a database table. Forms can be defined that:
|•||Edit existing data|
|•||Edit existing data, with delete capability|
Once a form is created and saved, in order to use it, it must be executed from a Menu or as a QLR Widget. Form widgets are created using Preview tools which is available when designing a form and viewing it in Preview mode. The Design Form panel supports the creation and previewing of a form and its associated search panel to define its layout and behaviors. Although previewing the Form displays live data, no data can be added, edited or deleted in Preview mode.
When a form is designed to Edit or Delete data, the Form Designer is used to design the Form and also the characteristics of the Search Portal, which is used to find the records to be edited or deleted.
Using the Form Designer, forms can be created that range from allowing a power user to select all records and edit all fields, to tightly limiting access to specific data that can be selected for deleting or editing. When a Delete Form is created, data fields are displayed as read only text. No data editing is permitted.
The Designer's User ID and password are associated with a form, so when the Form is accessed from a menu or a widget, it is accessed using the Designer's connection credentials. This allows for other users to use the Form to edit data in tables for which they don't have Insert, Update or Delete privileges. If the Designer's password changes, the Designer must login to QLR Manager, retrieve and save a single form. This will update the password associated with all forms belonging to the Designer's User ID.
Note: Edit and Delete forms can only be created for tables that contain at least one unique index, or where a set of columns can be selected to Define a unique identifier for each row of data in a table.
A form to edit data in a table can be created with just a few mouse clicks.
After clicking on the Design Form tab:
|1.||Choose a database / schema where the table resides.|
|2.||Select a table and click the Get Table button.|
|3.||Click either the Preview tab or Preview button. The default search panel will appear.|
|4.||Click the Search button. A 10 row Tabular form will appear in Preview mode.|
|5.||This Form can be saved on the Design Form panel. It can be accessed and run from the Menu panel by retrieving "My menu", the Form can be built into a custom menu using the Create Menu panel, or a QLR Widget can be created using Preview tools.|
Following is and example of a Simple Edit Form with the ability to Edit. Add, and Delete records:
Forms are associated with a database table. This requires that the Form Designer first select the database and/or schema where the desired table resides, and then choose the table from the table list. After selecting a table, click the Get Table button.
Tables must have at least one unique index to be used with a form. QLR Manager will perform this check when the table is retrieved. If no unique index is found, an error message will be presented and no form will be created.
The default behavior is to add all of the columns (up to the first 50) from the table into the Form Designer. If the intent is to create a form to edit a limited number of columns of data, it may be preferable to select the "None" radio button for the "Initially populate Form elements for selected Source table with" and manually add only those columns to be included in the Form. The Form Elements - Basic Formatting section supports the addition, deletion and rearranging of the data columns that are part of a form.
Forms can contain the following types of elements:
|Label and input field||Pairs associated with a column of data in the table being edited. The default Form layout of "Tabular" selected in the General Form Characteristics section will display the labels as column headings. When a "Free Style" layout is selected, the label positioning in relation to the input field can be defined by the Form Designer.|
|Label text only||Provides a means to add a custom label without an associated input element. It may be used as a heading or explananation preceding a group of related form elements or for whatever purpose the Form Designer deems appropriate. This selection is only available when "Free Style" is used as the Form layout.|
|Horizontal rule||Is provided to add some separation between groups of form elements. This selection is only available when "Free Style" is used as the Form layout.|
In order to locate the table to create a form, the Form Designer must first navigate to the desired database and/or schema. Changing the current selection will automatically update the table list.
Once the desired database/schema has been selected, the table upon which the Form is based must be selected. The table is retrieved by clicking the Get Table button. In order to create an Edit or Delete form for a table, the table must have at least one unique index defined, or the Form Designer must select a set of table columns that will uniquely identify each row of data in the table.
When a unique index for a table cannot be found, the Design Form panel will display a multiple select list that contains a listing of all the columns in the table. The Form Designer must select one or more columns whose values will uniquely identify each row of data in the table. Once these columns have been selected, clicking the Define button will cause QLR Manager to check the data in the table to determine if the selected columns uniquely identify all the existing rows of data. Unique identification is necessary to find the correct rows for updating and deleting information. When uniqueness cannot be determined, only an "Add" form can be created for the table.
When a table is first selected and before clicking the Get Table button, a decision should be made whether to start with the default of all columns in the table (up to 50) or no columns. Importing 50 columns from a large table will slow the web browser's performance. If the Form Designer intends to create a form with a limited number of columns of data, it would be more efficient to select "None" for the "Initially populate Form elements for selected Source table with" control. Regardless of the choice, table columns can be added, removed, and rearranged by using the controls found in the Form Elements - Basic Formatting section of the Form Designer.
A Master/Detail form combines two forms with related information. The Master Form will be displayed as a Free Style form and the Detail Form will be displayed as a Tabular form. An example may be a Purchase Order, where the main body of the Form contains general information about the Purchase Order, and the bottom of the Form contains details about each item being ordered.
In order to create such a form, the Detail Form is created first. Even if the Detail Form is defined as a Free Style form, it will be treated as a Tabular form when used in a Master/Detail Form. The Detail Form name selection list will be populated with all forms belonging to the User who is designing the Form. This option is for selecting the desired Form for use as the Detail Form.
Once the Detail Form has been selected, the Design panel will be repopulated with 5 pairs of selection lists that contain all of the fields in both the Master and the Detail tables. The Form Designer then selects the fields that define how the data in the two tables is related. Up to 5 field pairs can be defined for this relationship. An example could be that the Master table contains a field called PO_NUM and it is related to the PO_NUMBER field found in the Detail table.
The General Form Characteristics section allows the Form Designer to change common attributes that apply to the entire Form.
There are three basic form layouts that can be created:
|Tabular||Form is one that shows multiple records at one time in a table layout, similar to a spreadsheet. Each row is a different record from the table, and each column is a different field within the table. A 10 row Tabular form is the default format.|
|Free Style||Form works with one record of data at a time. When this layout style is selected, the Form defaults to display all data fields aligned vertically, preceded by the table column name.|
|Master/Detail||Form is one that combines the two previously mentioned form layouts. The top of the Form is the Master data and is presented as a Free Style layout. The bottom part of the Form is a Tabular layout of the Detail information. The data is linked together by specify how the two sets of data are related. A typical example of a Master/Detail Form is a Purchase Order. The Master information is general information about the Purchase Order. Each line item of purchase data is the Detail information. This type of form has two sets of buttons for moving between records. One set moves between different occurrences of the Master records, the other moves between the Detail records that are associated with the current Master record.|
Forms can be designed for various types of updates:
|Add||Form is intended to insert new records into a table. Data is entered into a form and then the "Add" button at the bottom of the screen is clicked to actually add the information. If the data being entered is repetitive in nature, including the Add - Provide a copy data option in the Form may be beneficial. If within the database definition itself, a table column has been defined with default values, QLR Manager will populate the Form with the default values.|
|Edit||orm is used to edit existing information. When designing a form for editing, the characteristics of the Search Portal, which is used to locate existing data, should also be defined.|
|Delete||Form is used to delete records from a table. When designing a form to perform deletes, the characteristics of the Search Portal, which is used to locate existing data, should also be defined. Prior to deleting data, the User is asked to confirm the delete request.|
When designing a form, it is only necessary to have database authority to SELECT data from the table for which the Form is being designed. However, when a form is used, the connection ID to the database is that of the Form Designer - the User ID that saved the Form. Therefore, a form with Add capability requires that the Form Designer has INSERT authority for the form table, forms with Edit capability requires UPDATE authority, and forms that can Delete records requires that the Form Designer has DELETE authority for the table.
Add and Edit Forms can be enhanced by applying additional options:
|Add - Provide a copy data option||After a record is added using a form, the User is presented with a blank form to add another record. When this option is selected, a checkbox is added to the bottom of the Form. If checked by the User, The Form presented after adding a record is repopulated with the data that was just added. This is useful when entering repetitive information where only a few data elements have changed.|
|Edit - Include Add capability||This allows the User to add a record when in Edit mode. A control is added to the bottom of the Form to either create a new record from scratch, or to copy existing data as the basis for the new record.|
|Edit - Allow for Delete||This option adds a delete checkbox to a form in edit mode. This allows the User to either edit or delete data from the same Form.|
A password can be assigned to a form which will prompt the User to supply the password prior to being granted access to the Form.
The optional validation query allows you to dynamically check if a form should be displayed to a user. It works similar to an optional password, but it allows you to query data in your database to validate the use of the form. If the query returns more than zero rows of data, then valid = TRUE. Zero rows returned is VALID = FALSE. A simple example would be to check that the currently connected user ID is found in the qlr_userid table:
select * from qlr.qlr_userid where userid='[qlr_userid]'
If the user ID is present, then the user can access the form. If not, then a message is displayed that says "Cannot validate information to access the Form."
It is also possible to use a validation query in conjunction with the qlr_hide_userid input control. This input control allows for the capture of the login user ID as a hidden form element to save the User ID associated with any change to a field in a form. However, if the form is accessed via a widget link, the login ID is always that of the form owner. In the validation query below, notice how the user ID field in the query is given the name of "qlr_hide_userid". When this is done, the data selected in the validation query becomes the login ID.
select userid as qlr_hide_userid from myuser_table where userid='[USER ID]' and password='[Password]'
In the above example, the User will be prompted to provide both a user ID and a password in order to access the form.
Another example of a validation query could be to check that there are rows of data related to the form.
select purchase_orderid from mydb.puchases_orders where po_date=curdate()
The General attributes allow the Form Designer to set the Form background and border colors, form width, inner border display, and how the Form will align in the browser window.
The "Specify form width" option is useful when a form contains multiple sections. The default size of each section depends upon the form elements included in the section, their width and positioning. This setting allows the Form Designer to set all sections to a uniform width. The width will be honored as long as a section's content is not wider than the provided width in pixels.
If inner borders are desired, checking "Display inner borders" will display a one pixel border around each cell in the Form. This is generally better suited for a Tabular form, but it can also be used for a Free Style form. This option is also useful when designing a Free Style form to see where form elements are being placed within the multiple columns in the Form.
The Form buttons controls allow the Form Designer to define the appearance of the buttons used in the Form. When the "Use default button style" checkbox is checked, the Browser's default button style is used.
These options are specific to a Tabular form layout. In addition to setting alternate row coloring, the number of rows of data to be displayed at any one time can be defined ranging from 1 to 200 rows. In general, the more columns of data that the Form is used to edit, the smaller the row setting should be to minimize page load time. In order to move between "page sets", the User can click the appropriate navigation buttons found above the Form. Unavailable options are grayed out.
For those columns in a tabular form that contain editable data, a "copy down" icon will be presented. If the User changes a value in a column, then clicks the copy down icon in that column's header, all the displayed rows below where the change was made will be updated with the same value.
Below is a working example to demonstrate this capability:
The Title text options allow the Form Designer to control the appearance of the Form title. The Form title appears above the Form and also above the Search Portal that is produced when an Edit or Delete Form is defined. The default value populated for the "Form title" is the name of the table selected for editing. The title can be edited or omitted by over-typing or clearing the value in the "Form title" field.
The Label text options control the appearance of the text labels that are associated with each of the data fields that are part of the Form. In addition to controlling the appearance, there are a few additional settings worthy of note.
The Required prefix will appear at the beginning of each label that is associated with a data element that has been defined as Input req'd. HTML tagging can be used in this field. For example, <span class="redtxt"><b>*</b><span> can be used to create a bold red asterisk prefix, such as *. Spacing can also be added by using the HTML entity of . When input is required for certain fields in the Search Portal, this same prefix will be used with those fields as well.
The Free Style suffix is added to end of all the field labels when a Free Style form layout is selected. HTML tagging can be used when defining the suffix. The suffix is not applied to the Labels when a Tabular layout is specified.
The default alignment of all the Labels is set by adjusting the Align and Vertical align settings. The alignment setting can be overridden at the individual form element level.
These options control the overall appearance of the input fields found in the Form, including the text color, size, font face and text decoration attributes. The Border style and Border widths that surround input elements can also be set. Note that how border settings are rendered can vary among different browsers.
The default alignment of all the input fields is set by adjusting the Align and Vertical align settings. The alignment setting can be overridden at the individual form element level.
Each element label in a form can have help text associated with it. There are two types of help. Tooltip help, which is visible when the User places the mouse pointer over the label text, and URL based help opened in a pop-up window when the label text is clicked. This set of options controls the color and text decoration (Bold, Underline, Italic) of the label text defined as links, and also the foreground/background colors of the tooltip help. More information about defining URL based links and tooltip help text is described in the Form Elements - Other Properties section.
Additional text can be added to the top of the Search Portal, and to the top and bottom of the Form. This optional text provides additional flexibility for the Form Designer to add instructions or convey any additional information. This text can contain HTML tagging. The font-size and font-family applied to this optional text is the same as defined for the Label text. These attributes can be easily overridden by applying different styles to a nested <div>. For example:
<div style="font-size:12px;font-family:sans-serif">optional text<div>
The Search Portal with optional text added:
This is an example of "Optional text to include above the Search Portal". It can be formatted with HTML tagging and will stretch across the entire page. The Form Designer can control the width by by enclosing the text inside a <div tag with style="color:#1D5084;text-align:left;width:400px">. In this case, the text color has been set to dark blue with some bold emphasis.
The Form with optional text added above and below:
This is an example of "Text or instructions to include above the Form". It can be formatted with HTML tagging and will stretch across the entire page. The Form Designer can control the width by enclosing the text inside a <div tag with style="color:#1D5084;text-align:left;width:400px">. In this case, the text color has been set to dark blue with some bold emphasis.
This is an example of "Text or instructions to include below the Form". It can be formatted with HTML tagging and will stretch across the entire page. The Form Designer can control the width by enclosing the text inside a <div tag with style="color:#1D5084;text-align:left;width:400px">. In this case, the text color has been set to dark blue with some bold emphasis.
This set of options allows the Form Designer to control how an "Add" form behaves when it is used in a QLR Widget. An example of a form used to collect user input could be a Send Feedback or Contact Us form.
When "Display message text" is selected, the defined message text, which can contain HTML tagging, is displayed after the Add form is successfully submitted and processed. This form can also contain replacement references to the columns found in the form, such as:
[first_name],<br /> Thank you for submitting your comments. We will contact you shortly.
The font-size and font-family applied to this message text is the same as defined for the Label text. These attributes can be easily overridden by applying different styles to a nested <div>. For example:
<div style="font-size:12px;font-family:sans-serif"> [first_name],<br /> Thank you for submitting your comments. We will contact you shortly. <div>
When the "Execute listed URL" is selected, QLR Manager will direct the User to the URL entered. Replacement references can also be used here and will be URL encoded, such as:
Or simply direct the User to a static thankyou.html page:
These behavior options only apply to a QLR Widget. When the same Add form is run from the Menu panel within QLR Manager, it will present another copy of the Add form after the Update button is clicked. When the form is a Tabular layout, data from the first record in the form is used as the replacement values. When a Master/Detail layout is used, only values from the Master record will be replaced. The reference to form field names is case sensitive and is dependent upon how the fields were defined when the database table was created. Generally, these will be lower case.
In order to test these behaviors, the Form Designer must first save the Form, then create the Widget from the Preview panel by launching Preview tools.
The Basic Formatting section is used to define which table columns (Available elements) will appear in a form, their order, and basic characteristics.
The list of available elements is composed of the columns found in the Form table. In addition, for a Free Style form, "Label text only" and "Horizontal rules" can be added to the Form. If these are added to a Free Style layout which is then changed to a Tabular layout, they are ignored when the Tabular layout is rendered.
The Column actions are used to manipulate the Available elements. Actions can be rolled back one at a time by clicking the Undo button.
|Add column||Works by first selecting an element from the Available elements list. Select the "Add column" Action, and then click the Apply button. The selected element will be added to the bottom of the existing form elements. A table column can only be added to a form once.|
|Insert before||Works similar to the "Add column" Action. First select an element from the Available elements list. Choose the "Insert before" Action, then select the Col # of an existing element and click the Apply button. The selected element will be added before the selected Col # element. A table column cannot be inserted if it is already used in the Form.|
|Replace||Works similar to the "Insert before" Action. First select an element from the Available elements list. Choose the "Replace" Action, then select the Col # of the existing element to be replaced. Clicking the Apply button will replace the selected Col # element with the selection from the Available elements.|
|Delete (Col #)||Will remove a form element. Select the "Delete" Action, choose the Col # to be deleted, then click the Apply button. This Action does not reference the Available elements list and explicitly deletes the selected Col #.|
To Move a column or element, left click on either the column number or name that you desire to move. Hold down the left mouse button and slide the entry up or down in the list. An entry can also be removed from the active list by dragging the entry back into the column selection drop down list.
The Col # refers the column number of an element in a form. It is used as a reference to apply many of the Column actions described above.
The Form element column is a list of elements that have been defined to be part of the Form. These are followed by an icon that indicates the type of data related to that form element. Mousing over the icon will provide additional information about the element.
Form elements can be grouped into separate expandable/collapsible "Sections". An example of the use of sections is the Design Form panel itself. It is divided into multiple sections, such as Database & Table Selections, General Form Characteristics, etc. Using multiple sections may be desirable when a form contains many form elements. It is also helpful when complicated Free Style form layouts are designed. A form is comprised of elements organized into HTML table columns. Each section is comprised of a separate HTML table. Grouping form elements into separate sections may help solve some layout issues.
The default section number for a form element is zero, which is the equivalent of no section. QLR Manager allows for elements to be placed into sections 0 to 5. Sections 1 to 5 can be given a title, and their behavior can be defined in the Section Title & Opening Behavior section of the Form Designer.
The Label alignment can be used to override the global Form level label alignment setting, which is set in the General Form Characteristics section. The default value is the global "Form" level setting.
The Label text is the text that is associated with an input field. The default value is the table column name.
When the Input required checkbox is checked, the field label in the Form will be prefixed with the Required prefix as defined in the General Form Characteristics section of the Form Designer. QLR Manager examines the table being edited for data fields where nulls are not allowed. These fields are automatically checked, and cannot be unchecked. If any fields in the Form require input, then the designated prefix, followed by the phrase "Input required", will appear at the bottom of the Form.
Upon submission of the Form to the server, QLR Manager will check to make sure that data is present in that field. If required data is missing, an error page is displayed stating which fields are missing input. Clicking the Return button on the error page will take the User back to the Form.
The Input alignment can be used to override the global Form level input alignment setting, which is set in the General Form Characteristics section. The default value is to use the "Form" level setting.
When QLR Manager creates an Add or Edit Form, the default data entry area is a simple text input field. This can be changed to use an Input Control. If the Form Designer wishes to use an input control that they authored and saved, it is only required to enter the Input Control name. If the use of an input control owned by another User ID is desired, the referenced control must be prefixed using the owner ID.control name format, such as jeff01.customer list. When the cursor is in one of the Optional input control fields, pressing the "Ctrl" key will present a small window that contains a list of input controls. This can be used to quickly reference and apply available controls:
There is also a set of special QLR internal input field types that can be entered as an Optional input control. These are listed in the Input Control pop-up as the first selections for the current logged on Owner:
|qlr_read_only||Will create "Read Only" data, which means the data will be
displayed in the Form as text, without the ability to alter it. When a Delete Form is created,
all input fields will be displayed as "Read Only".
When qlr_read_only or qlr_read_only_wrap are used, PHP formulas can be added to manipulate the appearance of the displayed data. The formula is entered after the qlr_read_only (or wrap) keyword, preceded by a blank space. The value of the form element is referenced by $value. Some examples:
Display the first 50 characters only: qlr_read_only_wrap $value=substr($value,0,50)
|qlr_read_only_wrap||Also presents "Read Only" data. The difference is that the
text will be word wrapped within the boundaries set by the
Input width setting for the field.
See the help for qlr_read_only above for information about using formulas to manipulate how data is displayed.
|qlr_numeric||Creates a text field that accepts numeric characters only, which are "-.0123456789". This can also be accomplished by creating a numeric only Input Control.|
|qlr_image_from_db||Allows an image for which the content is stored in the database to be added to a form. Image size arguments (height followed by width) can be provided after qlr_image_from_db as additional arguments. Entering qlr_image_from_db 100 120 will display the image as 100 pixels high and 120 pixels wide. If qlr_image_from_db 100 is specified, the 100 will be used for both dimensions and the image will be displayed as 100 pixels high by 100 pixels wide. If no size arguments are added, the image will be rendered as its native size.|
|qlr_image_from_file||Allows an image for which the content is a file to be added to a form. Image size arguments (height followed by width) can be provided after qlr_image_from_file as additional arguments. Entering qlr_image_from_file 100 120 will display the image as 100 pixels high and 120 pixels wide. If qlr_image_from_file 100 is specified, the 100 will be used for both dimensions and the image will be displayed as 100 pixels high by 100 pixels wide. Without the size arguments, the image will be rendered as its native size. If the field data does not contain the required path to locate the image, it can be added as a third argument surrounded by single quotes, such as qlr_image_from_file 100 120 '../../images/'. Note how the ending slash or backslash (depending on the server operating system) is part of the path reference.|
|qlr_upload_to_db||Creates a file upload control. This will upload file content from
the User's workstation to the field it is mapped to. The field must be defined
to be large enough to accept the anticipated data, or data truncation will occur.
Important: The number of bytes of data that can be uploaded are limited by two php.ini settings, those being "upload_max_filesize" and "post_max_size". upload_max_filesize limits the size of each individual file. post_max_size limits the entire amount of data uploaded from any given page. This means that Tabular form layouts which may contain multiple upload controls are more likely to truncate data uploads unless the php.ini file is updated accordingly.
When this control is used in a form type of Edit, the existing image will be displayed above the upload control. File uploads have been tested with Mysql Blob data types, the Oracle Blob data type using Oracle 10g, Postgres Bytea data type, and the MS SQL Server data type of Image.
|qlr_upload_to_file||Creates a file upload control. This will upload file content from
the User's workstation to a file of the same name on the server. A path argument can be added to
specify the path where the file should land, such as qlr_upload_to_file ../../images/.
It is not necessary to surround the path with quotes. The name of the uploaded file, without
the optional path, will be added to the field it is mapped to. When this is used in a form type of
Edit, the existing image will be displayed above the upload control.
Important: The number of bytes of data that can be uploaded are limited by two php.ini settings, those being "upload_max_filesize" and "post_max_size". upload_max_filesize limits the size of each individual file. post_max_size limits the entire amount of data uploaded from any given page. This means that Tabular form layouts which may contain multiple upload controls are more likely to truncate data uploads unless the php.ini file is updated accordingly.
|qlr_hide_date||Allows for a date field to be part of a form, but not visible in the Form. When the data record is added or updated, the current systems date, such as 2009-06-22, is entered into this field.|
|qlr_hide_datetime||Allows for a date-time field to be part of a form, but not visible in the Form. When the data record is added or updated, the current systems date plus time, such as 2009-06-22 09:34:11, is entered into this field.|
|qlr_hide_timestamp||Allows for a field that is used for a Unix timestamp to be part of a form, but not visible in the Form. When the data record is added or updated, the current timestamp, such as 1248459518, is entered into this field.|
|qlr_hide_ip||Allows for a field that is used for an ip address, such as varchar(15), to be part of a form, but not visible in the Form. When the data record is added or updated, the ip address of the connected User, such as 220.127.116.11, is entered into this field.|
|qlr_hide_userid||Allows for a field that is used for tracking a User ID, such as varchar(64), to be part of a form, but not visible in the Form. When the data record is added or updated, the User ID of the connected User, such as jeff01, is entered into this field.|
Notice how the above hidden fields can be added to tables to help track who was the last person to create or alter data.
If the name of the Input Control, or a special keyword cannot be found, the default value will be a simple text input field.
When QLR Manager creates a form, the default data entry area is a simple text input. The default size of that text box is determined by the field data type. A text field is the lesser of 16 or the field size as defined in the database. A numeric field type is 8 characters wide, and a Date/Time field type is 10 characters wide. This Input width control provides a means to override the default widths. The width value is an approximation of the number of characters that will be visible, and is used by QLR Manager to calculate the pixel width of the input fields. What is actually visible in the input field is dependent on the selected font face, font size and the characters themselves for variable width fonts (WWW takes up more pixel width than iii).
A table may contain important information where it is necessary to track changes to certain data fields within the table. QLR Manager supports this by allowing individual fields to be tracked in an audit table called qlr_form_audit_trail. When the Audit trail checkbox is checked for a given data element, the original value and the new data value are added to this table. The following data is recorded as part of the audit trail:
table_name varchar(64) not null, -- name of the associated table table_col varchar(64) not null, -- name of the table column form_name varchar(64) not null, -- form name form_owner varchar(24) not null, -- form owner host varchar(60) not null, -- database host dbspace varchar(64) not null, -- dbspace where associated table resides form_action varchar(24) not null, -- action performed on data(insert,update,delete) userid varchar(64) not null, -- userid performing the changes ip_address varchar(15) not null, -- user ip address change_date datetime not null, -- date and time of change trans_num smallint not null, -- transaction number for that batch update original_data text null, -- original data in the field new_data text null -- new data that replace original data
Any change to a data element from an Add, Edit or Delete Form are entered into this table. The data will reside in this table until manually removed by someone who has the authority to do so.
Multiple audit trail entries, all occurring from a single form, will have the same change_date tracked to the second. Oracle may require an update to the way dates are displayed in order to query the data in this table. An Example of adjusting for Oracle is: alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
The Other Properties options are intended to enhance the appearance and layout of Free Style forms. Tooltip or URL based pop-up help can also be defined for both Free Style and Tabular layouts.
Free Style forms are based upon an HTML table layout. Tables are comprised of columns and rows of table cells. The elements within a form are placed into these table cells. When the Label associated with an input field is positioned to the left of the Input field (default), the two elements take up two columns in the table. Since QLR Manager considers the Label and the Input field as a pair that comprise a form element, the two columns are considered a single (1) column set, or colset. In the example below, the form elements are all in colset 1, which actually occupy two columns in the table as illustrated with an "Inner border size" of 1 in the General Form Characteristics.
If the Free Style colset value for the First name field is set to 2, this field will be placed in the same row as the Last name field and occupy column positions 3 and 4. Every time that the colset value is greater than the preceding form element's colset value, the form element will be placed into a pair of adjacent columns on the same row as the previous form element. QLR Manager fills out a form by placing items into the table starting in the top left corner and adding adjacent elements to the right for each incremented Free Style colset value. When a new colset of less than or equal to the previous elements value is applied, a new row is started.
The Label colspan provides a means to define how many columns across the Form a label will span. If the Label colspan for the State is set to 3, then the label will span across 3 columns in the Form as illustrated below.
There are some special elements available for Free Style layouts to help label and delineate groups of elements. These are the "Label text only" and "Horizontal rule" elements. In most cases where these are used, it will generally be beneficial to use the Label colspan to span all columns across the Form.
The vertical alignment of labels is useful when a multiple row input field is associated with it. This could be an input control assigned to an input field that is a textarea, a set of vertical checkboxes, etc. In the example below, the Comments label has been set as a vertical alignment of "Top". The type of Input Control used here is a "Text area pop-up editor" that can be useful to minimize the footprint of the textarea in the Form, yet still offer a much larger textarea to collect input.
This field is used to define help that is associated with the label for a particular element. There are two types which offer different behaviors:
|Tooltip help||Is visible when the User mouses over a label. This type of help is useful for a brief tip that does not require a lot of content. The text is entered into this field and can contain HTML tagging.|
|Pop-up help||Which is a URL loaded into a pop-up window. This type of help is more useful to link to a more extensive set of instructions or to another URL.|
Whether the element label displays a tooltip or acts as a link to open a pop-up window is determined by what is entered into this field. An entry beginning with http://, https://, ./, or ../ will denote either an absolute or relative URL for which QLR manager will open a pop-up window and load the URL. Any other text entered into this field will be treated as a tooltip. This allows a form to contain a mixture of tooltip and URL based help.
The attributes for the label link and tooltip properties are defined in the General Form Characteristics section.
The Label location determines where the label should be positioned in relation to the input field. QLR Manager creates two columns in an HTML table for form elements. The first column contains the label and the second column holds the input field. This is important to understand when selecting from the 5 options below to reposition these elements:
|Left of input||Places the label to the left of the input field in its own table column. By using two columns, QLR Manager is able to vertically align both the labels and input fields. This is the default setting.|
|Input below||Groups the label and input field into the same cell and the pair is stacked so that both fall within the same column as other input cells where the labels are positioned to the left of input cell. In the example below, Last name, First name (placed into Free Style col set 2), and Street are set as "Left of input". City, State (Free Style col set 2) and Zip (Free Style col set 3) are all set as "Input below".|
|Label above||Groups the label and input field into the same cell and stacks both into the label column. If multiple occurrences of "Label above" are used in the same form row, accomplished by incrementing the Free Style col set, they are compressed together. This is a good technique to use when multiple columns must be spread across the page, such as working with 12 months of data.|
|Same cell||Will group both the label and the input field into the same table cell and place both into the label column. In the example below, the Street element is placed into the same cell, and the Label colspan is set as 2.|
|Omit Label||Omits the entire cell that would hold the element Label. This allows the input cell to slide to the left. In the example below, the First name label is omitted, and the Last name label is altered to identify both the Last and First name input fields.|
This control allows the Form Designer to set the vertical position of the input field. The default value is "Middle". In this example the Last and First name input fields have been set to "Bottom":
The Input colspan determines the number of columns that an input field will span. In this example, the Street field has a colspan of 2 in order to accommodate a much wider text input field:
The Both rowspan setting impacts both the Input Field and its associated Label. In this example, the Comments element is set to the right of three other input fields. In order to produce the proper alignment, it has been assigned a Both rowspan of 3.
The Search Portal is created along with the Form and is utilized by the User to locate records in the database for Edit and Delete Forms.
Search comparisons define how the Search Portal should treat the provided search criteria. A detailed explanation of Comparison operators is provided with the Search comparison topic below.
Search criteria may be entered into the Search Portal to aid in locating specific data. The provided Criteria will be evaluated against the Field name based on the selected Comparison.
The Sort order selection allows the User to determine the order in which they want to see the records found in the search.
Whenever an Edit or a Delete Form is created, there is usually a need to first locate the data that these forms are intended to manipulate. This is the job of the Data Search Portal. Along with creating the Form, the Form Designer should create the Search Portal. Search Portals can select data from the database table by allowing the User:
|Full access||To search on all fields.|
|Limited searches||On specific fields.|
|Hidden searches||Where the search criteria is not displayed.|
|No search||Which would select all records in the table.|
The Search Portal will select data from tables that may contain millions of records. However, it limits the number of records in the search results to a maximum of 10,000.
The Searchable fields is a list of fields in the table selected for the basis of the Form. If the Form is initially populated with the "First 50 table columns" option, the Search Portal is also populated with those table columns. If "None" is selected for the initial population of the Form, it is up to the Form Designer to choose the fields they wish to include. Just as with the Form itself, the order in which the fields appear is controlled by the order in which they were added into this section.
As an example, the Form Designer may want to create a Search Portal to locate certain customers in the Customer table. If the Form Designer added the City, State and Zip fields to the Searchable fields list, the resulting Search Portal would look like this:
Records in the table must match all of the selection criteria provided. If 'Billings' is entered for City and 'MT' is provided for the State, the selection criteria would translate to: where city='Billings' AND state='MT'.
The Field actions are used to manipulate the searchable fields. Actions can be rolled back one at a time by clicking the Undo button.
|Add column||Works by first selecting an element from the Searchable fields list. Select the "Add column" Action, and then click the Apply button. The selected element will be added to the bottom of the existing search elements. A field can only be added once.|
|Insert before||Works similar to the "Add column" Action. First select an element from the Searchable fields list. Choose the "Insert before" Action, then select the Fld # of an existing element and click the Apply button. The selected element will be added before the selected Fld # element. A field cannot be inserted if it is already used in the Search Portal.|
|Move before||Works with elements that are already part of the Search Portal. Choose the name of the element to be moved from the Searchable fields list. Select the "Move before" Action, then choose the Fld # the element should be moved in front of and click the Apply button.|
|Replace||Works similar to the "Insert before" Action. First select an element from the Searchable fields list. Choose the "Replace" Action, then select the Fld # of the existing element to be replaced. Clicking the Apply button will replace the selected Fld # element with the selection from the Searchable fields.|
|Delete (Fld #)||Will remove a search element. Select the "Delete" Action, choose the Fld # to be deleted, then click the Apply button. This Action does not reference the Searchable fields list and explicitly deletes the selected Fld #.|
The Fld # refers to the column number of an element in the Search Portal. It is used for referencing the column associated with many of the above "Field actions".
The Field name column is a list of fields that have been defined to be part of the Search Portal. Removing all the Field names will eliminate the Search Portal and immediately present the Form. If more than one record is found in the table, a set of buttons will be available at the top of the Form. This allows the User to move between the selected records.
When the Hide field checkbox is checked, the selected field will not be displayed. This allows for selection criteria to be defined as part of the Search Portal, but not displayed to the User. In the example below, the Hide field for Zip was checked and will not be displayed even though a specific Zip code was entered as the Default search criteria. Other values can be populated in this manner and hidden from the User.
If all search fields are hidden, then the data selection is based upon the hidden selection criteria and the Search Portal will not be displayed to the User. The Edit or Delete form will be immediately presented.
Search comparisons define how the Search Portal should treat the provided search criteria. In most cases, it is the combination of what is selected as a Comparison, working with the provided Criteria.
|Equals||Is the most basic of search comparisons, selecting data where the information in the table field matches the provided criteria. If the criteria provided for the state field is NY, then the search is state='NY'. When multiple values are provided in the criteria field separated by commas (such as NY,CO,GA), then each value will be part of the search: state='NY' OR state='CO' OR state='GA'. To find NULL entries, the word NULL (all uppercase) can be part of the filter set. To find the text string NULL in a case sensitive database environment, enter !NULL! as the filter text. In a case insensitive environment, simply enter a lower case null. Two single quotes ('') can be used to find empty values. An entry of NULL,'' will provide the same results as choosing the Is blank filtering option.|
|Not equals||Is the opposite of equals, selecting data where the information in the table field does not match the provided criteria. If the criteria provided for the state field is NY, then the search is state!='NY'. When multiple values are provided in the criteria field separated by commas (such as NY,CO,GA), then each value will be part of the search: state!='NY' AND state!='CO' AND state!='GA'.|
|Less than||Returns values that are less than the criteria value provided.|
|Less or =||Returns values that are less than or equal to the criteria value provided.|
|Greater than||Returns values that are greater than the criteria value provided.|
|Greater or =||Returns values that are greater than or equal to the criteria value provided.|
|Between||Will return values that fall between the two provided values, or are equal to either of the provided values. The format for the criteria is lower_value and higher_value, such as AL and GA. The and keyword is used between the values.>|
|Not between||Will return values that do not fall between the two provided values, or are equal to either of the provided values. The format for the criteria is lower_value and higher_value, such as AL and GA. The and keyword is used between the values.>|
|Begins with||Will find records that begin with the provided characters in the criteria field. Multiple entries can be provided when separated by commas. For example, the criteria of n,g for the state field would find states beginning with either an "n" or "g".|
|Not begins||Will find records that do not begin with the provided characters in the criteria field. Multiple entries can be provided when separated by commas. For example, the criteria of n,g for the state field would find states that do not begin with either an "n" or "g".|
|Contains||Will search for a provided string of characters within the associated data field. If the state field were comprised of the full state name, then an entry of ne would return both new york and maine. Multiple entries can be provided, separated by commas.|
|Contains all||Is intended to be used with multiple criteria entries which are separated by commas. It will return records only when all of the Contain all values are found. An example of this would be a data field that tracks the toppings for pizzas. An entry of onions,peppers would find all pizzas that had both onions and peppers. Be as specific as possible, as this search would also return records that contained "red peppers and onions". This search is best suited to find records that may have been populated by checkbox sets or multiple select lists.|
|Not contains||Will search for a provided string of characters within the associated data field and return those records where that string is not present. If the state field was comprised of the full state name, then an entry of ne would omit both new york and maine. Multiple entries can be provided, separated by commas.|
|Ends with||Will search for a provided string of characters at the end of the associated data field. If the state field was comprised of the full state name, then an entry of ma would return both alabama and oklahoma. Multiple entries can be provided, separated by commas.|
|Not ends||Will return records when a provided string of characters is not at the end of the associated data field. If the state field were comprised of the full state name, then an entry of ma would return new york and virginia, but not alabama or oklahoma. Multiple entries can be provided, separated by commas.|
|Like||Utilizes the SQL LIKE command. It uses wild card characters to find string segments. The two wild card characters are "%" and "_". "%on" would find any string ending with "on". "james%" would find any string starting with "james". The "_" is a wildcard for a specific position in a string. "b_nk" would find "bank" and "bunk". "%L__" (two underscores) would find records where the third to the last letter is an "L" Multiple entries can be provided, separated by commas.|
|Not like||Utilizes the SQL LIKE command. It works the same way as the "Like" above, except it returns items that do not match. Multiple entries can be provided, separated by commas.|
|Null||Will select all records where there is a null value for that field. This ignores any criteria that may have been provided. Please note that a null value in a database is different than an empty string. To locate empty strings, use Equals with a comparison of '' (two single quotes with no spaces between them).|
|Not null||Will select all records where there is a value for that field. This ignores any criteria that may have been provided.|
|Is blank||Will select all items where the value is either NULL or empty (''). Is not blank will select just the opposite of Is blank.|
|Custom SQL||Allows the User to define Structured Query Language (SQL) text to be inserted into the search. For example, the User could enter the following into the criteria field where the comparison has been set to Custom SQL: city in (select big_city from high_population). Notice how the "city" field is referenced in the Custom SQL text. It is up to the User to create a complete comparison, including the fields to which the comparison is mapped.|
|Ignore crit||Allows the User to ignore the currently entered criteria for that field. This is useful when a large criteria entry has been provided, and the User wants to ignore it for an individual search without having to remove the criteria.|
When defining a form, the default value for the Comparison operator is "Equals". This option allows the Form Designer to set the default value to something else, such as "Contains". When the Search Portal is presented to the User, the selected default value will be displayed
There may be times where the User should not be given the option of changing the search comparison, such as creating a search panel to allow a customer to access only their information. When this checkbox is checked, the default comparison operator will be visible, but no select list of values will be presented:
The Default search criteria is the initial value that will be displayed to the User when they access the Search Portal. If an input control is used for presenting the criteria choices, such as a checkbox set, then these values will set the initial values of the Input Control:
Checking the Lock crit checkbox will make the criteria visible, but the User will not be able to change it. If an input control has been mapped to this field, it will be ignored.
The Sort order field allows the User to determine the order in which they want to see the records found in the search. The default Sort order can be set here by the Form Designer.
Checking the Lock sort checkbox will make the sort order visible to the User, but they will not be presented with a select list to change the value. The Form Designer could lock all of the sort controls to prevent the User from overriding the sort order:
The Criteria text input box can be replaced with an Input Control. If the Form Designer wishes to use an input control that they authored, then it is only required to enter the Input Control name. If the use of an input control owned by another owner is desired, the referenced control must be prefixed using the owner ID.control name format, such as jeff01.customer list. When the cursor is in one of the Optional input control fields, pressing the "Ctrl" key will pop up a small window that contains a list of input controls. This can be used to quickly reference and apply available controls:
In this example, the zip field has been assigned an input control that is comprised of vertical checkboxes. The Lock crit checkbox is ignored when referencing an input control:
If the Input req'd checkbox is checked, QLR Manager will validate that search Criteria has been entered. If not found, a message panel will be displayed and the User will be returned to the Search Portal:
Forms can be divided into expandable/collapsible sections by selecting a section number in the Basic Formatting section. The behavior of each of these sections is controlled by these settings. The behavior cannot be changed for section 0 (zero). It is always open, and does not have a title. Section 0 is the default section number.
These controls are used to change the appearance of the section title text.
The Behavior setting controls if a section can be opened or closed. In addition, if the section can be opened, should it be open when the Form loads for the first time.
Sections are easily identifiable by the "twistie" that precedes the section title. These are black PNG images. If the Form Designer wishes to uses different color twisties, perhaps to match the color of the section title, the following PNG can be replaced.
The title is the text that will appear at the top of the section.
When working with an Edit or a Delete Form, records will be selected that meet the criteria entered into the Search Portal. If more than one record is found, a set of navigation buttons will be available at the top of the Form: . Unavailable options are grayed out. This allows the User to move between the selected records.
In order to update data in a record, or delete a record, the User must click the Update or Delete button, which is located at the bottom of the Form. Moving between records will not update or delete data.