We Use Q&A to Publish The Quick Answer
Cumulative Index to Our Web Site

SINCE the early years of The Quick Answer, T.J. Shuflin has maintained a Q&A database that references all the articles, tips and @Help answers that have appeared in the newsletter.
     The database (a sample record is shown below) includes fields for the title, author, month and year of publication, page number, and other information. There's one record in the database for every article, tip, and @Help Answer.

     In most cases, the Major Topic field contains the title of the article or tip. Below that is the Keywords field.
     In the sample record, the major topic of Jeff Nitka's article is Reports from a Multi-Lineitem Database. The keywords T.J. assigned to it are Report, Multi-Line Item, Export, Import, Archive, and Macro because these are the important elements (or Q&A features) that contribute significantly to the overall approach or technique discussed in the article.
     Ultimately, each keyword becomes a topic entry or heading in The Quick Answer cumulative online index. And each major topic becomes a reference under that heading.
     If you look at any of the cumulative index pages, you'll see that the entries are formatted in two columns with dot leaders, like this:

Export ......... All about Speedy Fields - AUG 96 p.7
                 ASCII Import/Export - MAY 92 p.11
                 Convert Dates for Easy Export - JUN 94 p.10
                 . . .
                 Reports from a Multi-Lineitem Database - OCT 97 p.11
                 Sure-Fire Wholesale Retrieves - FEB 96 p.7
                 The Export Business - APR 93 p.11
                 . . .      

     In this example, a reference to Jeff's article appears in the index under the Export keyword heading, as well as under the other five keyword headings mentioned earlier.
     So, you have what in relational database jargon is called a one-to-many relationship—one keyword to many references.
     That's how the index database is designed. But how do we generate a cumulative index like this that can be published on the Web? The process is fully automated using Q&A macros. What follows is a discussion of how the application was designed. 

Turning a Keyword report into an ASCII export file
The cumulative index, as it appears on this Web site is in two columns. The first column's Keyword is combined with a dot leader (a series of periods), and the second column contains the title, issue and page number. Because a Keyword field (a special Q&A field type) can't be manipulated in the report derived columns we need to get the kind of output we want, we start with a Keyword report that we print to disk. Here's the Column/Sort Spec and derived columns for the report:

Major Topic: 20,AS,I
Keywords: 10,AS,K,H(\ ),HS(\ )
Month: 30,I
Year: 40,I
Page: 50,I

Heading: \
Formula No. 1 ";" + #20
Column/Sort Spec: 60,HS(\ )

Heading: \
Formula No. 2 ";" + #30
Column/Sort Spec: 70,HS(\ )

Heading: \
Formula No. 3 ";" + @Right(#40,2)
Column/Sort Spec: 80,HS(\ )

Heading: \
Formula No. 4 ";" + #50
Column/Sort Spec: 90,HS      

     Printing this report to disk gives us an excellent Standard ASCII file (with no column headings) in the following format:

Export ;Reports from a Multi-Lineitem Database ;OCT ;97 ;11  

     First is the Keywords field entry, followed by the Major Topic (title), the month, year, and finally the page number, each separated by a semicolon. Since each record in T.J.'s database (about 1,200 in all) contains an average of five keywords, the export file winds up containing some 6,000 such lines (records).

Using a supplementary database
Next, we turn around and import this Standard ASCII file (using the semicolon as the field delimiter) into another Q&A database that contains these six fields and Merge Specs:

Keyword: 1
   NoAt: 
  Title: 2
  Month: 3
   Year: 4
   Page: 5      

We now have one record in the new database for every Keyword in T.J.'s original database.

Making the index pages
We want Keywords such as @XLookup and XLookup to appear one right after the other under the X index heading. So we Mass Update the second database using the following Update Spec:

Keyword: #1
   NoAt: #2

If @Left(#1, 1) = "@"
Then #2 = @Mid(#1, 2, 25)
Else #2 = #1      

     We can now sort on the NoAt (meaning no @ sign) field so that all the @ functions will appear under their appropriate alphabetic headings.

One report for each index group
We have a series of index reports in this new database that are identical except for their Retrieve Specs. Here's the Column/Sort Spec, followed by the Derived Columns and Define Page settings for these reports:

Keyword: 10,AS,H(20:),I
   NoAt: 5,AS,I
  Topic: 20,AS,H(65:),I
  Month: 30,I
   Year: 40,I
   Page: 50,I

Heading: \
Formula No. 1 #10 + " " + @Text(24 -@Len(#10), ".")
Column/Sort Spec: 60,HS(\ ),I

Heading: \
Formula No. 2 #60
Column/Sort Spec: 100,HS(\ ),I

Heading: \
Formula No. 3 @Text(#60<>#100, #60)
Column/Sort Spec: 70,HS(\ )

Heading: \
Formula No. 4 #20 + " - " + #30 + " " + @Right(#40,2) + " p." + @Str(#50)
Column/Sort Spec: 90,HS(\ )      

Define Page settings:

Page width.: 100  Page length..: 600
Left margin: 0    Right margin.: 100
Top margin.: 3    Bottom margin: 0

Header Lines 2 and 3 (this example for the E-F Index):

<html><head><title>E-F Index</title><base target="Main"></head><body>
<pre><strong><big>E-F Index</big></strong><small>

     The derived columns format the report so that the first column contains the Keyword followed by however many dots (periods) it takes to fill the remaining column width. The derived columns also ensure that the Keyword (and its dot leaders) don’t repeat for each reference. The last derived column formats the reference so the major topic or title is followed by the month, the last two digits of the year, and the page number.
     The Header lines add the HTML code to the top of each index page. (See the April 1999 Quick Answer, "How to Maintain a Report's Header When Printing to Disk.")
     Here's the Retrieve Spec expression for the EF Index report (the one containing all the keywords beginning with E and F.):

{@Left(Keyword,1) = "E"
or @Left(Keyword,1) = "F"
or @Left(Keyword, 2) = "@E"
or @Left(Keyword,2) = "@F"}      

     The same basic retrieval expression is used for all the other reports. Only the letters and index ranges are different. The only exception is the retrieval expression for numbers and special characters, for which we use the following:

{Keyword < "@" or Keyword > "ZZ"}

     Using these specs, each report is printed to a disk file, winding up in the Web page format shown earlier.

A little cleanup
One final step is needed before the reports are ready to be dropped into their Web pages. We bring each disk file into Write and resave it as ASCII. This removes the extra space left by the 600-line page length. (The index files vary in length, and we don't want page breaks in them.)

Automating it all
Except for entering the records in the original index database (a much-appreciated labor of love on T.J.'s part), the entire process I’ve describe is fully automated. A macro runs the Keyword report from T.J.'s database (creating the export file), removes all the records from the second database, imports the Standard ASCII file (the Keyword report) into it to populate it with all new records, and runs the Mass Update.
     The series of index reports, AB Index, C Index, D Index, EF Index, and so forth, are run via macros to create corresponding HTML ASCII text files named AB.htm, C.htm, D.htm, EF.htm, and so on. Shown below is the macro that prints the EF.htm report to disk, then cleans it up it in Write and resaves it to ASCII. (Each index page macro calls the Alt-A macro which adds the closing HTML code):

*
<begdef><nokey><name>"EF<sp>Index"<vidon><esc><esc>rp<capsf4>d:\qa\data
\qaindex.dtf<enter>EF<sp>Index<enter>n<capsf4>d:\qa\index\EF.htm<enter>
y<esc>wg<capsf4>d:\qa\index\EF.htm<enter><alta><enddef>
*
<begdef><alta><name>"<caps,>alta<caps.>"<vidon><capsf4><dn><dn><capsf4>
<capsf4><ctrlend><capsf4><ctrlhom><ctrlend><caps,>/small<caps.><caps,>/
pre<caps.><caps,>/body<caps.><caps,>/html<caps.><ctrlf8><enter><esc><esc>
<enddef>

     Except for the report names and file names, the macros (13 of them) are identical. We simply run them one after another—bang, bang, bang. Takes just a minute or two.

Creating the Web pages
The HTML-formatted ASCII text files generated by Q&A are now ready for import into FrontPage as valid Web pages. We simply use FrontPage's Import command to replace the existing 13 HTML index pages with the new ones. Takes a couple of minutes.
     
     When we talk about Q&A being powerful, this is just one example of what we have in mind: its uncanny ability to reliably perform automated services like time after time with just a small initial investment in setup.

--Tom Marcellus, your Web host

Comments? Looking for something? Write us at inbox@quickanswer.com
Want to submit an article to The Quick Answer? Write to office@quickanswer.com
Phone -  800-780-5474 or 949-722-9127 / Fax -   949-722-9127
Marble Publications, 1927A Harbor Blvd., Costa Mesa, California 92627
Last modified: December 11, 2006