GM-X ERP for Blockchain - User Defined Fields (UDF)

By Tony Marston

Created: 2nd September 2022
Amended: 19th May 2023

Introduction
Entity-Relationship Diagram
Table Structures
The Problem
The Solution
New Class Methods
_udf_getColumnSpecs()
_udf_addFieldsToScreen()
_udf_adjustQuery()
_udf_getColumnValues()
_udf_setSearch()
_udf_updateFields()
build_colspecs_file()
New Classes
<TABLE>_EXTRA_NAMES
<TABLE>_EXTRA_NAME_OPTIONS
<TABLE>_EXTRA_values
Making Manual Adjustments
Amendment History

Introduction

As well as the standard fields in the core database tables, the GM-X application also has the ability for the user to define additional fields for certain tables, then to assign values for entities in those tables. In order to provide this ability the current convention is to have the following additional tables in the core database:

XXX_EXTRA_NAMES - to identify the name and data type of any user-defined fields
XXX_EXTRA_NAME_OPTIONS - to identify options when extra_type is 'DROPDOWN'
XXX_EXTRA_VALUES - to hold values for these named fields.

The value XXX denotes the core table. The following tables are currently supported:

INVOICE subsystem
FINANCE_AP subsystem
ORDER subsystem
PARTY subsystem
PRODUCT subsystem
REQUEST subsystem

Note that in the REQUEST subsystem that QUOTES are derived from REQUESTS, therefore they share the same set of user-defined names.

Entity-Relationship Diagram

Figure 1 - Entity-Relationship Diagram

<TABLE> is the core table in the GM-X database.

<TABLE>_EXTRA_NAMES identifies the extra field names.

<TABLE>_EXTRA_NAME_OPTIONS identifies the options when the extra_type is 'DROPDOWN'.

<TABLE>_EXTRA_VALUES identifies the values entered for each row in that table.

Table Structures

<TABLE>_EXTRA_NAMES table

This identifies the extra column names that may be added to a database record.

Column Name	Type	Description
extra_id	string	Identifies the field name for a user-defined value. This should NOT be the same as a field name in the associated core table.
extra_name	string	A user-friendly name for this field.
extra_desc	string	A longer description for this field.
extra_type	string	Identifies the data type for all values for this field. Options are: String - free-format text Date - a valid date Boolean - a Yes/No flag Number(n) - a number with 'n' digits and no decimals Number(n,d) - a number with 'n' digits and 'd' decimals Dropdown - a dropdown list where the options are obtained from the XXX_EXTRA_NAME_OPTIONS table
is_required	boolean	A Yes/No flag. If YES then a value must be supplied.
sort_seq	numeric	Identifies the order in which the fields should appear on the screen..

Note that additional fields mat be added if required, such as the following:

Column Name	Type	Description
applies_to	string	This limits the availability of this extra column. Examples are: In the PARTY subsystem, Organisations, People, or Both In the REQUEST subsystem, Requests, Quotations, or Both
is_exportable	boolean	Indicates if this field can be included in a CSV export file.

<TABLE>_EXTRA_NAME_OPTIONS table

This identifies the options when the extra_type is 'DROPDOWN'.

Column Name	Type	Description
extra_id	string	Links to an entry on the <TABLE>_EXTRA_NAMES table.
option_id	number	Option value assigned by the system.
option_name	string	Description of the option value displayed in dropdown lists.
sort_seq	number	Sort Sequence. A numeric value denoting the sequence in which this option appears in dropdown lists.

<TABLE>_EXTRA_VALUES table

This identifies the options when the extra_type is 'DROPDOWN'.

Column Name	Type	Description
<primary_key>		Same as the primary key of the core table to which it is attached.
extra_id	string	Links to an entry on the XXX_EXTRA_NAMES table.
extra_value	string	This holds the value for this Extra Name with this entry on the core table.

The Problem

As these tables are in addition to the core tables each has its own set of maintenance tasks. The EXTRA_NAMES table has a set of tasks which start with a LIST1 screen while the EXTRA_VALUES table starts with a LIST2 screen where the outer entity is the core table for which it holds extra values. In some cases there may be an UPDATE3 screen which shows, for a selected object, all the EXTRA_NAMES entries plus the current value for each of those entries. This allows all the entries to be viewed and input or amended on a single screen. Any name without a value will be automatically deleted from the EXTRA_VALUES table.

It is also possible to provide a custom version of a standard screen which includes a selection of user-defined fields as well as the core fields. As well as a custom screen this also requires custom processing to both retrieve values from the EXTRA_VALUES table and to update them afterwards. A current example can be found in the PRODUCT subsystem where extra fields for LENGTH, WIDTH, HEIGHT and WEIGHT are shown in the detail screens.

A comment has been received from some clients who have made use of the existing ability for user-defined fields that it is not very user-friendly as it requires too many keystrokes to view and update these extra fields. They would like to see any user-defined fields to automatically appear in the relevant detail screens so that they can be viewed and maintained along with the core fields. They would also like to be able to search on any of these user-defined fields.

The Solution

In order to make the handling of user-defined fields as easy as possible it has been decided that for any entity which has entries on its EXTRA_NAMES table these should automatically be displayed on that entity's DETAIL screens, those which show a single row. These are provided by the following patterns:

ADD1 and ADD2
ENQUIRE1
DELETE1
UPDATE1
SEARCH1
LIST1/2/3/4 screens after returning from a SEARCH screen.
MULTI1 (eg: to update ORGANISATION or PERSON data)
OUTPUT1 and OUTPUT4 (CSV export)

In order to minimise the amount of extra coding in those table classes which are affected by this change the majority of code has been inserted into framework components as follows:

A new set of methods had been created in script includes/include.user_defined_fields.trait.inc which takes advantage of a recent addition to the PHP language called TRAITS. This allows additional methods to be added into a class to supplement those which are inherited from the abstract class.
The existing abstract table class includes/std.table.class.inc has been modified to call the new methods in the appropriate places.
The existing classes includes/std.extra_names.class.inc and includes/std.extra_values.class.inc which provide standard code for any EXTRA_NAMES and EXTRA_VALUES table classes have been amended to assist in the new functionality. This includes ensuring that any field name in the extra_name column has any hyphens ('-') replaced with underscores ('_') as SQL will treat each hyphen as a mathematical minus symbol.
The Data Dictionary has been amended to include four new optional columns which identify when a standard table has associated EXTRA_NAMES and EXTRA_VALUES tables. This is to avoid any manual coding in those classes which are affected by the functionality.

This approach therefore requires the minimum of effort to make this new functionality available in those table classes which are affected. There are only two steps:

Use the Data Dictionary to update the table's data to provide the following:
- Provide the database name and table name for the EXTRA_NAMES table.
- Provide the database name and table name for the EXTRA_VALUES table.
Update the table class to include the methods in the trait using the following lines of code which are highlighted in yellow like this:
```
require_once 'std.table.class.inc';
require_once 'include.user_defined_fields.trait.inc';
class XXXX extends Default_Table
{
    use user_defined_fields;  // include methods from this trait
    ...
```
Without these extra lines of code the relevant _udf_XXX methods cannot be called.

New Class Methods

The following methods are available in each table class which has included the trait for user-defined fields mentioned previously.

_udf_getColumnSpecs()

In order to be processed as normal fields the relevant entries must be added to the standard $fieldspec array which is populated from the relevant <table>.dict.inc file which is produced from the Data Dictionary. This requires a procedure to extract all the field names from the EXTRA_NAMES table and then build the relevant entries which can be added to the $fieldspec array. Rather than reading from the database, each time the table class is loaded the processing inside the EXTRA_NAMES class has been amended to create a separate file called classes/custom-processing/<project_code>/<table>_extra_names.colspecs.<project_code>.inc to contain this information. This file will automatically be rewritten each time a record is added, updated or deleted from the EXTRA_NAMES table.

Each time the core table is instantiated into an object this procedure will automatically be called before the _cm_changeConfig() method in order to load the contents of the colspecs file. Note that this file may not exist or may even be empty. This data will be loaded into $this->udf_fieldspec as well as being appended to the standard $this->fieldspec array.

_udf_addFieldsToScreen()

The standard <table>.<pattern>.screen.inc files produced by the Data Dictionary, which may also be customised later, do not by default contain any of the fields identified in the EXTRA_NAMES table. As this file is loaded into memory at the start of each task before it is processed by the $view->buildXML() method at the end of the task there is ample time to amend the version in memory before it is processed.

This method will automatically add references to any user-defined fields into the current screen structure. These will be inserted before any refence to created_date. The value in the extra_name column will be used as the field label.

Note that if any of these fields has already been added to a customised screen structure file then it will not be duplicated.

_udf_adjustQuery()

Instead of reading the contents of the EXTRA_VALUES table in a separate operation after the call to the standard getData() method this procedure will automatically adjust the SQL query so that it will read all the data in a single combined operation. It will do this by updating the SELECT string of the database query to include the following for each user-defined field:

SELECT extra_value FROM $udf_tablename AS UDF 
                   WHERE $link 
                   AND UDF.extra_id='$extra_id') AS $extra_id

$udf_tablename is the name of the EXTRA_VALUES table.

$link is where the primary key of the core table to the primary key of the EXTRA_VALUES table.

$extra_id is the name of the user-defined field. Note that this name must be a valid SQL name.

_udf_getColumnValues()

In some circumstances it may not be possible to adjust the SQL query as outlined above, so this method will access the EXTRA_VALUES table in a separate operation and add any values found to the current result set.

_udf_setSearch()

The search screen will now include any user-defined fields if they exist for that table. However, upon returning to the LIST screen the search criteria will have to be adjusted otherwise the resulting query will be invalid and will cause a fatal error. This method will then examine the search string and where it finds

extra_id='extra_value'

with

EXISTS(SELECT 1 FROM $udf_tablename 
                WHERE $link 
                AND extra_id='$extra_id' 
                AND extra_value='$extra_value')

$udf_tablename is the name of the EXTRA_VALUES table.

$link is where the primary key of the core table to the primary key of the EXTRA_VALUES table.

$extra_id is the name of the user-defined field. Note that this name must be a valid SQL name.

$extra_value is the value entered in the search screen.

_udf_updateFields()

During any amendment to the core table, either through an insert, update or delete operation, the data array may contain entries on the associated EXTRA_VALUES table. This method will identify and extract any references to the user-defined fields, then compare any new value will the current value and perform the appropriate insert/update/delete operation. Note that if a value is changed to NULL the corresponding database record will be deleted.

build_colspecs_file()

This method has been added to script includes/std.extra_names.class.inc in order to recreate the colspecs file (refer to _udf_getColumnSpecs() above) each time an insert, update or delete operation is performed on an EXTRA_NAMES or EXTRA_NAME_OPTIONS table. This file will contain additions to the standard $fieldspec array as well as the $lookup_data array.

New Classes

To create the classes for the additional tables you will need to do the following in order to inherit the pre-written code:

<TABLE>_EXTRA_NAMES

Change the initial lines to the following:

//require_once 'std.table.class.inc';
//class <table>_extra_names extends Default_Table
require_once 'std.extra_names.class.inc';
class <table>_extra_names extends Extra_Names

<TABLE>_EXTRA_NAME_OPTIONS

Change the initial lines to the following:

//require_once 'std.table.class.inc';
//class <table>_extra_name_options extends Default_Table
require_once 'std.extra_name_options.class.inc';
class <table>_extra_name_options extends Extra_Name_Options

<TABLE>_EXTRA_values

Change the initial lines to the following:

//require_once 'std.table.class.inc';
//class <table>_extra_values extends Default_Table
require_once 'std.extra_values.class.inc';
class <table>_extra_values extends Extra_Values

Making Manual Adjustments

There may be circumstances where the standard processing for user-defined fields needs to be adjusted. I have encountered several such areas in my development and testing of this new functionality:

The INVOICE subsystem has EXTRA_NAMES and EXTRA_VALUES tables for both INVOICE_HEADERS and INVOICE_ITEMS. The same database is also shared by the FINANCE_AP and FINANCE_AR subsystems which is why the SUPP_INVOICE_HEADER/ITEM and CUST_INVOICE_HEADER/ITEM classes inherit their shared code from the classes in the INVOICE subsystem. However, the FINANCE_AP has its own set of EXTRA_NAMES and EXTRA_VALUES tables. In order to switch to these alternative tables all that is necessary is to amend the constructor of the FINANCE_AP classes to the following:

    function __construct ()
    {
        parent::__construct();
        // switch directory name for the retrieval of message text
        $this->dirname = dirname(__file__);
        // switch table names for extra_names and extra_values
        $this->nameof_extra_names_db   = 'finance_ap';
        $this->nameof_extra_names_tbl  = 'supplier_invoice_extra_names';
        $this->nameof_extra_values_db  = 'finance_ap';
        $this->nameof_extra_values_tbl = 'supplier_invoice_extra_values';
    } // __construct

In the PARTY subsystem there is a single set of EXTRA_NAMES and EXTRA_VALUES tables which are shared by both ORGANISATIONS and PERSONS, which are identified by the value in the party_type column on the PARTY table. However, some of the values apply to only one of these party_types, and some of them need to be excluded from any CSV export process. This requires code similar to the following in the _cm_changeConfig() method of the ORGANISATION and PERSON classes:

    if (!empty($this->udf_fieldspec)) {
        // hide entries which do not apply to this party_type
        foreach ($this->udf_fieldspec as $field => $spec) {
            if (!empty($spec['party_type']) AND !empty($fieldarray['party_type'])) {
                if ($fieldarray['party_type'] == 'ORG' AND preg_match('/^(ORG|ALL)$/i', $spec['party_type'])) {
                    unset($this->fieldspec[$field]['nodisplay']);  // unhide this entry
                } elseif ($fieldarray['party_type'] == 'PER' AND preg_match('/^(PER|ALL)$/i', $spec['party_type'])) {
                    unset($this->fieldspec[$field]['nodisplay']);  // unhide this entry
                } else {
                    $this->fieldspec[$field]['nodisplay'] = 'y';   // hide this entry
                } // if
            } // if
        } // foreach
    } // if

In the REQUEST subsystem there is a single set of EXTRA_NAMES and EXTRA_VALUES tables which are shared by both REQUESTS and QUOTATIONS, which are different tables. However, some of the values apply to only one of these tables. This requires code similar to the following in the _cm_changeConfig() method of the REQUEST class:

if (!empty($this->udf_fieldspec)) {
    // remove entries which do not apply to this table
    foreach ($this->udf_fieldspec as $field => $spec) {
        if (!empty($spec['applies_to'])) {
            if (!preg_match('/^(R|B)$/i', $spec['applies_to'])) {
                unset($this->udf_fieldspec[$field]);
                unset($this->fieldspec[$field]);
            } // if
        } // if
    } // foreach
} // if

This requires code similar to the following in the _cm_changeConfig() method of the QUOTE_HEADER class:

if (!empty($this->udf_fieldspec)) {
    // remove entries which do not apply to this table
    foreach ($this->udf_fieldspec as $field => $spec) {
        if (!empty($spec['applies_to'])) {
            if (!preg_match('/^(Q|B)$/i', $spec['applies_to'])) {
                unset($this->udf_fieldspec[$field]);
                unset($this->fieldspec[$field]);
            } // if
        } // if
    } // foreach
} // if

Date created: 2nd September 2022

Amendment history:

19th May 2023

Added the <TABLE>_EXTRA_NAME_OPTIONS table.