Form Assistant

Result:

• A well styled Bootstrap 5 form with CRUD functions
• Ready to use in your web database application

The data type of the form element depends on the database column. As a database app builder, K8 Web Kit starts by creating the table.

Process for the Web form creation

1. please login first (register)
2. table creation, Elements online
3. Create data definition, generation of the basic form with the right data types
4. Assitant, starts the Web form assistant with
   • layout and
   • field configuration

The most common data types are:

• INT: for number without decimals (used also as primary key)
• DECIMAL: for numbers with a fixed maximum of decimals
• VARCHAR: a variable length string with a maximun length
• TEXT: for long strings with a maximun of 65535 bytes
• DATETIME: a date and time combination

More to data types at w3schools MySQL Data Types

Representation:

• Stand alone
• Embedded
• Master data form
• Simple data form
• Linked to a catalog

Layout mode:

• One block layout
• Containers with different blocks

Form layout:

• Horizontal
• Vertical
• Floating

Specials:

• Special HTML elements, tags and input types
• Additional libraries: TinyMCE, use-bootstrap-tags, CodeMirror
• Joined fields: user groups, datalist
• Special appearances: input group, with dropdown

If your datadefID is not listed, enter it the form element datadefID and save it. Then press 'Get datadefID'.

Get datadefID

Get datadefID reads the datadefinition and list the columns in Form elements.

Update datadefID

The definition of k8form, tabulator and columns is saved in the JSON data definition.

Update + Open

The definition of k8form, tabulator and columns is saved in the JSON data definition and the master data form is opened.

This is a beta version! The configuration is yet not limited to the data type of the table column!

datadefID

• Type in your datadefID from elements online.

form layout

• vertical
• horizontal
• floating

layout mode

• 0: inactive
• 1: all fields in 1 block
• 2: container layout (define row and blocks below

Container layout: 2;3

• each number stands for a row:
  • first row with 2 blocks
  • second row with 3 blocks

The container layout allows to define several containers and place the form elements in it. The first digit determines the row. The second digit determines the column.

The block numbers are used to position the fields in the right block.

This are the standard templates. The settings allows to change the templates and for example use fieldsets.

Layout container 2;1: two blocks side by side in the first row, one block below. Each block is a bordered fieldset with a legend:

How to:

• Layout mode 2: container layout, Layout container 2;1
• Enter the block of each field in Pos area (1.1, 1.2, 2.1)
• Paste the templates into Settings and save
• Press Update datadefID

The fieldset needs the classes border border-2 rounded, the title is the legend.

A row with 1 block: only the block template is used (no row template), so leave out col-md-12.

The button Element in the Form Assistant opens the layout assistant. It arranges the elements of the master data form in a bootstrap grid, without writing the html by hand:

• {{filterformwrapper}}: filter form
• {{tabulatorwrapper}}: tabulator list
• {{formwrapper}}: k8form
• own tags, e.g. a place for images (pic_ph) or new elements

Result: the html snippet

• <datadefID>_masterdata_wrapper.html in the folder of the data definition

How to

1. Select the row of the data definition in the Form Assistant (exactly 1 row).
2. Press Element. An existing wrapper file is read into the grid, otherwise the standard preset is shown.
3. Choose a preset and press Apply preset, or build the grid yourself:
   • + row, + column: add rows and cells
   • width of a cell: 1 to 12 (the cells of a row should sum up to 12, otherwise a warning is shown)
   • ⇵: split a cell into rows (nested rows)
   • ×: delete a cell or a row
4. Drag the elements (chips) from free elements into a cell. Drop a chip back on free elements to remove it from the grid.
5. Own tag: enter a name and press Add tag. pic_ph or .pic_ph is written as <div class="pic_ph">pic_ph</div> (a class: several elements may share it, CSS .pic_ph). #bugfix is written as <div id="bugfix">bugfix</div> (an id: it must exist only once on the page, JavaScript finds it with document.getElementById('bugfix'), CSS #bugfix). The chips show . or # in front of the name.
6. Check the generated HTML below the grid and press Save.

Presets

• All in one column (standard)
• Table left, form right (5 / 7)
• Left tall, right 2 blocks: list left, form and an own tag right
• 3 blocks below each other: list, form, own tag

Toolbar

• side by side from: breakpoint of the columns (sm, md, lg, xl, xxl), e.g. col-lg-6; below it the cells are below each other
• headline, footline: adds {{headline}} / {{footline}}
• selector: saved in the data definition as masterdata.selector (masterdata0, masterdata1, html0, html1); html0 uses the full browser width
• tabulator height 100%: saved as tabulator.height; unchecked the own height of the data definition is kept, else 311px

Check below the HTML:

• number of <div> and </div> must be equal
• {{tabulatorwrapper}} and {{formwrapper}} must be in the html

Generated html, preset Table left, form right (<datadefID>_masterdata_wrapper.html):

Existing file

The file is read back into the grid. Its html stays unchanged in the code field until you change the grid; then the html is generated new: own attributes of the grid, comments and different breakpoints are lost. Own tags keep their html; an element with an id is shown as #name, otherwise by its first own class as .name.

Saving

• an existing wrapper file is overwritten after a confirmation
• selector and tabulator height are written into <datadefID>.json only if they have changed
• reading and saving needs the superuser (role 2)

Layout assistant and container layout

• The layout assistant places the elements of the master data form (filter form, tabulator, form, own tags): file <datadefID>_masterdata_wrapper.html, see Master data, Html layout.
• The container layout (layout mode 2) places the fields inside the form: Layout container (e.g. 2;1), Pos area of each field and the templates in Settings, see the example erpersons (2;2 with fieldsets).

Sources:

• masterdata/k8userdefs/k8layoutassistant.js: the assistant (k8LayoutAssistant())
• masterdata/k8userdefs/k8userdefs_head_end.js: button handler (menu value layoutassistant), reading and saving the files
• masterdata/k8userdefs/k8userdefs.json: button Element and the script in head_end
• masterdata/js/lang_sys_{de,fr,es,ru,th}.js: translations of the texts

The Form elements are a very powerful instrument for configuring awesome forms.

• HTML Form elements
  • input, textarea, select
  • types
  • attributes
• Form elements enhanced functionality
  • editor, tinymce
  • datalist fields joined to foreign tables
  • usergroups
  • input groups

Column order: Sort

If you want to place an element in a new position, just write the sort number of the element, you want to put it behind, followed by decimal separator and 1. Go to the Form elements menu and sort new.

The Form elements: tagName

• input & types: Core element for data input
• textarea: Used for multi-line text input (like comments or messages)
• select & option: For a list to select from
• button: Submitting the form or other actions

The Input Types

More to attributes:

• w3schools HTML input attributes.

The option diaog is enabled for:

• input: type=radio
• select
• datalist (only values)

For input (type=radio) and select, choose between:

• Text
• Value + Text

To allow empty text for the selection, leave the first line blank.

The datalist dialog is only available for special datalist elements, which are joined to foreign data definition.

datadefID

This is the datadefinition, you want to join with.

ID field

This is the field name, which correspond to the primary key column of the source datadefinition.

link field

The link field filters the records in the source datadefID.

field list (comma separated string)

The fields need to be form elements. If the column name of the source table is different to your form element name, define it like this:

• <form element name>=<source column name>

only options

Only options limits the data entry on records, which are in the source table. Otherwise all entries are allowed.

Attention: by use of datalist fields the source datadefID needs a datalistcolumn (property of the data definition).

Your form element is filled with the content of the source datalist column.

The input group dialog allows you to put a HTML element to the right or left of the input field. If you want to put more than 1 input field in the input group, choose the field type: 1:JSON.

Column

• DROPDOWN: for a dropdown element; add you elements in JSON in the item array. HTML and action is not necessary.
• HTML: add your icon in the column HTML and fill entry.
• <column name>

HTML

Place a Bootstrap Icons into the HTML column like this:

• <i class="bi bi-telephone"></i>

Action

The action value is used in JavaScript for the specific programm code.

The tabulator sub element need to be a ready defined datadefinition:

• datadefID: the datadefinition for the tabulator element
• table: the name of the sub table
• key: the key of the sub datadefinition
• fieldname: the name of the data array for the sub element
• masterkey: the masterkey is the column with the key of the parent element

The list is a part of the form: it is loaded and saved together with the parent record. Example and details: Master + tabulator child.

A tabulator sub element is not the same as a master detail element. A master detail element is an element of its own, which is saved by itself.

The assitant has 2 parts:

• the list with the columns
• the formula column, here pricetotal, with the formula content area

Process of writing and testing the formula:

• Select first a row to test your formula with the row selector
• the JavaScript formula editor has intellisense support. Insert your columns, operators and JavaScript functions. If something is wrong, you'll get a red, jagged underline. If it's right, press Set
• the value of your column is displayed in the tabulator cell
• with save you can directly store it in the database

Back

The formula is written to the json k8form. Save then the form element. After Updata + Open the formula will work in forms and lists.

Elements with special features have the main goals:

• support bootstraps dropdowns and input groups.
• enhance the controls with functioniality of additional libraries.
• join the fields to foreign tables.

Attention: datalist (fieldtypes: 211,212,223,224)

The joined column in the table should be the IDfield. The datalist column is configured like this, fieldtype:

• 211,223, the search column is an additional field! Use the button New to add it to the form:
  • identifier / name: datalist column of the joined datadefID
    By updating the datadefinition the sql statement is also extended with the datalist column of the joined datadefID
• 212,224:
  • identifier / name: datalist column of the joined datadefID

sql_additionals

For fieldtype 211 and 223 the new field is added automatically to sql_additionals, if it's not added yet. Fhe field is added like this:

• select:
  • foreign table.column as column
• from:
  • LEFT OUTER JOIN foreign table ON this_table.IDfield=foreign_tabe.key

If the datalistcolumn of the linked datadefinition is not equal to your "column name", you have to overwrite the select expression manually.

fieldtype: 145, optionsadditional:

tag Name: select

JSON k8form, please configure it manually:

• datadefID: <your datadefID>
• column_mapping
  • value: <key column of your table>
  • text: <displayed text>

fieldtype: 211, datalist only options:

If you need to join a foreign table and enter the values of it in your form, do the following:

• select the column with the foreign ID: accountID
• if you don't need it to be displayed:
  • type: hidden
  • JSON tabulator, add: ,"visible": false
  • Save
• Form elements: New join
• datalist dialog opens
• choose your datalist
• Ok: the new field is inserted in the form
• Save
• Sort
• Update + open

fieldtype: 211, joined tables:

The main table / datadefID is:

• exinvoice

The joined table / datadefID is:

• excustomer

The tables are joined by the column accountID. The column name1 only exist in the customer table. It is joined with the sql statement. It's also added in the form with an additional datalist field.

fieldtype: 211, sql statement:

This is the sql statement to join both tables. It is created autotmically by the definition beneath.

datadefinition: masterdata:

In the datadefinition the joined table are defined by:

• sql_additionals
  • select
    • column: expression
  • from
    • table name
      • join expression

fieldtype: 401, tabulator sub:

A form can contain a list of its own records, e.g. the items of an invoice. The list is saved together with the form. You need:

• a ready defined datadefinition for the list (the child), e.g. exinvoiceitems
• in the table of the child: a key (itemID) and a column with the key of the form, the masterkey (docID)
• in the form assistant a field with fieldtype 401. The button Tabulator sub opens the dialog: choose the datadefID, table, key and masterkey are filled in
• Save and Update + open

The settings of the child (head, settingsadditional, optionsadditional, ...) are not copied. Add them by hand, if the child needs them.

fieldtype: 401, datadefinition: k8form field and childs:

The dialog writes both parts:

• k8form field
  • fieldwraptemplate: the HTML element k8-tabulatorsub is the place of the list
  • child: the settings of the dialog
• childs
  • childmode 2: the list is defined by a datadefinition
  • fieldname: the name of the array in the data of the form, it contains the rows
  • masterkey: the column of the child, which is joined to the form

fieldtype: 401, JavaScript:

k8.datadefAddtabulatorsub(settings) in the head_end.js of the form element:

• builds an editable list in the place of the field
• fills it with the rows of the form
• when the form is saved: writes the rows back into the data, without empty rows and with the masterkey. The server saves them with the form

Complete example with an invoice: Master + tabulator child.

This is a part of the customer tabulator definition (string columns):

The most often used column properties are:

• title - Required This is the title that will be displayed in the header for this column
• field - Required (not required in icon/button columns) this is the key for this column in the data array
• headerfilter - true for more details look in the tabulator documentation
• visible - (boolean, default - true) determines if the column is visible
• hozAlign - sets the horizontal text alignment for this column (left|center|right)
• headerHozAlign - sets the horizontal text alignment for this columns header title (left|center|right)
• width - sets the width of this column, this can be set in pixels or as a percentage of total table width (if not set the system will determine the best)
• minWidth - sets the min width of this column, this should be set in pixels
• formatter - Tabulator offers a wide range of formatters for strings, numbers, dates, ...
• formatterParams - additional parameters you can pass to the formatter

For more details look to:

• tabulator

Columns can easily removed or added. if desired you change the order of the columns. If you extend the sql-statement, you can easily add the columns here.

Example for date and number:

The formatting of dates and numbers depends on the settings of the clients web browser. K8 Web Kit reads this on the first page request and sets it in the session variables. This are the corresponding JavaScript variables:

• GLOBALS_decimal_point
• GLOBALS_thousands_sep
• GLOBALS_tabulatordateformat
• GLOBALS_tabulatordatetimeformat
• GLOBALS_tabulatortimeformat

The date and time formats follow the luxon syntax, date time format:

• 'dd.MM.yyyy HH:mm'