Linking Forms

This dialog allows you to specify which tables of data will be available for display on the form.

Main

The main or central table that this section of the form will be focused on. Click on the down arrow of the control to list all tables currently available for selection.

You may also specify a character variable that contains the name of a database to open. If you wish to specify a variable name surround the variable with double quotes. E.g., "TableName"

Step

This selection determines movement through the main table once the table has been opened, ordered and positioned. (Ordering is determined by the Sort entry, and initial positioning by the Seek entry.) The default behaviour is to move forward through the list of entries in the main table until the end of the table is reached or a rule that terminates further forward movement is evaluated as TRUE. You may override the default behaviour by specifying "Backward". Display of the report will then terminate when the beginning of the table is reached, or a rule that terminates further backward movement is evaluated as TRUE.

Sort

Enter an expression to sort on or select from the pre-defined list of sort expressions by clicking on the down arrow. If a sort order is not specified the table will be displayed in "natural order", or in other words, in the order in which the data was entered.

Note that if you enter your own sort expression rather than select one from the list, CAPITAL will have to physically sort the report before printing it. This may take anywhere from a few seconds to several hours to perform, depending on the speed of your computer and the size of the database that must be sorted.

Hints & Tips

It is generally okay to specify your own sort expressions when you are running a report that will be executed infrequently. For example, once per day, week or month. Sort expressions should usually be avoided if you will be printing the form continuously. For example, a custom sort expression would not be recommended for an "invoice" which needs to be printed quickly and frequently.

Seek

Enter a field, variable or suitable expression that will be used to position the Main table, the first time information from this body form is displayed. Seek expressions are optional.

If you have joined a body to either a parent or another body, the body will be positioned automatically and there is no need to enter a Seek expression. When this occurs the Seek expression edit control will appear disabled.

Join

Select this option when you want a body form to be automatically joined to the "parent" or main section of the form/report or another body form. The following condition must be met:

The Sort expression of the destination join must be of the same type as the body form Sort expression. For example, if the Sort expression of the parent is a number, and the Sort expression of the body form is a character string, then automatic linking will not be attempted and a warning message will be displayed.

See the following diagram:

Keep in mind the following points:

• Body forms can be joined to the main form or each other. The main form cannot be joined to anything as it is the starting point for all joins.

• A body form joined to itself does nothing.

• When joining to a parent: When an automatic join is in place, only the records that match the Seek expression from the Main parent form will be included in the sub/body form. To use the Invoice example again, if you have an expression that evaluates to the invoice number 1000 in the Main parent table, then only records in the stock sales database that also match 1000 will be listed on the form. See the following diagram which illustrates this relationship:

• When joining a body to another body: When you have joined one body to another, the joined body takes on the next record position of the body it is joined to. This is very useful if you wish to create a set of labels on a page. Each body is used to represent a label. If the bodies are joined together, they will each print the next record/label data. Joining a body to another body is only meaningful when both bodies share the same Main table.

Nearest Match

The form will evaluate the expression entered in the Seek field (if any) and move to the first record that exactly matches this expression. If there is no exact match, no records will be included on the form.

You may allow the form to find the next closest match by selecting this option. If the Seek expression is looking for the date 01/01/99 and the next available date is 05/01/99, then this record will become the starting record for display. Likewise, if the Seek expression is trying to locate transaction number 1000, but the next closed number is 1002, then 1002 will be located instead.

Database + Key

You may link or join other databases that are related to the Main database here. Enter the database name, then -> followed by the key. Click on the table list to display a pre-defined list of keys that you may choice from. Most, but not all databases have pre-defined keys.

Database name -> Key field

The database name is the name of the secondary table to join to the Main table listed as the top of the dialog. The key field is the field reference within this database.

Joined By

This is the name of the field from the Main table that contains the key or matched information that the secondary table will join itself to. Click on the down arrow to display a list of all available Main table fields that you can choose from. You may also define a key expression here but this is usually not necessary.

It is often necessary to join additional tables to the Main table in order to be able to select and display the information you require on the form.

The key to successfully joining database tables together is to find what they have in common. In many cases CAPITAL will suggest what they have in common automatically for you. For example, the table INVOICE contains a field called ACCCODE which stores the customer account code. The table CUSTREC (customer details) contains a field called CUSCODE which also stores the customer account code. The correct Database + Key would be:

Custrec->Cuscode

This means: "the field Cuscode stored inside the database table Custrec."

The Joined By key would be:

Acccode

Note that you don't have to specify: Invoice->Acccode. CAPITAL will assume this automatically.

In the above examples you can also use "Customer" in place of "Custrec" and "Account" in place of "Cuscode" or "Acccode". CAPITAL treats these alternate names as meaning the same thing.

Note that you cannot join tables together if you cannot find what they have in common. (This is the "key" to finding what joins them together.) For example, you cannot join the stock database table to the salesperson database table because neither of these tables contain fields that store the same information.

You could, however, join a service manager job to an invoice. Close inspection of the fields contained in these database tables would reveal that the JOBCARD (or SMJOBS) database contains a field called JOB. The INVOICE database table also contains a field called JOB. Since these fields contain the same data (i.e., job numbers), the tables can be joined together.

In most cases you do not have to do anything special to locate the "key" that enables you to join tables. Simply click on the down arrow to select from the available list. If no suggestions are provided you can enter your own key field if you happen to know what it is. For more information on joining databases see: Linking Forms - Hints & Problem Solver.

You may also specify a character variable here if the Main table is a variable. Surround your variable with double quotes. E.g., "TableName"->Myfield

Export...

Select this option if you want the information generated by this body to be output to a data file.

____________________________

Related Topics

Linking Concepts

Exporting Data