When running a query in netFORUM, the list of tables and columns in the columns to query, sort order and columns to select entries are populated based on the List Table setup for the main table being queried. Any tables that are set up as From Tables for that List Table are then populated in the columns to query, sort order, and columns to select.
To simplify adding From Tables, a Table Join Wizard has been added to the 2006.02 build. This wizard makes it easier to add a From Table to a List Table.
Avectra cautions our clients that this tool should only be used by users who have a good understanding of the data model. Table Joins that are set up incorrectly could result in misleading queries, broken queries or poor performance. This wizard can simplify this task, but the user still must have a basic understanding of the data model.
To use the Table Join Wizard, navigate to the Toolkit module, the tables’ list group item, and then select the table join wizard item link. By default, only users in the netForumAdmin, AvectraAdmin, and netForumCMS security groups will be able to view this link.
The first page of the Wizard prompts you to choose the Group Item Link for the query to which you want to add a table join. In many cases, you may not be sure which is the main List Table of a query, so this page eliminates the guesswork. The Wizard will determine the main List Table based on the link you choose.
For example, if you wanted to add a new table join to the Individuals query, then you would choose the following values for Page 1 in the Table Join Wizard.
Once you have chosen the link and click Next, on the next page you will see the main Table associated with that link. Then you will choose the table you want to join to. You must know the name of the table. The table descriptions and the table prefix are displayed next to the from table name to help you.
Also on this page, you will see a child form listing the other from tables that are already joined to the main list table. This can assist you in setting up the Join Condition on the 3rd page of the wizard, and it will also tell you if you need to add an Alias. If you are joining to the same from table more than once, then you will need to alias both from tables to avoid the SQL Server "ambiguous column name" error.
On this page, you must enter the from order, join type and join expression.
Underneath the main form, the first two child forms on this page display a list of columns in the main list table and the table to join, respectively. The columns listed are only those columns with the data type of uniqueidentifier, as these are the column most commonly used to join tables. Use these child forms to help you write the join expression.
In more advanced cases, you may be adding a table join to a different from table (not the main list table); in this case, you may expand the folder on the other from tables (on the bottom child form) to see other columns in the other from tables to help determine the join condition.
Clicking the Save and Finish button will save the Table Join and then take you to the List Tables setup. You may now navigate to the Query to ensure that your new Table Join is working as expected.
There is no cancel button on this page; the previous page will have added the from table. If you made a mistake, you may click the Save and Finish button to go to the List Table, and then delete the newly added From Table.
After saving, you may need to Clear cache before users will "see" the new from table in the Query tool.
If you wanted to manually add a From Table, instead of using the Table Join Wizard, then you would do the following: