Adding Child Frame

In this example we will display a table together with one linking table.
We will also do some improvements of the input fields.

On this page:

String Length

The string length can be defined in the .mql file or preferably directly in the database.

Defining String Length in MQL File

A string length can be restricted with the length parameter of the string.comp file;
Some examples of length parameter are in the commissions6.mql file:

<%method type_FIELD><& /input/string.comp, %ARGS, length => 15 &></%method>
<%method description_FIELD> <& /input/string.comp, %ARGS, length => 40 &> </%method>

Defining String Length in Database

A better way to restrict a string length is by making the restriction directly in the database with using varchar.

Instead of:

<%method name_FIELD><& /input/string.comp, %ARGS, length => 15 &></%method>
<%method surname_FIELD><& /input/string.comp, %ARGS, length => 15 &></%method>
<%method address_FIELD><& /input/string.comp, %ARGS, length => 30 &></%method>
<%method city_FIELD><& /input/string.comp, %ARGS, length => 15 &></%method>
<%method country_FIELD><& /input/string.comp, %ARGS, length => 15 &></%method>

We change the string fields in the employees table to the character varying type with defined max length:

ALTER TABLE demo.employees ALTER COLUMN  name TYPE character varying(15);
ALTER TABLE demo.employees ALTER COLUMN  surname TYPE character varying(15);
ALTER TABLE demo.employees ALTER COLUMN  address TYPE character varying(30);
ALTER TABLE demo.employees ALTER COLUMN  city TYPE character varying(15);
ALTER TABLE demo.employees ALTER COLUMN  country TYPE character varying(15);

The string widget inherits the length property from the database so the displayed field lengths are identical:

Father Table, Child Table

The father-child relationship involves the frame.html and reflects primary-foreign key relationship between two tables.
Its important to understand that the table with foreign keys is a child table and the table with a primary key in the relationship is a father.
The frame.html file is important because it builds the frames. A father can have one or more child tables. Each child is in its own tab.

Configuring Father MQL File

In example we will add these methods to create a new child frame:

Method Name	Description	Example Usage
CHILDREN	Is a reference list of child tables. The number of child tables should be the same as the number of tabs defined with the FIELDS_NEW method.	<%method CHILDREN>gruppi_anagrafiche, gruppi_modifica</%method>
CHILDREN_FIELDS	Each field is (primary) key from father table corresponding to the foreign key in the child table.	<%method CHILDREN_FIELDS>id, id</%method>

In the example file employees6.mql we'll add one child table:

<%method CHILDREN>orders4</%method>
<%method CHILDREN_FIELDS>id</%method>

The father-child page in this configuration would look like this:

Configuring Child MQL File

In the example we will add these methods to the child table:

Method Name	Description	Example
FATHER	Provides field:father_table[:id] parameters for the dbms_library.comp: The field parameter is extracted internally by the FATHER_ID_NAME method into the $father_id_name. The father_table parameter is extracted internally by the FATHER_NAME method into the $father_name. The id parameter is extracted internally by the FATHER_KEY_NAME method into the $father_id. When the FATHER method doesn't contain the id parameter, the FATHER_KEY_NAME method tries to load it from the KEY method from the father table. And if the $father_id can't be found in the father.mql the dbms_library.comp also tries to obtain it from the arguments.	<%method FATHER>employee:employees</%method>
FIELDS	The list of fields from a table to be queried. By default, when the FIELDS method is not overloaded, all of the fields from a table are included.	<%method FIELDS>price, commission, employee</%method>

Since we want to upgrade the orders4.mql file we create a new file for the linking table and properly name it orders_employee.mql.

Consequently the CHILDREN method in the employees6.mql must be changed to:

<%method CHILDREN>orders_employee</%method>

We want to choose a row in the father table which automatically refreshes the child table with the orders made by the chosen employee.

This can be achieved by setting the key value of the FATHER method in orders_employee.mql:

<%method FATHER>employee:employees</%method>

When adding the FATHER method to the orders_employee.mql file and trying to display the Employees (6) example we get an error message:

SERVER: No father retrieve permission.

The message says that the father table employees, has no access permissions to query the database.
The behaviour is currently a shortcoming of the FATHER method. We fix it by adding the permissions to the employees table:

We have the complete employee row displayed in father table, we don't need its information any more in the child table, so we want to overload previously defined methods inherited from orders3.mql with:

<%method FIELDS>price, commission</%method>
<%method FIELDS_NOT_NULL>price, commission</%method>
<%method FIELDS_DESCR>Price, Commission</%method>

In the final result a user can select the employee in the father table and see the resulting orders of the employee in the child table:

Both tables can also additionally be edited.