To set up a report you need to specify the prime table and up to eight secondary tables. There are also various methods of table linking.
Points to Remember:
Up to eight tables may be named in addition to the first one. These are secondary tables.
Do not duplicate tables on the list unless they are being accessed from more than one company or you are accessing more than one record from the same table.
Understand Tables:
- Primer- Naming conventions.
- Primer- Key-paths explained!
- Primer- What does the Tables Tab do?
- Primer- Table connections- Why are they needed?
- Primer- Reading secondary tables
Tables:
The Tables group box defines the data tables that are used in the report definition.
Module: The two-character module identifier, always upper case. Defaults to the module that you are currently working in.
Company: The company selection:
Name: Name of the table. Normally the first field you complete. The table name is always five characters, lower case.
Run time default: If two asterisks are input, which is the default entry for the company, then the report is run within whatever company you are logged into at the time the report is executed. If it is run as a batched report, it only runs on the company number which you was in when the report when batched (the most conventional choice).
Specific company number: The report only runs across a specific company. This could exclude certain people.
Multi-company: It is possible to toggle more than one company. If you have sufficient priority the Report Generator can be set up to scan the same table across different companies. For example, to write a report that includes 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 need select the required table company numbers from a list. Autoline searches the tables sequentially and combines the selected records into one report. In order to run the report successfully the records have to be Sorted.
Note: 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. 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 shows 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.
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.
Details how Autoline gains access to the records held in the secondary tables.
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)
From Table: The table to which the secondary table is being joined.
Note: Tables can 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.
Column: The column in the table you are linking to which contains the information Autoline uses to scan the index in the secondary table, to find matching records.
Note: If the Column has more than a single occurrence, the occurrence number can be defined in the column adjacent to it. For example, if you were linking to a bin in the stock table you would have to specify which bin number to use.
Using Index: The index you use to find the information in the secondary table.
BUTTON
Complex: Enable MULTI-COLUMN connections
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 is 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 enables 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 enables you to look up subjects or products. Next to the item is a page number, or series of page numbers, where the subject required is 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 is 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 enables 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
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 is 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 is 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.
If secondary tables are required for the report then they is 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 is 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 :
This button is called an Ellipsis . It provides a search facility. Click on the button to search for the information that you require.
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 enables 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.
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 are 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 is 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 is 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 is 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 is 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 is 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 is 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 is 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.
