Using Select and HtmlSelect Widgets

We already have an example table with Commission ID and Employee ID fields pointing to their children tables which is not very practical. Much better option is to replace ID fields with a drop-down lists from where a user can choose the right value. The Select widgets are intended for this purpose. In this chapter we will demonstrate the select and the htmlselect widgets which show more straightforward drop-down lists. There is also the divselect widget that shows more complex drop-down lists which will be described in the next chapter.

On this page:

Defining Parent's Fields

First let's change names of ID fields:

<%method Select_FIELDS_DESCR>Price, Commission, Employee</%method>

Both select lists shown in this example also need the Select_SELECT_FIELDS method which determines the fields of the parent table:

<%method Select_SELECT_FIELDS>price, commission, employee</%method>

The select, htmlselect and divselect widget use _Select __ suffix to change standard SQL library methods.

Defining Children's Fields

Format Of Parent Drop-down List

In order to correctly show the children's field values in the parent drop-down lists we must define the display format with the Select_SELECT_FIELDS method.

We create the commissions.mql file in the /opt/demo_masonsql/htdocs/data/demo/ directory and add the Select_SELECT_FIELDS method with the format string which will be used in the parent's commission field:

<%method Select_SELECT_FIELDS>percentage || '% ' || type</%method>

We create the employees.mql file in the /opt/demo_masonsql/htdocs/data/demo/ directory and add the Select_SELECT_FIELDS method with the format string which will be used in the parent's employee field:

<%method Select_SELECT_FIELDS>name || ' ' || surname || ' - ' || city</%method>

Note that the SQL string concatenation operator || is used for joining parts into one format string.

NULLs and Aggregate Expressions

Additionally we need to prevent searching NULL values in the selected fields with FIELDS_NOT_NULL method. This is important because a query where at least one of the values is NULL would return NULL result. Most PostgreSQL Aggregate Expressions ignore NULL inputs, so that rows in which one or more of the expression(s) yield NULL are discarded.

We add the displayed list of child's fields to the commissions.mql file:

<%method FIELDS_NOT_NULL>percentage, type</%method>

We add the displayed list of child's fields to the employees.mql file:

<%method FIELDS_NOT_NULL>name, surname, city</%method>

SQL NULL

NULL in SQL represents a missing value. A NULL value does not occupy space in memory. A table can have many columns but these do not take up memory if they contain NULL. The most useful functions for handling NULLs in PostgreSQL are COALESCE, NULLIF and COUNT. Handling NULLs in SQL can at times get tricky. A helpful overview of this subject is available on Wikipedia under the title: Null (SQL).

Adding Select Field

We add a simple drop-down list for the commission field using commission_FIELD method combined with GenericSelect_FIELD method:

<%method commission_FIELD>
  <& SELF:GenericSelect_FIELD,
     widget => 'select',
     buffer => 'commissions', 
     from => 'demo/sel_commissions', 
     %ARGS &>\
</%method>

In order to show contents of the child table we must set the from to refer to the child table demo/sel_commissions.
Notice that the widget parameter is set to 'select' because if missing is set to the default value htmlselect.

We get the select drop-down list:

Notice the the drop-down list is displayed in the format that we defined in the commissions.mql file.

Adding HtmlSelect Field

We add a bit more compulsive drop-down list for the employee field using employee_FIELD method combined with the GenericSelect_FIELD method:

<%method employee_FIELD>
  <& SELF:GenericSelect_FIELD, 
     from => 'demo/sel_employees', 
     width => 200, 
     %ARGS &>\
</%method>

In order to show contents of the child table we must set the from to refer to the child table demo/sel_employees.

We get the htmlselect drop-down list:

Notice the the drop-down list is displayed in the format that we defined in the employees.mql file.

_FIELD Methods

As shown in the examples from previous chapters each field can also be uniquely configured. Here's the list of the FIELD methods:

Method Name	Attribute/Parameter Name	Description	Example Usage
_ALIAS		A field name alias.	<%method id_referente_ALIAS>id_socio</%method>
_CHECK		The method that verifies the specified field. It can be <field_name>_CHECK or <alias>_CHECK
_FIELD		A _FIELD method determines how the field should be displayed.	Examples of _FIELD method usage: Adding Field Method, Adding Select Field, Adding HtmlSelect Field, Adding DivSelect Field, Adding Child Frame There are also a couple of examples of _FIELD methods in the global_autohandler file.
	select_name_prefix
	select_from	The select_from is used for setting additional parameters to a select widget to optimize data transfer from server to the browser.
_OUT_FILTER		If the method is defined, applies the filter to the output of the specified field. Returns an array with two fields (value, parameter).
	$field	A field name.
	$key	A primary key value.
	$value	A field value.
	$names	An array of the field names.
	$fetch_row	A hash of the field values.
	$comp	A component (.mql), if not defined using the basic component.
	$noparam	If set to 1, it does not retrieve the parameters (used to optimize in cases where they are not used)
_NAME_PREFIX
_FROM