Form extension

The form extension enables the creation of multilingual custom forms, similar to questionnaires. Upon submission, the form is sent to a myDBR linked report. This linked report then receives the user-entered values as parameters through an HTTP POST request.

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
'radio', 'id', 'title' [, default_selected_id ] ]
Creates a radio button list from subsequent result set
'select', 'id', 'title' [null | 'plain' | 'required'[, default_selected_id ] ]
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' [,length, [null | 'required' [, default_text[, placeholder ] ]] ]
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
'date', 'id', 'title' [,length, [null | 'required' [, default_date] ]
Creates a date field. The 'required' option can be used when the user input is required
'textarea', 'id', 'title' [ null | 'required' [, default_text[, placeholder ] ]]
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. The default value can be set as third column in subsequent result set
'checkbox', number_of_columns, title
Creates a series of checkboxes from the subsequent result set with the desired number of columns. The checked can be set as third column in subsequent result set (1=checked)
'json' [, parameter_name]
Uses JSON in POST instead of separate fields. Place command between dbr.report and form start. The parameter name by default is inJSON
'submit', title
Creates a submit button

Form parts

Enabling the extension involves incorporating a CSS file and a JS file from the 'extensions/form' directory. The CSS file applies custom styling to the form, while the JS file manages optional multilingual features of the form. When the form language is altered, this JS file retains user modifications to the form and manages the 'required' field settings for dependent elements.

The form report consist of:

  • An HTML form-tag with a linked report attached to it for handling the submit
  • Optional language choice 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;choice

return '';

end

Form tag with a linked report

Any elements can be incorporated into the form using the dbr.html command. A particular select tag element named 'lang' is designated for creating multilingual reports. myDBR manages form language changes through predefined translations while retaining user selections within the form.

select 'dbr.html', '<div class="lang-choice">';
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 produce a select tag with options. In this case, the first value from the result set will serve as the option value, and the second value will be the display name. The 'plain' option generates only the select tag itself, omitting any additional surrounding tags that are typically used for actual form elements."

Defining sections within the report

Sections can be initiated using either the section.start or section.start.no_highlight options within the dbr.form command. The inclusion of no_highlight deactivates the CSS hover effect (light blue highlight) for sections. The presence of a 'dummy' resultset after the command activates its functionality (note that 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';

The two commands can be combined into a single command when separating the options with a comma:

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

Defining form elements: radio buttons

Radio buttons comprise one or more choices for the same parameter. In the example, the parameter name will be 'how_long_used,' and 'in_' will be added in front of it, resulting in 'in_how_long_used.' The parameter for the linked report's submission 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 result in creating an element like the one shown below:

Additional fields can be incorporated for radio button selections using dbr.record. This is particularly valuable when seeking user clarification or choosing a value from a select list for specific radio button selections.

  
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 indicates that the field in question becomes mandatory when the corresponding radio button is selected.

Note that the select and radio elements do not require the 'dummy' selection, as the result set already serves that purpose.

This will result in creating an element like the one shown below:

Defining form elements: select list

The select list can be defined as follows:

  
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 result in creating an element like the one shown below:

Defining form elements: tables

A The table element comprises multiple parameters, each with a certain 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 result in creating an element like the one shown below:

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 organized 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 result in creating an element like the one shown below:

Similar to radio buttons, checkboxes can accommodate additional elements (such as dropdowns or text inputs) attached to individual choices. Refer to the complete example below.

Defining form elements: text and textarea

The textarea allows multiline input, while the text field is designed for simple single-line text

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

Defining form elements: date

Date fields enable the utilization of the native datepicker

  
select 'dbr.form', 'date', 'license_date', 'License date', 10,'required';

Defining required fields

The form extension utilizes the HTML 'required' attribute. Fields that serve as parameters for the linked report upon submission obtain their 'required' attribute status from the parameter's 'Optional' attribute. If the 'Optional' attribute is unchecked, the 'required' attribute is applied to the field during submission.

Defining the submit report

The form submission report (refer to 'sp_DBR_form_submit' above) will retrieve parameters from the form's POST request. After processing these values, the submission report should perform a redirect to another report. This prevents accidental resubmission if the user refreshes the page.

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 '' as  '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-choice" 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

Parameters that are not mandatory are labeled as 'Optional.' The select list 'other_products_chosen' is designated as 'required' within the report code, and myDBR will enforce this requirement when the 'used_another_product' parameter is selected.

POSTing as JSON

You can configure the form to submit the data as JSON instead of separate individual parameters.

  
select 'dbr.report', 'sp_DBR_form_json_submit', '[form_action]';

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

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', 'Deutsch'
union
select 'es_ES', 'Española'
union
select 'nl_NL', 'Nederlands'
;

select 'dbr.form', 'select', 'products_chosen', null, 'required';
select "prod'a", 'Product A'
union
select 'prod_b', 'Product B';

select 'dbr.form', 'submit', 'Submit';
select 'dbr.form', 'end';
select 'dummy';

The submit report will only have one parameter:

  
create procedure sp_DBR_form_json_submit(
inJSON text
begin
...
end

If you create your own form using a template, the form tag should look as follows. The data-json attribute instructs myDBR to send the form as JSON. Optionally, you can use the data-jsonparam attribute to modify the default parameter name. Additionally, the data-formext="1" attribute indicates that myDBR should utilize HTML sessionStorage to save user selections. This allows users to pick up where they left off if they close the window.

<form action="{#form_action}" method="post" id="cr_form" data-json="1" data-jsonparam="inJSON" data-formext="1" accept-charset="utf-8">