Advanced techniques

1. Overview
2. Installation Guide
3. Configuring your site
4. The NeXTensio List
5. Editing a NeXTensio list
6. Advanced techniques
7. Record sorter & counter
8. The NeXTensio Form
9. Editing a NeXTensio form
10. Deleting a NeXTensio Form
11. Conclusions

Advanced techniques for creating NeXTensio Lists

Creating dynamic filters

In our initial NeXTensio List generation, we have decided not to include in the list, fields that are actually foreign keys to other tables. However, this is a very used case as every normalized database should use foreign keys extensively to avoid duplicate information.

If we would want to add a foreign key field to the NeXTensio list, you could simply select the table field and add it to the list. Unfortunately, foreign key fields are usually numbers and they are not very "human-friendly".

A solution would be to create the NeXTensio list as it is, and then edit the recordset to do a left join to the related table – but this is a pretty long process and requires a lot of SQL knowledge.

NeXTensio includes a simple way of creating a filter and the join automatically for you. To instruct the NeXTensio List GUI that the listed table include a foreign key to another table, you have to set its filter type to SELECT, and make it dynamic from a recordset table.

Let's proceed create a simple page that will list the products with their categories. First we'll create in the products/ folder of the site root the findex.php file.

Open it and click on Create NeXTensio 2 List.

Select the products_prd table, make it ordered after the order_prdfield and remove all List Fieldsexcept for idctg_prd, name_prdand price_prd.

Select theidctg_prdfield in the grid, and then choose "SELECT" in the Display Asmenu in the Filterssection (the lower side of the GUI).

Two buttons will be displayed below the menu – Menu Propertiesand Add Recordset, like in the following image.

We will now add a new recordset with all the categories from the database, by clicking on the Add Recordsetbutton.

Then we will click on theMenu Propertiesbutton and we'll configure the filter to be a dynamic dropdown field that will show the name_ctg field as the label and will use the id_ctgfield as the ID.

After generating the NeXTensio List, the page will look like this.

As we can see, the idctg_prd(we've forgot to rename this) column has as filter a dropdown menu.

Looking at the generated SQL query, we can see that a left join has been automatically added to the current SQL.

Note:the NeXTensio Dropdown filter can also be used for hierarchical data structures where we edit a table that has a foreign key to itself (like a hierarchy of categories).

Finally let's take a look at the generated page loaded in a browser.

As we can see, theidctg_prdfield (numeric) is not shown. Instead, the label from the categories_ctgtable is shown in the NeXTensio List.