List, sort and filter orders

In this section of the tutorial you will build the pages that will display the order list and details. Before continuing with the page creation, it is recommended that you read and understand the way a master-detail relation can be implemented using NeXTensio in the How to build master-detail lists and forms tutorial.

The first page to create is the master NeXTensio list, displaying the basic order details from the order_ord table. To complete it, follow the next steps:

Open the admin/index page in Dreamweaver.
Start the Create NeXTensio List Wizard from the MX Kollection tab of the Insert bar. The wizard is divided into several steps which you have to configure in order to create the list.
In the first step of the wizard, you have to configure the data source information:

The list will be generated using a table, and not a recordset.
Select the connection defined at the beginning of this tutorial.
Set the table used to retrieve data from as order_ord.
The primary key column is id_ord. If you've followed the tutorial so far, the sesid_ord column might be selected due to the user interface persistence.
Also in the first step you have to specify which file will store the NeXTensio form, as well as the number of records to display on page.

In the second step of the wizard you have to define what columns will be displayed from the selected table and what values to show. If you need to display the actual label for a foreign key column (e.g. the idusr_ord column), simply use the look-up table option, to automatically create the JOIN operation and display the correct label.

Remove the sesid_ord field from the grid by selecting it and clicking on the Minus (-) button.
Set the idusr_ord field to use a look-up table. In the Database table drop-down menu select the user_usr table, for the Label column the name_usr column, and for the Value column the id_usr column. By doing this, the user name is displayed instead of the ID.
Do the same for the idsta_ord column, but use the status_sta table and the name_sta column for the label.
Also change the labels for the idusr_ord and idsta_ord fields, by replacing the existing ones with User and Status.

The third step of the wizard allows defining the filter elements to use for each of the list columns. For columns using look-up tables, the element is automatically set to menu and the dynamic content is based on the same table as the one selected in the wizard's second step. You do not need to modify anything in this step, so simply skip it.
The last wizard step offers the possibility to set list settings: the ordering column, skin, navigation and button options.

In the Default order drop-down menu select the date_ord column, and set it to be ordered Descending. This way, new orders will appear first in the list.
Set the skin of your choosing by clicking the Change skin button and selecting from the drop-down menu the desired one. In the images below, the Aqua skin has been used.
Leave the rest of the options at their default values and click Finish to close the wizard.

When you preview the page in the browser, it will display the first 10 orders and some additional action buttons: add new, edit and delete. Only edit and delete are of use to administrators, as they should be able to edit (change the status) or delete (remove) an order. An administrator must not be able to create a new order. Therefore, in Dreamweaver, select the add new dynamic link and its associated drop-down menu, and delete them.
Back in Dreamweaver, before the edit link (displayed on each row), enter the 'details' text. Select it and right -click on it. From the pop-up menu select the Make link option. Set it to point to the detail_order page. The link will pass two URL parameters:

The id_ord parameter, with the value retrieved from the id_ord field of the List recordset.
The KT_back parameter, with the value 1. The KT_back parameter is used by the NeXTensio list and form to ease navigation between the two. It is in fact an array of pages that is passed through a session variable. The value 1 indicates the place in the array that stores the page of the original list.

To make the link use the same properties as the edit and delete (show as buttons and skin properties), set the new link's class to KT_link. To do so, select the details link and switch to code view. Enter the following code after opening the <a> tag corresponding to the last URL parameter: class = "KT_link".
Save the page and upload it to the server. If you preview the page, the details link will appear next to edit and delete, and will look the same - it uses the same properties.

The next page to create is the detail list for the orders. This will also be implemented using NeXTensio. To create the page, follow the next steps:

Open the detail_order page in Dreamweaver.
Start the Create NeXTensio List Wizard from the MX Kollection tab of the Insert bar. Configure each step of the wizard as shown below.
In the first step, select the database connection defined at the beginning of this tutorial and the database table to retrieve records from - order_product_opr. Also, enter a name for the file containing the form. The actual form will not be created, as an order's contents should not be modified.
In the second step you can define the table columns to retrieve and display in the NeXTensio list. There are three available fields: idord_opr, idprd_opr and quantity_opr. Configure each field as follows:

Remove the idord_opr field. It will not be used in the list.
For the idprd_opr field select the Look-up table option. In the table drop-down menu, select the product_prd table, and in the label drop-down menu select name_prd. Also change the label to Product.
The quantity_opr field can remain at its default options.

The filter is automatically configured in step three - with the idprd_opr field set as a menu and the quantity field as a text field. So you can move right to the last step.
In the last step, select the Default order column as idprd_opr to order the list by the product ID. Leave all other options at their default values. Click the Finish button to close the Create NeXTensio List Wizard and add all elements to the page, both HTML and code.

As you can see in the image above, the list displays the edit, delete and add new buttons. For the order detail, none of these are relevant, so you can delete them all. Also delete the drop-down menu next to the add new link, in the form footer.
In place of the form footer elements, add a link that will allow administrators return to the master list. Since you are using a master-detail construct with NeXTensio, the back list must be created in the correct manner: by using the NeXTensio back page (read below).
To create the link, type its text in the page (e.g. Back to master list). Select the text and right-click it. From the pop-up menu select the Make link option and set it to point towards the ../includes/nxt/back.php page (if using a different server model, the file extension is different). Also set it to pass the KT_back URL parameter with the value -2 (similar to step 8 above).
As shown in How to build master-detail lists and forms, using a file for link is not the only step you must take. The class that handles links using the KT_back parameter must be available to the page. To make it available, you have to include the includes/nxt/KT_back class file in your page. Insert the corresponding source line at the beginning of the page (code view).
For PHP_MySQL, the code would look similar to the following:

// Load the KT_back class
require_once('../includes/nxt/KT_back.php');

For ColdFusion:

For ASPVBScript:

Now you have to filter the list. By default, it will display all details for all orders and not only for the one selected in the master list. To filter the list, you must filter its recordset:

Double-click the ListRecordset from the Bindings panel. This will open the Recordset dialog box, in Advanced view.
The SQL code until now should look similar to the following:

SELECT product_prd.name_prd AS idprd_opr, order_product_opr.quantity_opr, order_product_opr.id_opr
FROM order_product_opr LEFT JOIN product_prd ON order_product_opr.idprd_opr = product_prd.id_prd
WHERE NXTFilter
ORDER BY NXTSort
To display only the relevant records, the recordset must be filtered by the id_ord URL parameter passed by the details link. To add the filter, first define a new variable. Click the Plus (+) button on top of the Variables grid, and configure it as shown in the image below:
Note: For ColdFusion, you can ignore this step for now. The same procedure is easier to be done as part of step 4.
Now you will have to edit the SQL query. It already contains a condition - WHERE NXTFilter - that allows the NeXTensio filter to function. To add a new condition that will be evaluated on the same level as the first one, use the AND operator. The condition to add is: AND idord_opr = orderid. Add this after the existing condition and the recordset will be filtered.
The new SQL query will look like:

SELECT product_prd.name_prd AS idprd_opr, order_product_opr.quantity_opr, order_product_opr.id_opr
FROM order_product_opr LEFT JOIN product_prd ON order_product_opr.idprd_opr = product_prd.id_prd
WHERE NXTFilter AND idord_opr = orderid
ORDER BY NXTSort

For ColdFusion, add AND idord_opr = #URL.id_ord# after the WHERE #PreserveSingleQuotes(SESSION.filter_tfi_listorder_product_opr1)# line.
The final SQL code will look like this:

SELECT product_prd.name_prd AS idprd_opr, order_product_opr.quantity_opr, order_product_opr.id_oprFROM order_product_opr LEFT JOIN product_prd ON order_product_opr.idprd_opr = product_prd.id_prd WHERE #PreserveSingleQuotes(SESSION.filter_tfi_listorder_product_opr1)# AND idord_opr = #URL.id_ord#ORDER BY #SESSION.sorter_tso_listorder_product_opr1#
Click OK to close the recordset dialog box. Save the page and upload it to the server.

Now when you preview the main list in the browser and click on one of the order's details button, the detail list will only display the products that the order is composed of:

To further improve the list, you can enter a better title (replace the order_product with Order details) or modify the list recordset to display the product price as well.

For the overall application improvements, you can create product and user management pages, using NeXTensio lists and forms.