Technote Details :: NeXTensio List from recordset issues

When creating a NeXTensio list from a recordset that contains a JOIN operation, you must select the correct primary key to avoid records with NULL values.

This happens because in a JOIN operation there are two tables involved, each with its own primary key. The primary key that you choose in the NeXTensio List Wizard user interface must be the primary key for the first table in a LEFT JOIN operation, as it is the one passed to the NeXTensio Form for processing data (e.g. update and delete).

For example, when creating a NeXTensio list on a recordset that joins the employee and departments tables (employee_emp LEFT JOIN department_dep), you must set the list's primary key to be the employee table one. This way, when a record is deleted, it will remove the employee, and not the department. If the department is removed, then the employee will still show in the list, but having the NULL value for its department.

In order to avoid such an issue, make sure you always select the correct list primary key.

As a rule of thumb, when using such recordsets (which use LEFT JOINs), the list primary key should be the first table's primary key.

Login to add a new note