Master detail

{ "name": "Customer", "settingsadditional":["employee"], "head_end":["<script src=\"{{root}}kitsamples/customermaster/customermaster_head_end.js\"></script>"], }

In the settingsadditional the second datadefinition for the employees is added.

The head_end adds the JavaScript file which is used to trigger the detail element.

customermaster_head_end.js

SetArrayAll(...)

The header filters are set to false. The list is displayed without header filters.

pagesettings[]

This is an array with the detail elements:

• settings: the datadefinition
• mode: "masterdata","catalog","chartjs"

cbAfterNew

The showMasterDetail() function is called with the key=-1. The result is an empty detail list.

cbAfterLoad

The showMasterDetail() function is called with the accountID and the "rightuser_update". The corresponding employees are displayed. Without the update right of the master no new records can be added to the detail. Without the "rightuser_update" the controls "edit" and "delete" are hidden in the detail list.

Function showMasterDetail(...)

• el_md: the detail element is appended to this HTML element
• submode:
  • masterdata
  • catalog
  • chartjs
• settingssub: the datadefinition of the detail
• ID: the accountID of the master is the default value for the detail
• rightuser_create: set to rightuser_update of the master

The detail element is created new by each time the function is called.

Data definition: customermaster.json

{ "backend":{ "foreigndeletions":[ { "table":"er_employee", "childkey":"accountID" } ] } }

The data access class will delete all records in the declared table where the childkey is equal to the key of the main table (er_customer).

{ "masterdata":{ "include":{ "beforedelete":"kitsamples/customermaster/customermaster_beforedelete.php" } } }

With the include file of the event "beforedelete" the corresponding records of the detail table are deleted with a PHP function. Here the PHP file to include is declared.

customermaster_beforedelete.php

<?php $sql = 'DELETE FROM er_employee WHERE accountID=?'; $stmt = $this->db->prepare($sql); $stmt->bind_param('i', $ID); $stmt->execute(); $subresult = $stmt->affected_rows; //mylog(array('$subresult',iif($subresult,'true','false')),2); $stmt->close();

This event is triggerd in the PHP backend, when a master record is deleted.

The variable "ID" contains the accountID value. With the SQL-Statement the corresponding records in the detail table are deleted too.

DELIMITER $$ CREATE TRIGGER after_customer_delete AFTER DELETE ON er_customer FOR EACH ROW BEGIN DELETE FROM er_employee WHERE accountID = OLD.accountID; END$$ DELIMITER ;

First the delimiters needs to be changed.

The trigger is build on the table er_customer with the event "AFTER DELETE".

For each deleted row the statements between BEGIN and END are executed.

The records with the condition: accountID = OLD.accountID are delete in er_customer.

At least the delimiters are changed back.

{ ... "name": "Employees", "masterdatadefID":"customermaster", "masterkey":"accountID", "masterdata":{ "edittype":3, "bnoform":true, "rights":{ "create":{ "2":2, "3":1000 }, "read":{ "2":2, "3":1000 }, "update":{ "2":2, "3":1000 }, "delete":{ "2":2, "3":1000 } } }, "tabulator": { "columns": [ ... { "title": "accountID", "field": "accountID", "headerFilter": true, "hozAlign": "right", "width": "80", "visible": false } ] }, "k8form": { "fields": [ { "name": "accountID", "type": "hidden" } ] } }

masterdatadefID

The CRUD rights depend on the master data definition.

masterkey

The masterkey is the detail column linked to the primary key of the master table.

edittype

The edittype is set to "3" for displaying the employee form as overlay.

bnoform

bnoform is set to true for displaying only the tabulator list.

rights

The method 2 (superuser) grants all rights to the superuser.

The method 1000 points to the master table. If the update right is granted for the master the CRUD function are granted in the detail table.

tabulator: accountID

The accountID is set to "visible":false.

k8form: accountID

The accountID is set to "type":"hidden".