9.5 A Detail Report with Look Ups

The next type of report is a detail listing with look ups. Look ups are done to get a more thorough description of the information in one file. For example, in the subscriptions file there are codes for subscriber and the magazine. If you want to know the title of the magazine for a subscription you have to look up the code in the magazine file. A request for this kind of report might be something like:

Produce a report of all subscriptions.

Sort subscriber by the subscriber code.

Show the names of the subscribers.

If we did not have to show the subscriber name this report would look much like the ones from the previous section.

Before we get too deeply involved in how we are going to write this report, we must first finish the design of the report. Upon further questioning you might discover that the user would like to see the subscriber name, the magazine code, the date the subscription was started, and how many issues are left. The headings should be in the standard format (report title, current date, page numbers, detail titles, etc.). The layout of the detail is left up to you but each subscription is to be listed on a separate line.

This report requires two files: the subscriber file (sub), and the subscriptions file (script). From the subscriber file you need the following fields:

subscriber

name

From the subscription file you need the following fields:

subscriber

magazine

issues

started

Notice that the field subscriber appears in both files. This is permitted by the Report Writer, but we must be careful to make sure that we are using the field from the right file (more on this later).

We are now ready to start writing the report. First we must declare the files we are going to use. For this report we have chosen the internal names scripts for the subscription file and sub for the subscriber file. In the Report Writer, this would look like:

FILE scripts IS "script"

FILE sub IS "sub"

The order of the FILE declarations is not important and does not affect the way the report is run. The next step is to declare the fields we need from each file. These declarations might look like:

FIELDS IN scripts ARE

subscriber, magazine,

issues, started;

FIELDS IN sub ARE

subscriber, name;

The order of the FIELD declarations doesn't matter. In addition, the order of the fields in one FIELD declaration is not important. This means we could also have used the following declarations:

FIELDS IN sub ARE

name, subscriber;

FIELDS IN scripts ARE

issues, subscriber,

started, magazine;

The end result would still be the same. We are now ready to look at the main process. We can use the same basic pattern of the reports from the previous section. We still need to set a break for the page headings. Instead of sorting the subscriber file, we must sort the subscriber file, in order of the subscribers. With these changes the MAIN procedure would look like:

MAIN

BEGIN

AT TOP OF PAGE

DO headings;

SELECT FROM scripts

SORTED BY subscriber;

FOR EACH scripts

DO details;

END

Notice the field, subscriber, in the sort clause. Which file is this field in? Since you are selecting records from the file scripts, the Report Writer assumes that this reference to subscriber refers to the field in the subscriptions file (scripts).

The heading procedure is very similar to the ones of the previous section and is not shown at this time. Instead let us concentrate on the procedure details. It is here that we must look up the name to go with the subscriber code. Before we can print the subscriber name, we must find it. The statement to find something in a file is the FIND statement. This statement searches the given file until it finds a record which satisfies some condition. FIND looks like:

FIND IN file WHERE condition;

File must be the internal name of one of the FILE declarations. Condition plays a similar role as it did in the IF statement. In this case, we want to search the subscriber file until we find a record where the subscriber field of the subscriber file matches the subscriber field of the subscription file. At first you might be tempted to write this condition as:

subscriber EQ subscriber

This does not work as expected. You are searching records in the subscriber file so the Report Writer assumes that both fields are in that file. The end result is that you always find the first subscriber in the file (since a field always equals itself). What we wanted was one of the fields to be from the subscriptions file. When the Report Writer makes the wrong assumption about a file, or if it can't decide which file you want, you must state explicitly which file you mean. This is done as:

file.field

For our example, we use the following condition:

subscriber EQ scripts.subscriber

The first field is assumed to be in the subscriber file and the second field is in the subscriptions file. If we put it all together we have the following statement to look up the subscriber name given the subscriber code:

FIND IN sub

WHERE subscriber EQ scripts.subscriber;

Notice that we didn't specify which field we are looking up. After the FIND statement, all of the fields for the file contain the information in the record found. If the record is not found the fields are empty or zero (empty for strings or zero for numbers). With this in mind, the detail procedure might look like:

PROCEDURE details /* print one subscription */

BEGIN

FIND IN sub

WHERE subscriber EQ scripts.subscriber;

PRINT name, TAB (36), magazine, TAB (52), started,

TAB (62), issues, NL;

END

There is another method of finding the name of the subscriber in the subscriptions file besides the FIND statement. This method is called an indirect look up and has the following syntax:

expression ->fieldname

If fieldname is not unique (the field name appears in two or more FIELD declarations), you must explicitly specify the file, as in filename . fieldname. The indirect operator uses the value of expression to do a primary keyed read on the file containing fieldname. The result of the indirect look up is the value of fieldname after the record has been read. If there are no matching records, the result is the value of expression.

The name field in sub is accessed by indirect look up as follows:

scripts.subscriber->name

Note the subscriber is the primary key field for the file sub. A look up is done on the sub file to find the record where the subscriber field is equal to scripts.subscriber; the expression returns the value of the name field. The following example shows the details procedure written with the indirect look up feature:

PROCEDURE details /* print one subscription */

BEGIN

PRINT scripts.subscriber >sub.name,

TAB (36), magazine, TAB (52), started,

TAB (62), issues, NL;

END

The entire program might look something like:

/* list subscriptions */

FILE IN scripts IS "script" /* subscriptions file */

FILE sub IS "sub" /* subscriber file */

FIELDS IN scripts ARE

subscriber, magazine,

started, issues;

FIELDS IN sub ARE

subscriber, name;

MAIN

BEGIN

AT TOP OF PAGE

Do headings;

SELECT FROM scripts

SORTED BY subscriber;

FOR EACH scripts

DO details;

END

PROCEDURE headings /* print page headings */

BEGIN

PRINT TAB (20), "S u b s c r i p t i o n L i s t" ,

TAB (60), $todays_date:10, "Page ", $page, NL;

PRINT "Subscriber name" , TAB (36), "Magazine",

TAB (52), "Date started", TAB (68),

"Issues Left", NL, NL;

END

PROCEDURE details /* print one subscription */

BEGIN

FIND IN sub

WHERE subscriber EQ scripts.subscriber;

PRINT name, TAB (36), magazine, TAB (52),

started, TAB (68), issues, NL;

END

If you run this report, notice that the name of the subscriber is listed for each subscription even though the list is sorted by subscriber. It would be better to only print the subscriber name once when the subscriber has more than one subscription. This is done by just printing the subscriber name on the first subscription. Then on all other subscriptions after the first, only print the magazines subscribed to. There are many ways this could be done, but with the Report Writer the easiest way would be with breaks.

The subscriptions file is sorted in increasing order by subscriber. To print out the subscriber name each time the subscriber code changes, set a break on the subscriber field. Now each time the subscriber changes we can execute a statement that look up the subscriber name and prints it (sounds like a good job for a procedure). We have a choice as to when we want to do the break: just after a field has changed, or right before the field is about to change. If we break right before the change, all of the fields still have their old values (and we cannot look up the new subscriber name). If we break after the field has changed then all of the fields have their new values and we can do the necessary look ups. Obviously, we need the latter choice. To set this break in the Report Writer, use the following:

AT START OF subscriber

To indicate that this is a break on input, an additional clause is required to indicate the file name. This is done as:

CHECK file,

File must be the internal name of one of the declared files. If we write a procedure to do the look up, the break statement might look like:

CHECK scripts, AT START OF subscriber

DO new_subscriber;

This is another example of where the Report Writer makes an assumption about which file you meant subscriber to be in. In this case, it assumes that the file you wanted is scripts. Since this is the file we wanted, no further coding needs to be done.

The procedure new_subscriber only needs to look up the new name and print it out. The detail procedure prints the rest of the information. The complete program might look like:

/* list subscriptions by subscriber */

FILE scripts IS "script" /* subscriptions file */

FILE sub IS "sub" /* subscriber file */

FIELDS IN scripts ARE

subscriber, magazine,

started, issues;

FIELDS IN sub ARE

subscriber, name;

MAIN

BEGIN

AT TOP OF PAGE

Do headings;

CHECK scripts, AT TOP OF subscriber

DO new_subscriber;

SELECT FROM scripts

SORTED BY subscriber;

FOR EACH scripts

DO details;

END

PROCEDURE headings /* print page headings */

BEGIN

PRINT TAB (20),

"S u b s c r i p t i o n L i s t",

TAB (60), $todays_date:10, "Page ",

$page, NL;

PRINT "Subscriber name" , TAB (36),

"Magazine", TAB (52), "Date started",

TAB (68), "Issues Left", NL, NL;

END

PROCEDURE new_subscriber /* lookup new subscription */

BEGIN

FIND IN sub

WHERE subscriber EQ scripts.subscriber;

PRINT NL, name;

END

PROCEDURE details /* print one subscription */

BEGIN

PRINT TAB (36), magazine, TAB (52),

started, TAB (68), issues, NL;

END

PROBLEM 2:

Produce a listing of subscriptions sorted by the magazine code. The output should show the title of the magazine (shown only once), the name of the subscriber, and the number of subscriptions left. The standard report headings should be used, but the detail layout is left up to you.