List Table

List Tables are used to configure the table/column List Results that are returned in various places in NetForum, including:

List Tables include the List Table itself, as well as From Tables and From Columns.

How do you know which List Table definition is called? The answer is based on the Form. Any area that runs a List Result (which uses the List Table setup) references a Form. The Form is linked to an Object. The Object has an obj_prefix property; this is the 3-character column prefix. Based on this value, we go to the Table who has the same prefix in the column mdt_prefix. Once we have the Table in [md_table] then we can get to the List Table.

List Table

Here is the List Table page:

Table name: the name of the table. This field must be unique; each table in netFORUM may have only one List Table.

Select distinct?: when checked, any searches against this table will return only distinct rows. Leave this unchecked unless you notice that the List Table setup appears to be returning duplicate records. Checking this checkbox will slow performance as selecting distinct records takes more time to process.

List From

The List From tables are the tables from which columns can be displayed on the List page. Additionally, the columns that are available for querying, sorting and outputting in NetForum queries come from the List From definition.

In nearly every case, the List Table will have one From Table for itself. Although this may seem redundant, it is necessary. Consider the List Table for an Individual co_individual. Whenever you are listing Individuals, you will probably want to output columns from the Individual and Address tables (as well as others). Therefore, you will need a List From table for both Individual co_individual and Address co_address.

List Table: the parent List Table.

From Table Name: the name of the From Table. If you have multiple from tables pointing the same table, then you must use a from table alias.

From Order: the order in which the From Table(s) are JOINED. For multiple dependent JOINS, you must be sure to order the From Tables in the right order so that the dynamic SQL will be generated properly without missing dependencies. For example, if you are listing from County to State to Nation to Continent, you must create a From Table for each of those tables (even if you do not want to output any columns from those tables) and set the From Order in that same order.

Join Type: can be Join or Left Join depending on the situation. Use Left Join when you are joining to Tables based on an expression in which one column might be null. If you choose Join in this case, you will inadvertently exclude records in which that column is NULL. If left blank, netFORUM will assume it is a standard Join.

Join Expression: the expression that joins the From Table to another from table. If you are using From Table Aliases, you must use them here.

From Table Alias: the Alias to use if you are joining to the same From Table more than once. Here is an example of the Customer-to-Customer relationship table which has two foreign key columns that point to the Customer table. Observe how the aliases are setup as cs1 and cs2 and notice the Join Expression in the image on the left:

If you have two or more list from tables that point to the same table, then you must use aliases and you must also refer to those aliases in the join expression as shown above. You must use the alias in the Join Expression in this type of scenario, as shown above.

On the right is a second example, this time for the List Table setup for co_individual. Observe that there are three From Tables pointing to mb_membership, each with a different from table alias and a different join expression:

Note: If adding a From Table Alias to an existing From Table (in order to support a second new connection to the same table) other dependent From Tables must have the new Alias label added to the field names used in the existing Join Expressions. This will also impact saved queries that use the tables that have had Aliases added, the result will be that the affected fields will no longer be displayed in the saved query conditions.

List Column

List Columns are chosen from the From Tables. The List Columns are the actual fields that will appear in the list results.

Column Name: The name of the column.

Column Name Alias: an optional alias if you want the column name that is output on the column heading to be different from the default. You might want to do this if the description is long and you need a shorter one, or if the description can be made more applicable to the specific list.

Order: will order the columns from left to right on the list results grid. If the order is a negative number, then the column will not display.

See Changing List Table for details on how to edit/add.

Managing List Tables

The List Table form contains the following child forms to assist you in configuring and managing List Tables.

From Tables

This child form shows the From Tables and their From Columns for this List Table. This is the main child form for List Tables:


List Columns that will Appear in List Results

Displays the List Columns (sorted from top to bottom) that will appear in the results, in order from left to right.


This child form is useful because for complicated List Tables with multiple From Tables, it can be difficult to see and cumbersome to navigate all the List Columns at once and visualize them in the right order, as shown in the List Table setup for the co_individual table:


Where this List is used in iWeb

These are the Group Item Links in iWeb that will run off this List Table setup. These Group Items Links are for Search, List or Query. The preview hyperlink will launch the List in a new window to see the list in action.


Lookups on Forms that Use this List

These are any Lookup buttons on forms that will point to this List Table setup.


Where this List is Used in eWeb

This child form depicts any web pages in eWeb that use an eWeb List control that is based on this List Table setup.


Objects Using this List Table

Shows any Objects that will use this List Table. This child form is useful for configuring the default sort order.

Troubleshooting

List Comes Up Blank

Make sure that if you have more than two From Tables that are the same table, you use an alias on each of these tables, and use that alias in the join expression. You'll know you have this problem if you look in the error log and see an Ambiguous column name error message.

Make sure that you have actually selected one or more From Columns.

List Table Bug Report By Shien Ming.

Fact 1: List table alias and column alias compose of the list column header.

Fact 2: Change alias of 'from table' causes dynamic search fail if any of the search column is from the 'from table'.

Problem: co_organization table list has a 'from table' "co_customer" with alias "org_customer". Customer request an additional column 'cst_id', which is from "org_customer" 'from table', to be displayed in the search result list. Per requirement, the column header should be "Org ID". However, if we add "cst_id" column with alias "ID" under the "org_customer" 'from table', the column header of organization list becomes "org_cstomer ID", which is un-desireable. If we change 'from table' alias to "Org" and modify all of the join expression, we'll get correct column header "Org ID" but the search would fail when search by "sort name", "record number".

Bug: After using SQL server profiler, we find out that netForum still use the old alias in the search criteria however it use new alias in the join table. The criteria is now becoming " select .... from .... join co_customer as Org ........ where .... and org_customer.cst_recno like '200%'"

Temporary Solution:
1. Do not change baseline alias from the table list. Add another "co_customer" 'from table' to table list with desired alias "Org". Join this table with "org_customer" table using both keys "Org.cst_key=org_customer.cst_key" to make sure one to one mapping. This way, we bypass baseline bug without change the number of record.

2. Make sure you make change in both "Tables' List" and form "Search columns" child form because search columns that baseline uses in where statement come from form search columns. If you change the list table alias in "Tables's List" only, it means you only change select column and from table statement, but you did not change where statement. If you change in both "Table's list" and form search columns, this will work for you.
Note:
In Dynamic Search:
- Select columns come from "Tables' List"
- Select tables come from "Tables' Table"
- Select where come from "Search columns" child form.

List Table and Query

Based on the List Table and From table, the columns to query and columns to select are populated in the Query.

To get an idea of how these work, see this query: 

declare @tablename sysname
 
SET @tablename = 'enterTableNameHere'
 
-- example:
-- set @tablename = 'co_individual'
 
/*
  if you don't know the tablename, then get the FormKey from url querystring
  and then uncomment the section below
*/
 
/*
declare @formkey av_key
set @formKey = 'pasteFormKeyHere'
 
-- example:
-- set @formkey = 'b772881d-d704-40f3-92b6-09b13a50fcc9'
 
set @tablename = (select mdt_name from md_table 
JOIN md_object ON obj_prefix = mdt_prefix
JOIN md_dynamic_form ON obj_key = dyn_obj_key
where dyn_key = @formkey)
*/
 
 
SELECT
 
	[COLUMN TO SELECT] = 
		isnull(from_table.mdt_description, from_table.mdt_name) + 
		isnull('(' + lsf_from_alias + ')', '')  + 
		'->' + 
		isnull(mdc_description, mdc_name)
 
FROM 
	md_table main (nolock)
 
	JOIN md_table_list_table (nolock)
		ON lst_mdt_name = main.mdt_name
 
	JOIN md_table_list_from (nolock)
		ON lsf_lst_key = lst_key
 
	JOIN md_table from_table (nolock)
		ON from_table.mdt_name = lsf_from_table
 
	JOIN dbo.md_column (nolock)
		ON mdc_mdt_name = from_table.mdt_name
 
WHERE
	mdc_query_select_flag = 1
	AND main.mdt_name = @tablename
 
ORDER BY
	lsf_from_order,
	mdc_sort_order
   

FAQ

Q. How does a Find or Query know which List Table setup to use in the List Result?

A. An Object is associated with a List Table setup via the following route: An Object has a object prefix that is the 3-character column prefix (e.g. ind with co_individual) associated with a Table. That Table is then linked to its sibling List Table.

Based on this relationship described in the paragraph above, every Find and Query is launched from a Group Item Link which has a Form property. Each Form is linked to a parent Object, and based on this Object, we know the corresponding List Table as described in the paragraph above.

Q. Why does the Find page for Individual and Organization search only the primary phone, fax, email, address and relationship? Can these pages be changed to search for secondary ones also?

A. The reason these and most Find pages search only the primary ones is because the join expression in the From Tables join only to the primary record. The reason this is done is because if the join expression is changed to search for primary and secondary, then the List Results page can be confusing because it will show one record for each unique result and this is disorienting to the user. A number of times we have modified the join expression for a client, and later they ask to revert the join expression back to the original configuration.

If you want to search for secondary records in addition to primary, then in many cases this can be done with a Query.

Adding a From Table Alias

If you add an alias to a From Table that did not previously have an alias, then various parts of netFORUM that work with the List Tables could be broken.

One area is Queries. You can update the Query setup from SQL to append the new Alias in front of existing query columns that were added before the Alias existed. You must change the values below to use the appropriate alias, tables, and prefix: 

            UPDATE md_query_detail
SET --select
  qud_data1 = 'DonorCustomer.' + qud_data1
FROM md_query_detail d (nolock)
JOIN md_query q (nolock) ON que_key = qud_que_key
WHERE que_mdt_name IN ('np_pledge', 'np_gift')
AND qud_type IN ('column','where')
AND LEFT(qud_data1,4) = 'cst_'