Joins to other files
If you want to make use of data in files related to the one being viewed
you can do so by using the 'Manage Joins' dialog to specify the relationships
between the primary file (the one loaded initially) and other files.
Creating a join
To create a join to another file press Alt-J from the main record list
view to bring up the 'Manage Joins' dialog and press INSERT. You then need
to specify the following details:
-
The file you wish to join to.
-
The index of the chosen file to be used for the join.
-
The fields from the primary file that specify the join value, one per segment
of the chosen index. You can also choose a join name (default being the
dictionary name of the file), allowing you to join to the same file more
than once.
Once the join is created the fields from the joined file can be accessed
just like the fields in the primary file using the form "JoinName.FieldName".
By default the newly available fields are not visible so you should use
Alt-F to select the fields you want to see in the main record list view.
The only limitation is that the join fields are read-only, you can't edit
their values.
The join type performed is known as a 'left outer join' where the primary
table is on the left. This means that the resulting record list will always
contain one line corresponding to each record in the primary file, even
if no match is found in the joined file. In that case, all join fields
will be null. This means that you can only specify 'one to one' or 'many
to one' relationships so you should always load the most detailed file
as the primary file. Multiple level joins are possible by using join fields
in the join key of another file.
Managing Joins
Using the Alt-J dialog you can also use the Edit function to modify the
join key values and the Delete function to remove a join. Once a useful
set of joins is defined, the details can be saved using F2 at the main
record list view and reloaded with F3.
How it works
When a join fields value is needed, BTC constructs a key by evaluating
the set of expressions specified as the join value and using their results
as key segments. The resulting key is then used in a 'GetEqual' look-up
on the joined file. If a matching record is found the required field value
is retrieve from it.