Linking Forms - A Tutorial

As explained in the concepts section, it is usually necessary to link database tables together so that the information you want can be presented on a single form or report. If you use the Form Wizard or Report Wizard, Visual Builder will link all the necessary database files together for you. In these cases you do not have to worry about how linking "works".

Occasionally, however, you may need to build reports or forms not supported by the Visual Builder Wizards. In these cases you will also have to define your own links. Incorrectly defining links will probably mean that your form or report will not work the way you want it to.

Two issues must be settled before you can create successful links:

• Which database file will you be stepping through?

• What information do the database files share in common?

1. Specifying the Main Table

Forms and reports are essentially lists (columns of data). Which table most accurately represents that list? If we are going to create a report that shows customer account balances, then this report will list each account code, account name, other details, plus the account balance. Clearly, the main table will be the customer records file, as all that information is held in that file.

An invoice transaction is made up of at least three files:

• The customer database file.

• The invoice transaction file.

• The invoice stock/services transaction file.

Which file should be the main file? That depends on the type of report we intend to create.

If we want to produce a transaction list that contains the customer account code, transaction number, order number and invoice total, then this information is held in the invoice transaction file. It should therefore be made the main database file.

If, however, we really wanted to show all sales of product "ABC" between two dates, then we will need to step through the invoice stock/services transaction file. (Because product information is not held in the customer transaction file or the customer records file.)

The main table is therefore the table that contains the central information that the report will focus on.

2. Linking Shared Information

Once the main table has been established, the next step is to determine what additional, secondary, information is required that the main table does not contain. If we have decided that the report will focus on invoice stock sales, and we inspect this database file, we will find that it does not contain any information relating to, say, the customer order number. This information is held in the customer transaction file. If we wanted to include this information on the report as well, then both tables would have to be joined (linked) together.

Before table linking is possible we must determine whether the tables share anything in common. It is not possible to link files together if they do not store at least some information that is common to both. In the case of the invoice transaction file and invoice stock/services transaction file, both store the same invoice/transaction number. This then becomes the basis for the link.