Form extension

The form exetension allows creating multilingual questionnaire type custom forms. The form is posted to a myDBR linked report which receives the user entered valued as parameters (via HTTP POST).

Commands

dbr.form - Create a form

Syntax

select 'dbr.form', 'command', options..

Where command:

'start', 'from_us'
Starts the form. First command in form.
'end'
Marks the end of the form. Last command in form.
'section.start'[.no_highlite]
Starts a logical section in the form
'section.end
Ends a logical section in the form
'select', 'id', 'title' | null [, 'plain' | 'required']
Creates a select list from subsequent result set. The 'plain'-option creates just the select list. The 'required' option can be used when the select list is after a checkbox or radio button and the select list selection is required when the corresponding checkbox/radio button is selected
'text', 'id', 'title' | null [,'required']
Creates an input field. The 'required' option can be used when the input field is after a checkbox or radio button and the input is required when the corresponding checkbox/radio button is selected
'textarea', 'id', 'title'
Creates a texarea
'table', null, 'title', 'col1', 'col2', 'col3'..
Creates a table from the subsequent result set. The table columns are the columns defined in col1, col2, etc.
'checkbox', number_of_columns, title
Creates a series of checkboxes from the subsequent result set with the desired number of columns.
'submit', title
Creates a submit button

Form parts

Enabling the extension includes a CSS file and a JS file from extensions/form. The CSS file styles the form with custom style. The JS file handles the optional multilingual aspects of the form: changing the form language keeps the user changes to the form and handles the required field settings on dependent elements.

The form report consist of:

  • An HTML form-tag with a linked report attached to it for handling the submit
  • Optional language choise for multilingual forms
  • Sections for grouping the form elements. Sections appear to the user as logical blocks.
  • Form elements inside the sections (text fields, checkboxes, radio buttons etc)

Form tag with a linked report

The form definition consist of a linked report that handles the form submission and the creation of the form tag:

select 'dbr.report', 'sp_DBR_form_submit', '[form_action]', concat('append=&i=0',f_lang_append(GET_lang)), 'plain_URL=1';

select 'dbr.form', 'start', 'cr_form';
select '' as  'form_action';

The select 'dbr.form', 'start', 'cr_form';-command creates the form-tag with name 'cr_form'.

The append part the forallows submitting empty forms and optionally adds the GET lang-paramerter to the URL. The plain_URL-option creates just the URL instead of a-tag.

The optional f_lang_append-function checks the lang-parameter. If you do not need multilingual parameters, you can ignore this.

create function f_lang_append(lang char(5)) 
returns varchar(20)
begin

if (lang in ('en_US', 'de_DE', 'el_GR', 'es_ES', 'fi_FI', 'it_IT', 'nl_NL', 'sv_SE')) then
  return concat('&lang=',lang);
end if;

return '';

end

Form tag with a linked report

One can insert any elements into the form via dbr.html-command. A specific select tag element with name 'lang' is reserved for creating multilingual reports. myDBR handles the form language change with defined translations while preserving the user choises in the form.

select 'dbr.html', '<div class="lang-choise">';
select 'dbr.form', 'select', 'lang', null, 'plain';

select 'fi_FI', 'Suomi'
union
select 'en_US', 'English'
union
select 'sv_SE', 'Svenska'
union
select 'el_GR', 'Ελληνικά'
union
select 'de_DE', 'Deutsche'
union
select 'es_ES', 'Española'
union
select 'it_IT', 'Italiana'
union
select 'nl_NL', 'Nederlandse'
;

The command select 'dbr.form', 'select', 'lang' will generate the select tag with options where the first value from the result set is the option value and the second one is the name. The 'plain'-option will generate just the select tab and not any additional tags around it as it would do for actual form elements.

Defining sections within the report

Sections start with section.start or section.start.no_highlite options for dbr.form-command. The no_highlite-definition disables the sections CSS hover (light blue highlite). The 'dummy' resultset after the command triggers the command to be effective (dbr.form is a myDBR extension).

select 'dbr.form', 'section.start.no_highlite';
select 'dummy';

Sections end with section.end options for dbr.form-command

select 'dbr.form', 'section.end';
select 'dummy';

One can combine the two commands into one when separating the options with comma:

select 'dbr.form', 'section.end,section.start';
select 'dummy';

Defining form elements: radio buttons

Radio buttons consist of one or more choises for the same parameter. The name of the parameter in the example will be 'how_long_used' with 'in_'-added in front of it i.e 'in_how_long_used'. The parameter for the submit linked report's parameter will be 'in_how_long_used'.

select 'dbr.form', 'radio', 'how_long_used', 'How long have you used myDBR?';


select 0, 'Have not yet used it'
union
select 1, '0 - 1 years'
union
select 2, '1 - 5 years'
union
select 3, '5 - 10 years'
union
select 4, 'Over 10 years';

This will create a element like this:

One can add additional fields for radion button selection with dbr.record. This is useful when you need to ask user clarification or select a value from select list for individual radio button selection.

  
select 'dbr.record', 'begin', 'other_products';

select 'dbr.form', 'text', 'other_product', null, 30, 'required';
select 'dummy';

select 'dbr.record', 'end';


select 'dbr.record', 'begin', 'other_products_choose';

select 'dbr.form', 'select', 'other_products_chosen', null, 'required';

select null, ''
union
select 'prod_a', 'Product A'
union
select 'prod_b', 'Product B';

select 'dbr.record', 'end';


select 'dbr.form', 'radio', 'how_long_used', 'Have you used other products?';

select 0, 'No', null
union
select 1, 'Choose, what', '{#other_products_choose}'
union
select 2, 'Other, what', '{#other_products}';

The required-attribute means that the field in question is a required field if the corresponding radio button is selected.

Note that the select and radio elements not not need the 'dummy'-selection as the result set does already that.

This will create a element like this:

Defining form elements: select list

The select list can be defined as:

  
select 'dbr.form', 'select', 'database', 'Primary database';

select null, ''
union
select 'mysql', 'MySQL'
union
select 'mariadb', 'MariaDB'
union
select 'mssql', 'Microsoft SQL Server'
union
select 'sybase', 'Sybase';

This will create a element like this:

Defining form elements: tables

A table element consist of multiple parameters with number of values:

  
select 'dbr.form', 'table', null, 'Overall evaluation', 'Excellent', 'Good','Fairly good', 'Satisfactory', 'Poor';

select 'overall_evaluation', 'Overall evaluation'
union
select 'product_quality', 'Product quality'
union
select 'documentation', 'Documentation'
union
select 'support', 'Support'
union
select 'value_for_money', 'Value for money';

This will create a element like this:

The values for the parameters (like 'overall_evaluation') will be from 5 to 1 (Excellent=5, Good=4 etc)

Defining form elements: chcekboxes

Checkboxes can be grouped together and arranged into columns:

  
select 'dbr.form', 'checkbox', 3, ' Authentication';

select 'google', 'Google'
union
select 'ad', 'Active Directory'
union
select 'custom_login', 'Custom login'
union
select '2fa', 'Two-factor authentication';

This will create a element like this:

Checkboxes like radio buttons can have additional elemenrs (dropdown, text) attached into individual choises. See the full example below.

Defining form elements: text and textarea

The textarea allows multiline input whilas the rext is a simple text field

  
select 'dbr.form', 'textarea', 'comment', 'Comments / improvement ideas';
select 'dbr.form', 'text', 'contact_info', 'Contact info', 50;
select 'dummy';

Defining required fields

The form-extension relies on the HTML required-attribute. Fields that are parameters to the submit linked report get their required-attribute status from parameter's 'Optional' attribute. If the 'Optional'-attribute is unchecked, the required attribute is added to the field in submit.

Defining the submit report

The form submit report (see sp_DBR_form_submit above) will get the parameters from form's POST). The submit report should do a redirect to another report after processing the values so that if the user refreshes the page the submit will not be repeated.

Sample report and sample submit report

  
CREATE PROCEDURE `sp_DBR_feedback_doc`(
GET_lang varchar(30)
)
BEGIN

/* We do not need the title */
select 'dbr.title', '';


/* 
  The submit report
  &i=0 will run the submit report even with empty form
  f_lang_append(GET_lang) will pass the 'lang' URL parameter to the submit report
  plain_URL places the plain URL to the report to form's action attribute
*/

select 'dbr.report', 'sp_DBR_feedback_doc_submit', '[form_action]', concat('append=&i=0',f_lang_append(GET_lang)), 'plain_URL=1';

/* Create a form with id cr_form */
select 'dbr.form', 'start', 'cr_form';
select 'form_action';

/* Create a section wihtout background highlite for the logo and and the language selection */
select 'dbr.form', 'section.start.no_highlite';
select 'dummy';


select 'dbr.html', '<div style="text-align:center"><img src="user/images/myDBR.svg" style="width:400px"></div>';

/* Create optional language select tag and center it */
select 'dbr.html', '<div class="lang-choise" style="text-align:center">';
select 'dbr.form', 'select', 'lang', null, 'plain';

select 'fi_FI', 'Suomi'
union
select 'en_US', 'English'
union
select 'sv_SE', 'Svenska'
union
select 'el_GR', 'Greek'
union
select 'de_DE', 'German'
union
select 'es_ES', 'Spanish'
union
select 'nl_NL', 'Dutch'
;

select 'dbr.html', '</div>';


select 'dbr.form', 'section.end,section.start';
select 'dummy';

/* 
  Record the other_products_choose record with a select element
  Note the required option (see below)
 */
select 'dbr.record', 'begin', 'other_products_choose';
  select 'dbr.form', 'select', 'other_products_chosen', null, 'required';
  select null, ''
  union
  select 'prod_a', 'Product A'
  union
  select 'prod_b', 'Product B';
select 'dbr.record', 'end';



/*  
  Create a radio selection and use the recorded elements 
  The exrta fields (other_products_choose and other_products) were marked as required. 
  If the respective radio button is chosen, the extra field needs to be filled
*/
select 'dbr.form', 'radio', 'used_another_product', 'Have you used other products?';

select 0, 'No', null
union
select 1, 'Choose, what', '{#other_products_choose}'
union
select 2, 'Other, what', null;

select 'dbr.form', 'section.end,section.start';


/* Create a select field */
select 'dbr.form', 'select', 'database', 'Primary database';

select null, ''
union
select 'mysql', 'MySQL'
union
select 'mariadb', 'MariaDB'
union
select 'mssql', 'Microsoft SQL Server'
union
select 'sybase', 'Sybase';


select 'dbr.form', 'section.end,section.start';

/*
  Create a table element with values from 5 to 1 
*/
select 'dbr.form', 'table', null, 'Overall evaluation', 'Excellent', 'Good','Fairly good', 'Satisfactory', 'Poor';

select 'overall_evaluation', 'Overall evaluation'
union
select 'product_quality', 'Product quality'
union
select 'documentation', 'Documentation'
union
select 'support', 'Support'
union
select 'value_for_money', 'Value for money'
;

select 'dbr.form', 'section.end,section.start';

/*
  Create a checkbox element in 3 columns
*/
select 'dbr.form', 'checkbox', 3, ' Authentication';

select 'google', 'Google'
union
select 'ad', 'Active Directory'
union
select 'custom_login', 'Custom login'
union
select '2fa', 'Two-factor authentication'
;

select 'dbr.form', 'section.end,section.start';
select 'dummy';

/*
  Create a textarea
*/
select 'dbr.form', 'textarea', 'comment', 'Comments / improvement ideas';


/*
  Create an input field
*/
select 'dbr.form', 'text', 'contact_info', 'Contact info', 50;
select 'dummy';

select 'dbr.form', 'section.end';

/* The submit button */
select 'dbr.form', 'submit', 'Submit';
select 'dbr.form', 'end';
select 'dummy';

END

This will produce a form like this:

  
CREATE PROCEDURE `sp_DBR_feedback_doc_submit`(
in_lang varchar(10),
in_used_another_product int,
in_other_products_chosen varchar(20),
in_database varchar(20),
in_overall_evaluation tinyint,
in_product_quality tinyint,
in_documentation tinyint,
in_support tinyint,
in_value_for_money tinyint,
in_google int,
in_ad int,
in_custom_login int,
in_2fa int,
in_comment text,
in_contact_info text
)
begin

insert into doc_feedback2 (
  language,
  used_another_product,
  other_products_chosen,
  db,
  overall_evaluation,
  product_quality,
  documentation,
  support,
  value_for_money,
  google,
  ad,
  custom_login,
  auth2fa,
  comment,
  contact_info
)
values ( 
  in_lang,
  in_used_another_product,
  in_other_products_chosen,
  in_database,
  in_overall_evaluation,
  in_product_quality,
  in_documentation,
  in_support,
  in_value_for_money,
  in_google,
  in_ad,
  in_custom_login,
  in_2fa,
  in_comment,
  in_contact_info
);

select 'dbr.redirect', 'sp_DBR_questionnaire_thank_you', concat('append=&m=0&hdr=0',f_lang_append(in_lang));
select 'dummy';

end

The parameters that are not mandatory are set as 'Optional'. The select list 'other_products_chosen' is set to be 'required' in the report code and myDBR will make it required if the 'used_another_product' parameter is chosen.