Masterdata

Table requirement: primary key of the table

A table for master data has to fullfill the following rules:

• the Primary key has to consist of a single field
  • INT, autoincrement
• creatorID (by using the data access rights )
  • INT

The primary key is a hidden field in the master data form. It can not be changed!

Masterdata form on the fly

Checkout the generation of a masterdata form on the fly:

• Customer table on the fly:
  index.php?table=er_customer&page=masterdata
• Item table on the fly:
  index.php?table=k8components&page=masterdata

Attention, generate a table is only allowed on this demonstration page. This functionality is normally turned off!

Open data definition as page

you can open the datadefintion as:

• masterdata form:
  index.php?datadefID=customer&page=masterdata
• list:
  index.php?datadefID=customer&page=list
• form:
  index.php?datadefID=customer&page=form

The data definition contains details for reading, saving and displaying a recordset.

The following steps are necessary to create your own data definition:

• er_customer, if not exist create it with "k8examples.sql"
• generate your data definition
  • table: er_customer
  • datadefID: mycustomer
  • get_data definition.php?datadefID=mycustomer&table=er_customer
• folder <myproject> create a folder: mycustomer
• save the data definition in your folder mycustomer with the name "mycustomer" as json file.
• look in your browser:
  index.php?datadefID=mycustomer&page=masterdata

kitsamples/customerbasic/customerbasic.json:

The masterdata plugin offers this elements:

• master data form
• list with optional edit functions
• form only

To add an edit function to the list, please look for the "edittype" beneath.

Master data

<div class="row"> <div id="masterdata1"></div> </div> <script> var settings={"this is the":"data definition"}; var masterdata=$('#masterdata1').masterdata(settings); </script>

List (tabulator)

<div class="row"> <div id="masterdata1"></div> </div> <script> var settings={"this is the":"data definition"}; settings.masterdata.bnoform=true; // you only get a list settings.masterdata.edittype=0; // only by list, look to the edtittypes enumeration var masterdata=$('#masterdata1').masterdata(settings); </script>

Form only

<div class="row"> <div id="masterdata1"></div> </div> <script> var settings={"this is the":"data definition"}; settings.masterdata.notabulator=true; // you only get a form settings.masterdata.keyvalue=<ID>; // ID, form loads the data settings.data=[{col1:1,"col2":"my text"}]; // or the data var masterdata=$('#masterdata1').masterdata(settings); </script>

The return values of "masterdata" are:

console.log(masterdata.el_md); // HTML element console.log(masterdata.tableTab) // Tabulator table element masterdata.update(dat); // updates the current row masterdata.changes(settings) // changes the settings masterdata.getRow() // the current Tabulator row element masterdata.message(text,myclass,delay) // displays a message masterdata.insertRecord(dat) // inserts a new record to the top masterdata.gObject2FormBuild(form,el_object,obj,namestr) // inserts an object masterdata.setDirty(bool value)// set the dirty image the form masterdata.setElementValue(element,value) // updates an element of the form masterdata.newRecordExt(dat) // displays the data "dat" in the form

Properties and sections:

• name: the displayed name
• key: necessary for edit
• rightcheck: with RBAC, look to the explanation
• columns: the definition of the table columns
• masterdata: the main parameters
• tabulator: the list, please have a look to: tabulator.info
• k8form: the form, please have a look to: k8form
• html: the html snippets
• data: for form only

To change the look of your masterdata form, please configure tabulator (list at the top) and k8form (form at the bottom)

JSON structure:

{ "name":"<definition name>", "key":"<primary key column>", "rightcheck": 0 or 1, "columns":[], "masterdata":{}, "tabulator":{}, "k8form":{}, "html":{}, "data":{} }

key

The key column is used for edit functions. If the key value is zero, it's a new record.

rightcheck=1

The backend adds automatically:

settings.masterdata:

• right_usercreate: "0" or "1"

recordset

• right_userupdate: "0" or "1"
• right_userdelete: "0" or "1"

This is an part of the customer tabulator definition:

"tabulator": { "columns": [ { "title": "accountID", "field": "accountID", "headerFilter": true, "hozAlign": "right", "width": "80", "visible": false }, { "title": "accountnumber", "field": "accountnumber", "headerFilter": true, "width": "80" }, { "title": "name1", "field": "name1", "headerFilter": true }, ... { "title": "datetimecreated", "field": "datetimecreated", "headerFilter": "input", "width": 160, "formatter": "datetime", "formatterParams": { "inputFormat": "yyyy-MM-dd HH:mm:ss", "outputFormat": "GLOBALS_tabulatordatetimeformat" }, "visible": false } ] } }

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)
• 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.

This is an part of the customer k8form definition:

{ "k8form": { "selector": ".js_dataform", "templatetype": "masterform", "fields": [ { "name": "accountID", "type": "hidden", "label": "accountID" }, { "name": "accountnumber", "type": "text", "label": "accountnumber", "required":true, "maxLength": 50 }, { "name": "name1", "type": "text", "label": "name1", "required":true, "maxLength": 50 }, { "name": "name2", "type": "text", "label": "name2", "maxLength": 50 }, { "name": "street", "type": "text", "label": "street", "maxLength": 50 }, { "name": "country", "tagName": "select", "label": "country", "options": ["","DE","IT","FR"] }, { "inputgroup": { "label": "code / city", "fields":[ { "name": "code", "fieldclass":"form-control k8-flex-100-fix", "type": "text", "label": "code", "maxLength": 10 }, { "name": "city", "type": "text", "label": "city", "fieldclass":"form-control k8-flex-300", "maxLength": 50 } ] } }, { "name": "phone", "type": "text", "label": "phone", "maxLength": 50 }, { "name": "mobile", "type": "text", "label": "mobile", "maxLength": 50 }, { "name": "email", "type": "email", "label": "email", "maxLength": 250 }, { "name": "website", "type": "text", "label": "website", "maxLength": 100 }, { "name": "categoryID", "tagName": "select", "label": "categoryID", "dataoptions":{ "datadefID":"k8groups", "mode":1, "emptytext":"standard", "defaultclause":"type='customer_group'" } }, { "name": "representativeID", "type": "hidden", "label": "representativeID" }, { "name": "representativename", "type": "text", "label": "representative", "attributes":{ "list": "representativename_datalist", "autocomplete": "OFF" }, "datalist":{ "datadefID":"k8searchuser", "IDfield":"representativeID", "onlyoptions":true } } ] } }

The most often used column properties are:

• tagName - default: input This is the tag of the form element. Other supported tags are:
  • select
  • textarea
  • button
• type - for input, button most of the HTML5 input types are supported
• name - Required This is the name of the database column
• label - the label of the form element
• decimals - the decimals by type=number
• attributes{} - additional attributes for the field
  • maxlength - the maximum allowed charaters of the field
• required - defines if an form element is required
• disabled - defines if an form element is disabled
• inputgroup - declares an inputgroup, a bootstrap 5 element, consist multiple elements
• options - for select defines the options of a select element

For more details look to:

• k8form

Thanks to the container property of k8form HTML layout can be managed and fieldsets or multiple block can be displayed.

masterdata/templates/datadefinition_k8form_2blocks.json:

Bootstrap needs 3 containers to create 2 blocks:

• class: row
  • class: col-sm-6
  • class: col-sm-6

datalist example for employee "address":

{ "k8form": { "fields": [ { "name": "gender", "label": "Address", "attributes":{ "list":"gender_datalist" } }, { "fieldwraptemplate": "{{fieldelement}}", "fieldclass":"", "id": "gender_datalist", "tagName": "datalist", "innerHTML":"<option value='Mr.'></option><option value='Mrs.'></option>" } ] } }

"Address", name: "gender"

This field allows any entry to address the contact person. The "list" attribute points to a "datalist". In the data list you will find suggestions for field input.

"datalist", id: "gender_datalist"

Here are the suggestions for the field input listed. This "tag" is not displayed in the form. It has no label.

So the "fieldwraptemplate" only consist out of the "fieldelement". It also has no "class" -> the fieldclass is set to empty.

The "id" is same to gender attribute "list".

The "tagName" is "datalist".

In the "innerHTML" the options (list entries) are defined as HTML elements.

example for a status field:

{ "k8form": { "fields": [ { "name": "status", "tagName": "select", "label": "Status", "options":[ {"value":"","text":"please select"}, {"value":"0","text":"0: planed"}, {"value":"1","text":"1: done"} ] } ] } }

tagName

To select values out of an option list, you have have to declace a HTML tag: "select".

options

The options list allows you to define value pairs (value, text) to select from. If the text should be saved in field and table, you don't need a value.

example for a status field:

{ "tabulator": { "columns": [ {"title":"status", "field":"status", "headerFilter": "list", "headerFilterParams":{ "values":{ "":"","0":"planed","1":"done" }, "clearable":true }, "formatter":"#function#k8tabulatorformatter#", "formatterParams":{ "type":"color", "values":{ "0":{"text":"planed"}, "1":{"text":"done"} }, "width":120 } } ] } }

headerFilter: list

This defines a select field in the header filter.

headerFilterParams

The headeFilterParams define the values, which can be selected.

formatter:#function#k8tabulatorformatter

The k8tabulatorformatter allows to define values, which are displayed with the "text".

formatterParams

The value list contains the "text" property, which is displayed.

{ masterdata:{ "notabulator":false, /* don't displays tabulator list */ "bnoform:false, /* don't displays the form */ "defaultvalues":{ /* object for default values */ "column":"value" }, "keyvalue":"", /* set the value for the key column (form only)*/ "clause":"" /* sets an additional clause for the SQL Satement */ "url_new": "index.php?page=form&page_mode=2&datadefID="+datadefID, "url_edit": "index.php?page=form&page_mode=2&process_action=Edit&datadefID="+datadefID, "url_load": "masterdata\/ProcessData.php?process_action=Load&datadefID="+datadefID, "url_save": "masterdata\/ProcessData.php?process_action=Save&datadefID="+datadefID, "url_del": "masterdata\/ProcessData.php?&process_action=Del&datadefID="+datadefID, "url_readfilter": "masterdata\/ProcessData.php", "data_readfilter": { "datadefID": datadefID, "process_action": "ReadFilter" } }, "menuleft_options":true,/* enables the dropdown menu at the left */ "menuleftobj":{} /* additional elements for the menu */ "bGETfilters":true, /* the filters are set out of the url */ "bwithdirty":true, /* adds the dirty symbol */ "init_after_save":true, /* inits the form after save /not by auto_save */ "auto_save":false, /* for complex objects, by adding a new line */ "window_mode":0, /* is set automatically by new edit tabs */ "object_mode":0 or 1 /* 0=normal 1=complex object like invoice */ "edittype":8, /* please have a look to the edittypes */ "formmode":3, /* 1=html, 3=k8form (default) */ "message_position":4, /* 0 footline of form, 1 button line, 2 line above buttons, 3 in the last window line, 4 page overlay */ "bresize":true, /* adds a resizer at the bottom of tabulator */ "upload":{ "enabled":true, /* adds an upload and gallery beneath the form */ "singleimage":true or false } } }

defaultvalues

The defaultvalues allows to set default values for a new record in the form.

clause

The clause allows to filter the data.

url_...

• Ajax
  • load
  • read_filter
  • save
  • del
• Dialog
  • new
  • edit
  • master
  • detail

edittype

• 0 no controls
• 1 insert with reload (only catalog plugin)
• 2 innerHTML (form_selector)
• 3 overlay
• 4 new tab
• 5 new window
• 6 own line in catalog template (only catalog plugin)
• 7 own line with special template (only catalog plugin)
• 8 master data form
• 9 link in this window

Extend the datadefintitions with call back functions:

var f=settings.masterdata; f.cbCreateStructure=function(options){ var el_md=options.el_md; } f.cbMenuleft=function(options){ var el_md=options.el_md, e=options.e, row_actual=options.row_actual; var settings=options.settings,selected=options.selected; //options.display_record } f.cbBeforeNew=function(options){ var el_md=options.el_md,dat=options.dat; } f.cbAfterNew=function(options){ var el_md=options.el_md,dat=options.dat; } f.cbBeforeLoad=function(options){ var settings=options.settings,el_md=options.el_md,dat=options.dat; } f.cbAfterLoad=function(options){ var settings=options.settings,el_md=options.el_md,dat=options.dat; } f.cbBeforeDelete=function(options){ var settings=options.settings,el_md=options.el_md,dat=options.dat; var cancel=false; return cancel; } f.cbAfterDelete=function(options){ } f.cbChange=function(options){ var el_md=options.el_md,el=options.this; } f.cbPrepareSave=function(options){ var el_md=options.el_md } f.cbBeforeSave=function(options){ var el_md=options.el_md,dat=options.dat; var cancel=false; return cancel; } f.cbAfterSave=function(options){ var el_md=options.el_md,dat=options.dat; } //newRecord(options) dat f.onDelete(options) el_rec_record,e.target) ??onNew(options) } } //settings.masterdata.btnSave //settings.masterdata.cbbtnCancel

the parameters:

• settings: configuration of the element
• el_md: the masterdata html element
• dat: the record with column value pairs
• e: the event
• el: the source element
• selected: the selected rows
• row_actual: the current row

masterdata/templates/tinymceset.js

Create your datadefintion JavaScript file. Copy the line from the template into your file and add your datadefID to the settings variable.

additional declarations in the datadefinition:

{ "head":["<script src='{{root}}tinymce/tinymce.js'></script>"], "head_end": ["<script src=\"{{root}}<projectfolder>\/<datadefinition>\/<datadefinition>.js\"><\/script>"], "k8form":{ "fields":[ { "fieldwraptemplate": "<div class=\"mb-3\">{{labelelement}}{{fieldelement}}</div>", "name": "memo", "tagName": "textarea", "fieldclass": "form-control tinymce", "label": "memo" } ] } }

In the head of the page you have to add:

• the tinymce library: tinymce/tinymce.js
• the tinymce template: masterdata/templates/tinymce.js

After the declaration of the JavaScript variables at head_end the call back functions are added to the datadefinition: settings. The JavaScript file for this is masterdata/templates/tinymce.js.

In "k8form" the textareas with the class tinymce will get the tinymce editor.

The image list for tinymce is initialized by the init of tinymce. By each record the image_list changes, so tinymce is removed and initialized again.

The tinymce template consist the call back functions for your form. If you need to extend the call back functions or change the configuration, just copy the template in your datadefinition folder and change the link in your datadefinition.

More information about tinymce are in: TinyMCE docs

additional declarations in the datadefinition:

{ "masterdata":{ "upload":{ "enabled":true, "settings":{ "showtext":true, "minImageWidth":1000, "minImageHeight":1000, "dropzone":{ "resizeWidth":1000, "resizeHeight":1000, "resizeMethod":"contain", "maxFilesize":5, "acceptedFiles": ".jpeg,.jpg,.png,.gif", "resize_not_working":false } } } } }

The upload has to be enabled (true). The settings specify the details of the files to be uploaded.

Frontend

the following elements are added into the masterdata plugin:

• Upload area for the image
• Image list with with sorter and control elements

If tinymce is enabled the image list is also displayed and updated in tinymce.

showtext

"true" displays the filename.

minImageWidth, minImageHeight

The min values ​​ensure that a minimum resolution is guaranteed.

resizeMethod

• contain:
  • the image dimensions are scaled down to the specified values.
  • the aspect ratio of the uploaded image is retained
• crop:
  • the image dimensions are croped to the resizedWidth and resizeHeight
  • the aspect ratio corresponds to the resizedWidth and resizeHeight

Backend

by reading the main recordset, the following columns are added:

• image_ID
• image_file
• image_orientation
• image_aspectratio
• image_width
• image_height
• image_count
• image_array
  • array with images, same the columns above

menuleftobj, for more details go to dropdown.

{ masterdata:{ "menuleftobj":{ "button":{ "text":"", "carettype":0 }, "items":[ {"value":"remove","label":"Remove","inlist":false,"inform":false}, {"value":"edit","label":"Edit","inlist":false,"inform":false}, {"value":"insert","label":"Insert from clipboard","atlink":false}, {"value":"businesscard","label":"#ls#Business card#"} ] } } }

displaymodes (true by default):

• inlist (displayed in master data form or list)
• inform (displayed only in form)
• atlist (displayed only in a different form by a link)

Call back function, cbMenuleft:

settingscustomer.masterdata.cbMenuleft=function(options){ var el_md=options.el_md, e=options.e, row_actual=options.row_actual; var el=e.target; var value=getfromArray(el.dataset,"value"); if(value=="businesscard"){ if(options.selected.length!==1){ alert(getl("Please select 1 row")); }else if(options.isDirty){ myk8.message(getl('please, save record'),"alert"); }else{ alert("businesscard"); } }else if(value=="insert"){ alert("insert"); } }

The html snippets define the looking of the master data form. To overwrite the default, you can define your own snippets.

settings.html.masterdata.<property>

• layout
• wrapper
• headline
• filterformwrapper
• tabulatorwrapper
• formwrapper
• main
• footline

Create a folder for the datadefnition, name: <datadefID>. Write the HTML-Files with this names:

<datadefID>_<masterdata>_<property>.html

Please regard in your snippets the classes:

• "masterdata"
• "tabulator"
• "js"
• "js_dirty" for the edit symbol
• "js_middle" for the message by messages mode<>4
• Save button needs to be: type="submit"
• Cancel button: class="js_cancel"

if you place tabulator and form side by side:

{ tabulator:{ "height":"" } }

An example for a side by side form is in "kitsamples/customerleft".

data is only supported in form mode (notabulator:true):

[ {"column 1":"content record 1","column2":"content record 1","column 3":"..."} ]

Requirements for the data definition:

• optionsadditional[]
  • datadefID the source datadefinition need to be declared
  • column_mapping
  • fieldname
  • tabulatorcolumn
  • emptytext
  • defaultclause
• masterdata, sql_statement
  • the column "category" with the title can be joined for catalog output

datadefID:

• The datadefID of the source datadefinition needs to be entered here.

column_mapping:

This example uses an existing data definition as recordsource. But the option tag has this structure, which needs to be served:

1. <option>"text"</option>
2. <option value="value">"text"</option>
3. <option data-value="additional attribute" value="value">"text"</option>

The columns of the data source:

• groupID
• title
• value

With the column mapping the options are filled easily, column_mappping:

1. {"text":"title"}
2. {"value":"groupID","text":"title"}
3. {"data-value":"value","value":"groupID","text":"title"}

emptytext:

• none (default)
• standard (please select)
• your text

An empty text is only shown in mode 1. In mode 0 you get an empty option instead.

defaultclause:

The "defaultclause" allows to filter the data.

Example for the item category:

{ "optionsadditional":[ { "datadefID":"k8groups", "column_mapping":{"value":"groupID","text":"title"}, "fieldname":"categoryID", "tabulatorcolumn":"categoryID", "emptytext":"please select", "defaultclause":"type='customer_group'" } ], "masterdata":{ "sql_derived":"SELECT er_customer.*,concat(accountnumber,' ',name1,' ',city) as partnerdisp,k8login.username as representativename,k8groups.title as category, '' as k8select FROM er_customer LEFT OUTER JOIN k8login ON representativeID=userID LEFT OUTER JOIN k8groups ON categoryID=groupID WHERE 1=1", "sql_orderby":"name1,city" } "tabulator":{ "columns":[ { "title": "categoryID", "field": "categoryID", "headerFilter": "list", "formatter":"#function#k8tabulatorformatter#", "formatterParams":{"type":"color"}, "minWidth":120 } ] }, "k8form":{ fields:[ { "tagName": "select", "name": "categoryID", "label": "categoryID", } ] } }

tabulator categoryID

this parameters need to be set:

• headerFilter:"list", allow to choose from the value list
• formatter:"#function#k8tabulatorformatter#", this formatter displays the text in the rows instead of the values
• formatterParams":{"type":"color"}, defines the type of the formatter

The value lists are added automatically by processing "optionsadditional".

k8form categoryID

The "tagname" is set to "select". This HTML element allows to select from an option list. The option list is filled automatically by processing "optionsadditional".

To link our current form with another data definition we need both data definitions:

• input data definition: our current definition "mycustomer"
• source data definition: where we read the records from: "k8searchuser"

The source data definition will be the definition with the logged in users:

• k8searchusers

The input data definition is linked to the Primary key of the source data defintion. So we need an ID field in the input data definition:

• reprenstativeID

To search for the user the name will be typed in. So we need a second field for the name:

• representativename

The representativename needs to be read by loading the record into our input data definition. Therefore are adoptions necessary in:

• the sql statement
• the tabulator columns

Requirements for the input data definition:

• settingsadditionals[]
  • the source data definition need to be declared
• k8form, fields
  • datalist{}
    • look for the additional parameters

k8form attributes

list:

The "list" is defined like this:

• <field name>_datalist

autocomplete:

Only the results from the datalist should be shown here. Other suggestions for the content are shut off.

k8form datalist

datadefID:

The datadefID of the source data definition needs to be entered here. It has also to be declared in "settingsadditional".

IDfield:

The IDfield is the field which contains the primary key of the source table.

onlyoptions:

• true: only options from the list are allowed
• false: all entries are allowed

If another source table is linked the value must be true. After choosing the value the primary key of the source table is inserted into the "IDfield".

Example for linking the user from the table "k8login":

{ "settingsadditional":["k8searchuser"], "masterdata: { "sql_derived":"SELECT er_customer.*,k8login.username as representativename,'' as k8select FROM er_customer LEFT OUTER JOIN k8login ON er_customer.representativeID=k8login.userID WHERE 1=1"; "sql_orderby":"er_customer.name1,er_customer.city" }, "tabulator": { "columns": [ { "title": "representative", "field": "representativename", "headerFilter": true, "width": "100", "visible": false } ] }, "k8form":{ fields:[ { "name": "representativeID", "type": "hidden", "label": "representativeID" }, { "name": "representativename", "type": "text", "label": "representative", "attributes":{ "list": "representativename_datalist", "data-fieldlist":"", "autocomplete": "OFF" }, "datalist":{ "datadefID":"k8searchuser", "IDfield":"representativeID", "onlyoptions":true } } ] } }

The sql statement better displayed with line breaks:

SELECT er_customer.*,k8login.username as representativename,'' as k8select FROM er_customer LEFT OUTER JOIN k8login ON er_customer.representativeID=k8login.userID WHERE 1=1

Example for data-fieldlist:

• "data-fieldlist":"accountID,accountname=customername,email,phone"

Form elements can be filled with values from the source record.

Requirements for the source datadefintion:

{ "datalistcolumn":"<name>" }

In the source table the column <name> should be a "unique" column. This column is requested in the form.

Attention, not working well when the value is set before Ajax filled the options

Requirements for the data definition:

• settingsadditional[]
  • the source datadefinition need to be declared
• masterdata, sql_statement
  • the column "representative" with the username need to be joined
• k8form, fields
  • dataoptions{}
    • look for the additional parameters

datadefID:

The datadefID of the source datadefinition needs to be entered here. It has also to be declared in "settingsadditional".

mode:

• 0: <option>"datalistcolumn"</option>
• 1: <option value="key">"datalistcolumn"</option>

emptytext:

• none (default)
• standard (please select)
• <your text>

An empty text is only shown in mode 1. In mode 0 you get an empty option instead.

defaultclause:

The "defaultclause" allows to filter the data.

Example for the item category:

{ "settingsadditional":["k8groups"], "masterdata":{ "sql_derived":"SELECT er_customer.*,concat(accountnumber,' ',name1,' ',city) as partnerdisp,k8login.username as representativename,k8groups.title as category, '' as k8select FROM er_customer LEFT OUTER JOIN k8login ON representativeID=userID LEFT OUTER JOIN k8groups ON categoryID=groupID WHERE 1=1", "sql_orderby":"name1,city" } "k8form":{ fields:[ { "tagName": "select", "name": "categoryID", "label": "categoryID", "dataoptions":{ "datadefID":"k8groups", "mode":1, "emptytext":"standard", "defaultclause":"type='customer_group'" } } ] } }

Requirements for the source datadefintion:

{ "datalistcolumn":"<column name>" }

In the source table the column <name> should be a "unique" column. This column is requested in the form.