This topic will describe how to use the Tables tab to specify the prime table and up to eight secondary tables. It will also describe the various methods of table linking.
This tab is split into two main areas. There is the tables area where the tables required by the report are named. Then there is the connections area where you will link the secondary tables together.
What do you want to do?
Tables: This area defines the data tables which will be used
in the report definition.
Connections: This area details how the system will gain access
to the records held in the secondary tables. Primer- What does the Tables Tab do? Primer- Table connections- Why are they needed? Primer- Reading secondary tables
The report will need one table named as a minimum. This is called the
prime table.
Eight extra tables may be named in addition to the first one. These
are known as secondary tables.
If more than one table is selected they must be linked by the user.
Tables will not normally be duplicated on the list, unless they are
being accessed from more than one company, or unless you wishes to
access more than one record from the same table, (this however is
very rare).
Normal table connections can only be made using another table which
is higher up on the list of tables.
Once the tables are named and the report is written, the position of
the tables should not be amended, although new ones may be added to
the bottom of the list.
The only table that is normally searched by the system is the prime
table, the other tables, if named, are used for lookup reasons only.
A multiple connection to the second table, or concatenation of tables
are the only exceptions to this rule.
Tables may be linked using derived fields, although a good working
knowledge of the system, and the fields (columns) used, is required
to use this method.
Tables may be linked under programming control. This will normally be
done by AUTOLINE Software support staff only.
The same table, existing in three different companies, may be read
sequentially in order to include data from three companies on one report.
Tables which share the same data dictionary can be read sequentially.
Thus a Sales Ledger transaction table and a Sales Ledger Archived
transaction table may be concatenated. This option is open to
priority nine users only.
The larger the number of tables named, the harder the system must
work to run the report. Please keep the number of tables used to a minimum.
If the rules above look a little confusing please don't worry. The
tables tab is actually very simple, but because the AUTOLINE system
has built into it some powerful facilities to allow intricate table
links to be made, there are one or two extra prompts which most of
you will never utilise.
Some of the names or phrases used in this topic may be strange or new
to some people. As it is vital that you are aware of the names used,
some of them are explained here.
Field: A piece of information stored on the system, (such as a
customers name), would be stored in a field. It is also known and
referred to as a column. Either name will do.
Column: A piece of information stored on the system, (such as
a customer's name), would be stored in a column.
Record: A collection of columns, all related to one item,
would be stored in a record, (such as a Company record in the Company system).
Table: A collection of records, each of the same type, would
be stored in a table, (such as a company table in the Marketing
module). The Report Generator will always require table names to be
entered as five-character names in lower case.
Module: A collection of tables, all related to the same
subject, would commonly be stored in a module, (such as the Marketing
module). The Report Generator will always require module names to be
entered as two-character codes in upper case.
Key: A field which acts as an index in a key-path. In a
telephone book a key would be a person's surname, in a road atlas it
would be the name of a place, in the Yellow Pages it would be the
description of the type of business you want to look up. If you
wanted the key for a Sales Ledger account table it would be the
Account code, for the Stock table it would be the part number, etc.
Key-path: A key-path is an index. The system will use the
key-path to find the record required by you. Up to eighteen key-paths
may be used for each table created on the system. In most cases a
table will have at least one unique key-path. This is a single field
which identifies the record absolutely. A further explanation of key-paths
follows later in this topic.
Compound key-path: A key-path which is made up of more than
one field name. See the section which follows for a more detailed
explanation of compound key-paths.
!id: The table identification. As up to nine tables may be
used by a single report definition, each is identified by the system
by means of a table identification number instead of a name. Once the
table name is entered in the Tables tab, any subsequent mention of
the table will be made by means of the table !id. The top table on
the list, the prime table, is always !1. The second table on the list
(the first of the secondary tables), will always be !2. The other
secondary tables are named accordingly. Derived fields are always in
table zero and will have a table !id of !0.
!0 is the derived field table
!1 is the prime table
!2 - !9 are the secondary tables
In several areas of the system, such as derived fields and selection
criteria, you will need to name the fields to be used. The field
names will always start with the table !id. A full explanation of the field
naming convention is given in the derived field section of this
on-line help.
So far we have only detailed key-paths as indexes, but is there more
to them than that?
No. They really are just indexes. The author of this on-line help has
found the best way to explain a key-path is to remind people of
indexes which are in everyday use. You use indexes all the time
without any trouble at all, but when the Report Generator asks you
for a key-path you may panic. Don't!
A Telephone Book: If you want to make a telephone call you
will often need to use a telephone book to locate the telephone
number of the person you want to call. The number is not difficult to
find, all of the subscribers are listed in alphabetical order,
primarily by surname, then by initials, then by address. An unusual
name is very easy to find, there are not many of them. A more common
name is still easy to find but, because there are more of them
listed, you will often need the subscriber's initials or even the
address in order to be sure to get the number you require. To get the
correct number you need to be prepared with the right information.
A Street Atlas: If you want to find your way to a destination
in a large town or city you could use a street atlas. Use the index
in the back of the book to locate the street name you want and then
make note of the page number and grid reference of the street. The
page number and grid reference will allow you to locate the street on
the map.
Reference books: Many reference books, such as encyclopaedias,
will have an index at the back of the publication which will allow
you to look up subjects or products. Next to the item will be a page
number, or series of page numbers, where the subject required will be found.
All three examples mentioned so far are everyday indexes. They are
used by many people without concern, and they all have one thing in common;
They allow a piece of information to be found efficiently and accurately.
Imagine any of the three items above without an index.
A telephone book is really only a list of telephone numbers. If they
were recorded in telephone number order they would be completely
useless for locating a number, armed only with a subscriber's name.
If a street atlas had no index, it could take hours to find small
streets in large cities, especially when they are written upside down
in the lower left hand corner of the page.
If data were recorded in the Encyclopaedia in a random order, how
long would it take a reader to find all recorded details of the fall
of the Roman Empire within the tens of thousands of pages it contains?
A Computer Index: A computer system can search through tables,
even ones which hold data in a random order, very quickly. However,
the amount of work the system would have to do without an index in
order to locate a record would be detrimental to the performance of
the system as a whole and will make it work more slowly for other users.
In order to allow efficient use of all tables, the system will locate
records in the same way as we do. It will use an index which holds
unique information about each record in the table. In order to
display a record, it will use the index to identify the record, then
it will go and get it. If any of the three publications mentioned
previously are recorded on a computer system, they will use the same
sort of indexes that we use. Computers will also allow us to go
further than that, they will be able to calculate other indexes using
the data found in the publication. In order to explain this further
we will take the telephone book as an example.
A telephone book shows us the subscriber's surname, initials,
address, postcode and telephone number. If all of this data were fed
into a data table, such as a Marketing Target record on the AUTOLINE
system, we could begin to work on it to make it more user friendly.
The normal way of finding the subscriber will still be there - in
subscriber surname order - but now imagine two further indexes. One
could be in postcode order, the other in telephone number order. All
you have to do to locate a record is to provide a name or a postcode.
If you found a note on your desk asking you to ring 01635 523456, but
no name, you could type in the number and the screen will show you
the person you have to call, complete with address.
The AUTOLINE system will nearly always give this sort of access to
records. This is because each table on the system can have up to
eighteen key-paths for each data table. (In this example we have only
used three of them.)
Like a street atlas index, the key-path simply contains the key data
required to identify the record, and then the location of that record
in the main data area.
The last link in the chain is the means of finding Mr Smith in a
computer index. If we were to duplicate the telephone book in a
computer system, then the prime method of finding a number, by using
a subscriber name, would be insufficient. There would be too many Mr
Smith entries to make the index useful if the index only contained
this single piece of information. What we do is add to the index by
making it hold more data. In this way it will make the index entry
unique. Follow the subscriber name with the initials, then with the
address, and you finally get a unique entry. Now the computer system,
provided it is given the right information, can be precise when it
returns the telephone number.
The street atlas has the same problem. Looking up 'High Street' in a
Greater London A to Z would result in 149 entries. In order to
identify the correct entry we need a town name or postal area. So we
add this as a second index element in order to make the entry unique./P
In order to make the index entries unique we often end up with
'compound' keys.
Surname/Initials/Address
Street name/Town/Postal area
Vehicle model/Variant/Registration number
Product Group/Supplier/Part number
When tables are linked together in the tables tab, it is difficult to
make use of a compound key, because it is normal to only pass down
one piece of information in order to open the table. Only Derived
field links will allow you to identify elements in a compound
key-path and allow them to be used.
So for standard table links, look for key-paths which have a single,
(or Unique), index key./P
Once you have completed the tables tab it will tell the report which
table will be searched by this report, and which other secondary
tables are going to be needed in order to include all of the required
information on the report.
On a very simple report, only one table, the prime table, may be
needed. If the report needs to show information which is held in
other tables, then it will be necessary to name these other tables
and link them together.
Before completing the tables tab, you should be aware of the reasons
that tables should be correctly connected. Although it may be obvious
to some, some of you will not be aware of the amount of work the
system has to do in order to produce a basic report. Report Generator
makes very heavy demands on the system while it is working. It will
spend a lot of time reading information from the disk, a mechanical
function which is time consuming and relatively slow, when compared
to the speed with which the system can make calculations using data
which is held in memory. It is important to keep the system working
at optimum performance during the working day, so any reports that
are run must access records in the most economical way.
In general terms the key-path method will retrieve the required
information in about two or three moves. Thus, even in a large table,
if the system is given the right key information, it can look up that
key in the key-path and, once found, it will be pointed to the
correct location of the record within the data table.
If a basic report is run, which uses data from only one table, the
calculations about the amount of work the system is doing to access
the data is quite straightforward. Information is retrieved from the
disk sequentially via a key-path. This involves the system reading
the key-path once, to identify the next record to be read, and then
reading the data table once to actually load the record. Thus the
system has to read the disk twice in order to load one record. Once
the record is loaded, the report definition will decide if it should
be included in the report, then it will read the next record in the
key-path, load it from the data table, and so on until all records
have been read.
In very general terms we can assume that the system will read 2,000
times in order to scan a 1,000 record table.
If, as well as reading the prime table, we need to retrieve
supplementary information from a secondary table, we will have to
correctly link the two tables together in order to optimise the
retrieval of the records.
ecords are normally accessed from the table using key-paths. Remember
that the key-path is simply an index. It is very similar to an index
that would be found in the back of a road atlas. When you want to
look up the location of a place such as Lancaster, you simply look in
the index, locate Lancaster, and read the page number and map
reference given next to the entry. Go to the page number given, look
at the position given by the map reference, and you should find
Lancaster right away.
Compare that to opening the road atlas on page one and scanning each
page in turn until Lancaster is located and it is not too difficult
to see that it is quicker to go via the index than it is to search manually.
When the report finds a record in the prime table (table !id = !1),
and you want to include supplementary information to enhance the
report, the associated information is accessed from table number 2.
In the following example we will assume a 1,000 record Marketing
contact table and a 200 record Marketing target table.
The tables will normally use the Target magic number, which is found
in the Contact table, as a key to the magic number key-path in the
Target table. As long as the links between the records are accurate,
the number of read operations required to load and print the data
from both tables is about four times for each record.
(The magic number is simply a unique record number which is used for
record identification and for indexing.)
Table 1= Contact table
Table 2= Target table
Load and read the contact record location from the key-path in table !1
Load the contact record, check the column which holds the Target
magic number.
Load and read the Target record location in table !2 using the Target
magic number retrieved from table !1
Load the correct target record from table !2
Tip: The technical processes involved in sequential index
accessing are not a suitable subject for this on-line help, the
assumptions made here are very approximate and are intended for guide
purposes only.
In general terms then, for each record which needs to be printed,
there will be roughly four read operations, this equates to about
4,000 read operations for the whole report.
If we now say that a more involved report is going to be written, one
which involves including information from up to nine different
tables, then the number of read operations will increase
dramatically. Each record printed will involve approximately eighteen
read operations, (18,000 for the whole report), a significant
increase in workload.
The above example assumes that each of the tables is going to be
accessed using key-paths. If we now consider the example of a 1,000
record Contact table, and a 200 record Target table, which are not
linked together using key-paths, the picture will change
dramatically. The prime table (the Contact table), will still be read
twice for each record. The secondary table (the Target table), would
have to be searched sequentially in order to find the right record.
As this table holds 200 records, we will assume that it will take an
average of 100 reads in order to locate each record required. This is
a very rough estimate but it will suffice for this example.
Load and read the contact record location from the key-path in table !1.
Load the contact record, check the column which holds the Target
magic number.
Load the first target record in table !2 as the first step in a
sequential search of the table. Check to see if the target matches
the one from table !1, if it is the same then go to step 5.
Read the next target record in table !2. Check to see if the target
record is the one required, if it is go to step 5, otherwise repeat
step 4.
Accept the record and print it. If there are more contact records to
be read in table !1 go to step 1.
In this case we can expect to repeat step 4 an average of 100 times
for each record required. As there are 1,000 records in the prime
table, this means that the average number of reads required to
complete the report is 100,000, (100 x 1000), this does not compare
well with the 4,000 reads required in method 1, in fact it requires
25 times more disk accesses./P
Using the correct table linking method we were looking at a total of
18,000 read operations when nine tables were involved. Even this is
significantly less than 100,000.
The number of reads required, with nine tables being read
sequentially as with the second method (assuming 1,000 records in the
prime table and 200 in each of the other eight tables), is
10,000,000,000,000,000,000. The system workload involved is clearly
vast, therefore tables used by the Report Generator must be linked
correctly, if they are not, the system will ignore them and the
report will fail. Naming secondary tables manually. How do we link the tables together? Standard table linking- An example. How do I know which key-path to use?
The tables tab is completed in a very straightforward manner. The
following section will look at completing the tab with ordinary table
links, and then with derived field links.
Note: We will not discuss calculated table links or
concatenation of tables.
The first table that you need to name is the prime table. This is the
only table that the Report Generator will actually search from start
to finish (unless curtailed by use of the Sequence tab), and is the
only table which is mandatory on the report. Secondary tables may be
named if required but are not vital to the operation of the report.
In normal circumstances the system will assume you want to use a
table contained in the module you are already in, therefore the
Module and Company values are already filled in for you. You may edit
them as required by positioning the cursor in the correct box and
following the guidelines below.
Double click the first block under !id or click the Add button.
A TABLES form is displayed allowing the correct table to be
identified and loaded into the tables tab.
Module: This is the two-character module identifier, always
typed in capital letters. The module identifier is defaulted to the
module that you are currently working in. Tip :
Company: The next field is the company number. You have
several choices here.
Run time default: If two asterisks are input, which is the
default entry for the company, then the report will be run within
whatever company you are logged into at the time the report is
executed. If it is run as a batched report, it will only run on the
company number which you was in when the report was put into the
batch. This is by far the most conventional choice.
Specific company number: You may type in a specific company
number, in which case the report will only run across that specific
company. Please be aware that this may exclude certain people
Note: When the Ellipsis button is clicked, and the
available companies are displayed, they will be selected by clicking
on the required company number and then clicking on the OK
button. For reasons explained in the next paragraph, please be aware
that it is possible to toggle more than one company.
Multi-company:If you have sufficient priority the Report
Generator can be set up to scan the same table across different
companies. If, for example, you wanted to write a report which
included all transactions in a particular Nominal Ledger Expense code
in the ledgers of four different companies, as long as they are all
accessible by you on your normal login, it can be done. You will need
to tell the system which table to look at and select the required
company numbers from a list. The system will then search the tables
sequentially and combine the records selected into one report. In
order to run the report successfully the records have to be Sorted.
Note: The Multi company report will not run via key-paths.
If the report is going to run across more than one company then you
should use the search facility in order to select the company numbers
required. This can be done by clicking on each company in turn,
highlighting it to indicate that it has been chosen. Once the
required companies have been toggled click the OK button to
return to the company column on the form. The display will show two
@@ symbols to indicate that more than one company table is going to
be included in the report.
The multi company search facility is only available on the first line
of the table (for the prime table). Please be aware that the report
will have to be Sorted if multi company access is required. See the
topic covering the Sequence tab
for details about sorting reports.
Name: The name of the table is input in this column. This is
normally the first field you will be asked to complete. The table
name is always five characters, input in lower case.
Completion of the table name on the first line, is all that is
required in this form in order to allow you to proceed. If the report
needs to access extra information from other tables then the routine
will have to be repeated for as many extra tables as required up to a
maximum nine tables.
If secondary tables are required for the report then they will be
entered on line two through to line nine. The first three columns are
completed in the same way as for line one, with the exception of the
company column. You cannot name more than one company for secondary tables.
To add another table:
Double click the second block under !id or click the Add
button. If the system already knows of other tables that can be
linked to the one(s) being used a list will be presented.
Select the required table if found. This has the advantage of
connecting the tables for you. Alternatively, continue with
constructing a secondary table link manually.
Once the secondary table has been selected, the connection must be
established to one of the previously named tables. This is done by
completing the next four fields.
TYPE
!id
COLUMN
PATH
The purpose of these four fields is to enable the Report Generator to
link the required tables together in order that the information
required by the report can be gathered in the most efficient manner.
While the system allows you to name any table, held anywhere on the
system, it is up to you to determine that the table named can be
successfully linked to another table named in the report definition.
Linking tables together requires a modicum of system knowledge. Don't
worry if you find it confusing at first as it often gives the
impression of being a little difficult. Stick with it and you will
find it quite elementary after using the screen a few times. Tip :
One of the biggest concerns you may have is how to link tables
together. There is no simple answer to this and whilst most tables
can be linked without problem, other table combinations will require
more complex solutions. During normal use however, you will find the
linking of tables very easy.
The concept of linking two tables is simple. You have to tell the
system how to find the record in the secondary table, using
information you already have from one of the other tables. This is
done by providing information that will allow the system to look up
the record using one of the key-paths, (indexes), which the secondary
table has.
In essence this is very straightforward.
If we were trying to join the Sales Ledger transactions table to
accounts table, then we can establish that the account number exists
in both tables-so we would have a simple link to establish.
Similarly, if we were trying to link purchase orders to purchase
transactions lines we can again establish that the order number would
have be the way to match up the two tables. WIP headers and WIP lines
would both contain the WIP number, etc. In these examples, simple
links are easy to identify so we can provide information that will
allow the system to find matching records in the secondary table
using one of the keypaths (indexes) which the secondary table has.
All of this information is provided to the system through the
Connection area.
This area has a number of fields which require completion for each
supplementary table being linked. Access to the connection detail
screen is gained by a mouse double click on the box under the word
Type. Alternatively you can access the Connections area by clicking
the Edit button.
Type: Type has four options.
S: Simple. By far the most frequently used selection, and is
used when simple connections may be established.
M: Multiple. Used when you have a one too many relationship.
For example, when you use the Sales Ledger accounts as a prime table
and the transaction table as the secondary table, using this link
type will identify to the system that for each record accessed, there
are multiple secondary table records.
=: Calculated. Used when you are unable to provide a simple
link at the time of running-using additional information you will supply.
+: Concatenated. Used when you want to join together two tables
which share a common dictionary. The files you can do this with at the moment are as follows. PARTS logpa & parch (Parts logfile & archive file) SERVICE logla & larch (Labour logfile & archive file) ACCOUNTS trans & atrans (Transaction file & archive file) Tip :
Click on the Drop down
From Table: This is the table to which the secondary table is
being joined.
Tables may only be joined to other tables higher up the tables list
or by using derived fields. In normal operation therefore, table
number two can be joined to table number one. Table number six, if we
used one, could be joined to table one, two, three, four or five. The
table ID can be set to 0 if we are going to establish certain types
of derived link.
Tip: Select the drop down menu facility, adjacent to the From
Table field, to view / select from all the available tables.
Column: This is the column in the table we are linking to
which contains the information that the system can use to look down
the index in the secondary table, and so find matching records.
If the Column selected above has more than a single occurrence, the
occurrence number can be defined in the column adjacent to it. For
example, if we were linking to a bin number in the stock table-since
the system holds nine such bin locations, we would have to tell the
system which one of the nine to use.
Using Index: This is the index we will use to find the
information in the secondary table.
Tip: Select the drop down menu, adjacent to the Using Index
field, in order to view / select from all the available indexes.
BUTTON
Complex: Enable MULTI-COLUMN connections Tip :
In order to follow a form through the input stage we will use an
example of linking a Sales Ledger invoice record (from the
transaction table), to the Sales Ledger account details (found in the
Sales Ledger accounts table). The two tables in question here are the
SL.trans and SL.accts tables.
Using the methods described previously, put the sales ledger
transaction table onto the form as table number one and the sales
ledger accounts table as table number two.
Next, double click the first box under the word Type in the area of
the form marked connections. Alternatively, click the Edit
button. This will open the Tables form.
Type: There are four connection types available Simple,
Multiple, Calculated and Concatenated. Not all these options will be
available to all users, depending on priority, and for most purposes
Simple is the one to be selected. For the purpose of this example,
select Simple if a selection is made available to you.
From table: The screen now asks you where the data to open the
SL.accts file will come from, what it wants to know is;
'What field holds the data needed to open the table I want to access?'
Select table number one to indicate that you are going to link the
SL.accts table to the SL.trans table, and move to the next column.
Column: The Column name being asked for is taken from the
table identified in the previous column, the table !id. In the case
of this first example it is a column from table number one, the Sales
Ledger transactions table. Type in the column name if it is known. If
you are not sure which column to use then click the Ellipse [...]
button in order to help select it. The example we are using here
requires that the column chosen to be ACCOUNT (the Sales Ledger
account number recorded on the invoice).
Note: Be aware that in response to clicking the Ellipsis
button, the list of columns displayed is taken from the table which
is being linked to, this is the one input in the table !id column. It
is a common mistake to assume that the column list is taken from the
table that you are currently trying to open.
Tip: Some of you may find it helpful to remember that the
column name required here is one which will contain a piece of data
common between the two tables. (Common Denominator). Also Remember
that the column name must be input with the correct spelling, and in
capital letters.
Using Index (path): The next field requires a key path number.
What it wants to know is;
'Which key-path matches the column I have just named?'
You must identify the key-path which is to be used to open up the
secondary table. A number which identifies the correct key-path
should be input. In the example we are dealing with at the moment, we
need to complete the table link by identifying the ACCOUNT key-path.
Select the drop down menu, adjacent to the Using Index field to show
the list of key-paths. The ACCOUNT key-path can be plainly seen at
the top of the list, in position number one. Select this choice and
the number one will be placed in the Path column in the Connections
area of the Table tab.
Tip: When linking tables together using the standard method of
table linking (i.e. NOT using derived fields), it is important to
remember that all you have to do is to supply the information that
the key-path needs to identify the correct record. The system will
always find the record you want as long as you give it the right information. Tip :
Choosing the correct key-path (and of course the correct column to
feed the key-path), is vital. In order to choose the correct
combination of key-path and column, you require a small amount of
system knowledge. The following guidelines and examples may help.
Tables can only be linked using one column name.
The content of the column must match the content of one of the key-paths.
The key-path used must only contain one column name.
If you are not sure what key-paths are available, put any column name
in the column and click the Ellipsis button to look at
the key-paths before using the 'TAB' key to go back and locate the
required field.
Imagine how the link will be made by thinking what you would type
into the screen if you wanted to look at the record in normal
operation. Normally, to look at a Sales Ledger Account you would type
in the Account Code. To look at a Parts record you would type in a
part number, to look at a Target record you would type in a Target
number. You could also use description and short names to show these
things on the screen, but the important difference is that short
names can be duplicated and you will still have to choose which of
several records you want to use. Account codes, Part numbers and
Target numbers are unique. When you type in the correct number or
code, you will be shown the only record which has that code or
number. It is the unique nature of the columns just mentioned which
make them viable as index keys.
You may link using compound key-paths, (keys which contain more than
one column).
To enable multi-column connections click the Complex button.
Information to assemble the key path must still be available in a
higher table.
You may convert numbers to text or vice-versa to accommodate a link.
You may assign a value to a column to manufacture a link.
The connections area of the form asks for completion of the four
prompts just outlined. Think of them as follows.
Type: More about this later, accept Simple for now.
From Table (table !id): Think....
'Where do I get information to open this table?'.
Column: Think......
'What column holds the data needed to open the table I want to open?'
Using Index (path): Think.....
'Which key-path matches the column I have just named?' Connecting tables using compund keys. A key is embedded in another column- An example.
There will be times when the tables you want to use would be
impossible to connect. This situation would normally arise in one of
the following situations.
The table you wish to open only has compound key-paths.
The key to the table is embedded in another column.
In each of the above cases, a simple table link is out of the
question. Derived column linking can often solve the problems faced
with these examples. This section explains how.
In the most simple terms you need to assign a value to a column in a
secondary table before the table is opened.
During normal operation, using two tables with simple table links,
the report generator opens secondary tables in the following way.
The Report Generator reads a record in the prime table.
The column in the prime table which is needed to open the secondary
table is identified from the table connections area. The content of
this column is extracted.
The system assigns the content of the column from step two to the
key-path column in the secondary table which is identified in the
key-path column of the connections area.
The system uses the content of the key-path column to locate and read
the record from the secondary table.
Now compare that to the following example of how the Report Generator
links tables together using derived columns.
The Report Generator reads a record in the prime table.
The derived column logic is executed, during which you-created
derived columns to identify the column, or columns, which contain the
key data. The content of these columns are extracted by the system.
The system assigns the content of the column(s) from step two to the
key-path column(s) in the secondary table identified in the key-path
column of the connections area.
The system uses the content of the key-path column to locate and read
the record from the secondary table.
The only difference is in step two, the identification of the values
which will be given to the required key-path.
During report compilation, the system will spot the number zero in
the From Table (table !id) column and set up a process which will
examine the derived columns at run time in order to construct the key
required to access the records in the secondary table.
In the two examples of derived links mentioned previously, the first
one raised the problem of joining two tables where only compound keys
are available.
Whilst it is rare for a table not to have a useable keypath, in such
cases it will not be possible to join the tables using standard
methods, as the available path which will provide a link to our
common denominator contains more than just a single piece of data.
Where this occurs tables may still be successfully joined using the
following method.
For the purpose of this example, we will assume we wish to join the
DD (Franchise Data) variant table (DD. varia) to the prime table MK
(Marketing) vehicle table (MK. vehic).
Although we could quite simply use the Variant keypath here, for the
purposes of illustration we will use keypath one - which is a
compound index made up of three columns, FRAN (franchise) MODEL
(model) and VARIANT (variant), all of which exist in our secondary table.
Insert MK vehic table as the prime table and the DD dile as the
secondary table.
In the connections area, use = as the link type (Report Generator
must calculate the link at the time of running the report). Use table
1 as the table to which we are linking table 2.
Click the Complex button. This signifies to the Report
Generator that the link data is part of a compound keypath and
enables multi-column connections.
Enter the three column names from the Vehicle table which will match
the keypath specified.
During the execution of the report, the system will use the contents
of the three separate columns in From Table (table !id) one to look
down the index of From table (table !id) two and find matching records.
In this next example, the reason we cannot make a standard link is
not because of a multiple key, but is due to the fact that the data
we need to make a successful join is contained within another column.
The example is of a report that scans the Nominal Ledger transaction
table for transactions to do with vehicles. Since we make use of a
suffix in the Nominal transaction table which contains the vehicle
number, we can make use of this data so that we can create a link and
supplement the information held in the Nominal with information held
in the Marketing table.
The problem is that the suffix contains the vehicle number prefixed
by a letter. To use this piece of data on its own would not result in
a successful match in the Marketing vehicle index, which contains
only the vehicle number.
The solution is to extract the vehicle number from the contents of
the suffix column and then assign it to the record number in table two.
In the Tables tab, select the Nominal Ledger transaction table
(NL.trans) as the prime table and the Marketing Vehicle table (MK.
vehic) as the secondary table.
In the connections area select connection Type calculated.
Enter a From Table (table !id) of Derived (0) as we will be using
derived fields to establish the link.
Leave the Column name blank.
Input Using Index (path) 1, which is the correct path to use when
searching the vehicle table using the vehicle record number (magic).
Access the Derived tab and, by double clicking in the first available
column in the Name column, add the following:-
Double click in the =... column. Alternatively, click the Insert
button. This activates the Edit Derived field window. Leave the
column name blank. For this particular function we do not need a name
for the column we are using.
In the Definition area type:-
convert str(!1_suffix, 2, 7) to temp : error temp=0
Press <Return> and click on the OK button.
Then double click on the next available line, or again click the Insert
button to create a second entry as follows, again leaving the Field
name blank :-
!2_magic = temp
Press <Return> and click the OK button.
The first of these two lines converts the characters in the suffix
column (from the second character for the next seven characters) into
a variable called temp. In case there are alpha characters in any of
these positions we use an error trap to ensure we do not try and
covert them.
The second line assigns the extracted vehicle number which the system
can then use during the execution of the report to search the
secondary table index.
A new user may find the previous examples a little difficult to
follow at first, but please bear in mind that derived table links are
not intended for complete beginners. Some of the methods described
require a little experience of using derived
fields and prompts. Read the
topics which cover these two subjects and then get used to them
before you try to tackle derived field linking.
Derived field linking can be a positive advantage. If you can
sensibly provide the Report Generator with the values for the
key-path columns, then it can probably use those values to access a
record from a secondary table. As long as the correct values are
provided, then the required record will be located.
A link type of M defines a multiple link for the second table. This
allows a one-to-many relationship. For example the prime table could
be a sales order header table and the second table could be the sales
order line table. This has many applications such as:
It allows values etc on the lines to be summed and written back to
the header since it is the prime table.
It allows the use of a keypath or more efficient selection on the
prime table to make a report more efficient.
Headers which have no lines could be included in the report which
would not be the case if the lines table is the prime table. For
example this allows a report of End of Day options which are not
being run to be produced.
Tables
Tables - points to remember
Primer - Naming conventions
Primer - Key-Paths explained!
An everyday index
What about duplicated information?
Primer - What does the tables tab do?
Primer - table connections - why are they needed?
Primer - Reading secondary tables
Completing the Tables tab using normal table links
Naming the prime table
The TABLES form
![[...]](Ellipsis.gif){kind=small}
This button is called an Ellipsis . It provides a search facility.
Click on the button to search for the information that you require.
Naming secondary tables manually
Manual Table Connections
How do we link the tables together?
This button is called an Ellipsis . It provides a search facility.
Click on the button to search for the information that you require.
Connection Area
to select.
Standard table linking - An example
This button is called an Ellipsis . It provides a search facility.
Click on the button to search for the information that you require.
How do I know which key-path to use?
This button is called an Ellipsis . It provides a search facility.
Click on the button to search for the information that you require.
Standard Links
Derived Links
Type, From Table (table !id), Column, Using Index (path)
Derived Column Links
How does it work?
Connecting tables using compound keys
A key is embedded in another column- Example
Derived linking summary
Multiple link
