Whether you are creating a new report or modifying an existing report, your first challenge is to Figure out where your data is and how it all comes together. All your data exists in tables. Tables are linked and accessed through table keys. To reach some tables, you will need intermediate tables and table keys. Look to the tables to find the data.

The location table is called OBJ_location. Condition tab data exists on the Condition table. Some instances are slightly off. One table can be linked to multiple records. For example, all your date information on the date tab lives on the Date Range table. The Date Range table holds data for the Object Table, the Activity Table, and the Lexicon Table. The object key links the date range table to the object table, the activity key links the date table to the activity table, and the Lexicon key links the date table to the lexicon table.

Argus is written in table structure. The Argus database is made up of 64 tables. The tables most frequently needed by Argus users are:

1. Object
2. Activity
3. Lexicon
4. Party

1. Within Argus, place cursor on the field
2. Select Column Menu
3. Select attributes.
4. View the Database column name

Table keys link tables together. You have to have matching keys. Sometimes tables need intermediate tables in order to communicate the data you need for your report.

Intermediate Tables The Party table doesn't have an object_key, so you need the Party Role table, (an intermediate table), to link the Party and Object tables.
Example: Party-nest-all-fields.

1. Click on Design Menu
2. Select Retrieval Arguments to show the four keys or four tables this nest can be inserted in.
3. The Where tab can be used to link the tables.

Note:If you are a bit confused at this point, you are not alone. Questor's table map (resident on the local drive you loaded Argus into, probably named C:\ProgramFiles\Questor\Argus\SQLany\DataModel.pdf) to learn which tables contain the fields you need, what tables they are linked to, and what keys you will need to select. You will need Acrobat Reader to read this file, but that's also resident on your local drive.

PowerBuilder is a relational database which allows one to one relationships, one to many relationships (e.g. One object with many locations), and many to many relationships (e.g. One bibliography may be linked to many records, and each may in turn be linked to other bibliographies).

Although you can view bibliographical data in the object table, it is actually held by the bibliography table. Likewise, although you add locations to object records within the object table, location information is actually indexed in the location table rather than the object table. An intermediary table , obj_location actually indexes locations linked to object records.

Table joins enable Argus users to access the information apparent in one table that is actually resident in another or others.

When creating a new nest, it will probably be easier to check similar nests for examples of how their joins are constructed.

1. From report, click on SQL object icon.
2. Click on object table and drag it to the right to expose the Join box graphic in the center of the screen that links the two (or more) tables.
3. Double click on the Join box to instruct PowerBuilder what will link these tables together (read code strings after the word "and")

Example: Object-nest appraisal_history. This has three tables: appraisal, party, and bibliography. In SQL, the red lines connect tables and the white box is how they are joined. Click on the white box between party and appraisal to see options. Argus users are only concerned with the first three options. The third option is best for us (rows from appraisal that have no party).

1. From within nest, click on SQL button. In the SQL screen, table(s) (and a list of their columns) appear in the large white space.
2. Double-click on white box between appraisal and bibliography.
3. Select third option (rows from appraisal that have no bibliography). If you don't check a different join, it defaults to the first join.

Joins -- How Tables Are Linked

1. From within nest, click on SQL button. In the SQL screen, table(s) (and a list of their columns) appear in the large white space.
2. To add or select a table, right click on the white area, go to Select Tables, and highlight the table.
3. Multiple tables are joined with a line and a box which details the operator statement.
4. To display the join statement between tables, double click on the box along the link. Only the top 3 statements are of concern. Choice of statement affects direction of link and how the link limits data.

An example of joining multiple tables would be the Activity table (which corresponds to Argus' Activity Window/Admin Tab) and Activity_Object table (which is a special intermediary table, and it corresponds to Argus's Activity Window/Object Tab and Argus' Object Window/Activity Tab).

Which field - Where Tab - Tables linked by keys - exactly what field you will use from the table to link to the main table.

Your retrieval argument, column and value all need to be the same - all have same keys. You're telling your nest what kinds of tables you can get into.

Example: Object-nest appraisal_history. This has three tables: appraisal, party, and bibliography. In SQL, the red lines connect tables and the white box is how they are joined. Click on the white box between party and appraisal to see options. Argus users are only concerned with the first three options. The third option is best for us (rows from appraisal that have no party).

1. From within nest, click on SQL button. In the SQL screen, table(s) (and a list of their columns) appear in the large white space.
2. Double-click on white box between appraisal and bibliography.
3. Select third option (rows from appraisal that have no bibliography). If you don't check a different join, it defaults to the first join.