Technote Details :: NeXTensio filters do not work on columns with alias

After having created a NeXTensio list on a recordset that uses an alias for one of the columns, when I try to apply a filter on the aliased column, I get an error message saying "Unknown column ALIAS_NAME in Where clause".

• Products: NeXTensio List.

• Operating System: All.

• Server Models: All.

• Dreamweaver version: any.

This happens because the NeXTensio List displays column content based on the alias, and not on the original column name. Also, when a filter is defined, the alias name is used instead of the column name, and thus used in the query's where clause. Since no column named as the alias exists, the query fails, and an error is returned.

There are several ways to handle this situation:

1. Do not use aliases when constructing queries that will be used on lists. This way, the entire problem is avoided. If you need the alias only to display a different name on the list header, you can define the names to use for each label in the NeXTensio List Wizard > Step2

2. If you need to use aliases in the recordset's query, and do not necessary need to have filtering options on that specific column, remove the column from the filter grid in the NeXTensio List Wizard > step3.

3. If in need of both alias and filtering options on a column, you will have to manually alter the code. To do so, follow the next steps:

1. With the page containing the list opened in Dreamweaver, switch to code view.

2. Locate the code block marked with a starting comment stating "//filter". This is where all filter options are set, for each list.

3. Each code line starts with the list's name prefixed by "tfi_", as a variable (e.g. $tfi_list1, tfi_list1, etc)

4. Locate the line  on which the alias name appears. It must use the following format:
   $tfi_list1 ->addColumn("alias name", "column type", "alias name", "condition")
   (e.g. $tfi_listrec1->addColumn("Monthlysalary", "NUMERIC_TYPE", "Monthlysalary", "=") - in this example the salary_emp column has been aliased to Monthlysalary).

5. In order for the filter to function, you must replace the first parameter (the first alias name used in the statement) with the actual column name. For the above example, the code that allows filtering to work is (Monthlysalary is alias for the salary_emp column):
   $tfi_listrec1->addColumn("salary_emp", "NUMERIC_TYPE", "Monthlysalary", "=")