Creating an Index for a Report

by Robert Tishkevich

I recently wrote an article for the Alpha Newsletter explaining how buyers of Alpha Five should not limit themselves, as most people do, to one specific use of the product.  If you keep an open mind and analyze various aspects of your personal life or business, you will probably find several opportunities to use Alpha Five, to track a vast array of important data.  Along those same lines, I recently began to think about a situation where for many years, use of a word processor was the traditional solution, in fact it was the only option.  But when I saw how much unnecessary work and wasted manpower was involved with the use of traditional word processing, I started thinking outside of the box and wondered if Alpha Five could be a viable alternative.

I am writing this article on June 23, 2002.  About one week ago, I was discussing a serious problem facing the Special Forces Administrative officer for the United States Park Police [USPP], the only true law-enforcement oriented federal police agency.  Along with myriad other events, the USPP is responsible every summer for handling the annual July 4th celebration on the National Mall.  This is a huge, traditional, all day affair with non-stop entertainment from a host of celebrities from the National Symphony to Rock Stars.  July 4th activities culminate with a fantastic fireworks display between the Washington Monument and the Lincoln Memorial, that is truly an exciting experience.  As a result, this special event usually draws a crowd of approximately a quarter of a million people.

Although the size of the crowd will be approximately the same this year, in light of the September 11th terrorist acts and new threats, security precautions have changed.  Instead of the usual compliment of approximately 300 officers, there is a revised plan to utilize a much larger force.  I'm sure by now you're probably asking what in the world does this have to do with Alpha Five?  Good question, as this brings me back to the theme of the article, thinking outside of the box.

Remember the USPP Special Forces Administrative Officer I referred to earlier in the article?  Check out her responsibilities.  She must type out a USPP detail in MS Word, which lists the names, ranks, and assignments of hundreds of officers.  Obviously, she must constantly line up fields and headers, making formatting a never-ending, ongoing struggle as she creates the document.  But what happens after you've typed in all of those names/assignments, finally formatted the document just the way you want, and then you have to make the usual last minute change to officer assignments?  That means you must retype the info again and reformat both the individual pages and probably reformat the entire document.

What I've described above is only part one of her difficult challenge.  Yes, there is a 2nd and far more difficult aspect of her job.  Once she's finished the arduous task outlined above, she must then type out an alphabetical index of all officers showing their assignment [beat], rank, and the page number where more in-depth information on their assignment is listed on the report [Detail].  And once again, what happens after the inevitable last minute changes are made to the Force detail?  You're right, she will have to go back and type the names and page numbers of her officer name index a 2nd or possibly even a 3rd time.

I thought about the challenge facing her and eventually realized that Alpha Five could actually handle the aforementioned tasks far more efficiently, and do it in a fraction of the time.  I was very confident about setting up an application that would allow her to quickly and efficiently type hundreds of names & assignments, along with the ability to quickly make changes without having a single concern over document formatting.  Why was I so confident?  Because Alpha Five's report column features would automatically handle the formatting of officer assignments with ease.  Here is an example of how data entry will be simplified.


Figure 1: July 4th Data Entry Form

A significant portion of redundant data one must normally type is instead handled via drop-down lists.  In this case, there is a drop-down list for both the Type of Assignment and the Officer's Rank.  That obviously eliminates the need to manually type out both the Type and Rank multiple times.  You could also create a drop-down list of USPP officers [if it were available], leaving the beat number as the only manual typing required.  However, since multiple police agencies will be involved this July 4th, there is no master list of names available; maybe next time.

If you look at Figure 1 above, the July 4th Data Entry Form, you can see the assignments are broken down into groups and officers.  The parent table [tblGroups] stores the unique Group_ID, the group's description, location & type of assignment, reporting time, and the Group Commander's information.

All officers assigned to that specific group are stored in the child table [tblDetail].  You can see that information in Figure 1 under the subtitle Officers.  The one-to-many set is linked via the Group_ID field.  In this case there are no limits to the number of officers assigned to a particular group as you might have when using a word processor.  You can add 1, 5, 20, 40 or more officers to each group.   Please note that another option, depending upon the needs of the agency, is placing supervisors in their own table and linking the officers under their command in a 2nd one-to-many child table.

As previously mentioned, the other huge advantage of using a database program, such as Alpha Five, is eliminating the need to constantly format columns and titles as one inputs the data.  Once all of the data is entered into the database, she can click on the Preview Detail button to see her detail report.  Here is part of a sample page:


Figure 2: July 4th Detail, part of only one page.

That was only Part I of the job requirement and I was not quite as confident that I could find some way of using Alpha Five to solve Part II of the July 4th Detail challenge.

I knew coming up with a solution that would automatically print an Officer Index that reflects the page number of his/her assignment would be the most difficult aspect of this project.  How could I find a way to get Alpha's report writer to go through the July 4th detail report and retrieve each officer's rank, name, assignment, and then capture the variable, which in this case, is the system page number?

I spent several hours trying everything I could imagine to solve that problem, but I wasn't successful.  I finally gave in and realized I needed help, so I posted the question on Alpha's outstanding discussion forum.  The board participants came up with some very interesting solutions but they were all work-arounds, none of them really solved the problem.

With no definitive success on the Alpha discussion board, I turned to my long-time friend and Alpha partner-in-crime, Steve Workings.  He was extremely encouraging, and sent an e-mail reply advising there was no way Alpha Five could do what I wanted.  Gee, thanks for all of your help Steve.

But hold on folks, don't give up that quickly.  After all, I didn't write this article to document only a partial success.  Steve is the kind of guy who loves coming up with solutions for these special challenges.  After thinking about it for several hours, Steve sent me a second e-mail advising that Alpha's soon-to-be-released version 5.0 product has a new feature built in that should do exactly what we want.  I love his use of the word "should".

Steve told me about a new event included in the version 5 report writer, the "Detail Section-OnRecord" event.  He also sent me a sample script that I subsequently modified slightly.

Guess what folks, the new feature didn't work as advertised, we couldn't sort the officer's names properly.  Steve then fired off an e-mail to Selwyn Rabins, who is the genius behind this incredible program.  In less than 24 hours, Selwyn advised he corrected a minor problem and it was ready to go.  Wow!  Where else can you find that kind of dedicated, rapid customer service for developers?

It worked like a charm!  Of course you knew that's how it would turn out, otherwise I wouldn't be spending an entire Sunday [and more] writing this article.  I know Steve was also somewhat excited, but he acted as he always does, knowing all along that it would in fact work.

OK, how did we solve this challenge?  First I created a separate stand-alone table that served only one purpose, storing the officer information for our report.  I named it tbPages and with a stroke of genius, I named the report attached to this table, rptPages. How's that for creativity?

The structure of tblPages is quite simple.

	Beat	Character	5
	Rank	Character	3
	Officer	Character	20
	Page_No	Numeric	2

Note:  No need for first and last name because Beat + Officer must be Unique Expression.

Our goal of course is to fill each field with the pertinent information needed to build the Officer Index.  So how could we accomplish this task?  I decided upon using a two-part process [you may prefer a one-step approach] as follows:

The user clicks a button called Index 1 Rpt to Print/Preview srptDetail, which is based upon the set called setGroups, described earlier in this article.  [I use the "s" prefix to denote the report is based upon a set].  This report then:

Here are the two scripts:

OnPrintInit Event:

Dim TP as P
TP = table.open("tblPages", file_rw_exclusive)
TP.zap(.t.)	'empty the table so there are NO Duplicate Records
TP.close()	'close and reopen empty table in the Detail OnRecord Event
END

Detail-OnRecord Event:

'Date Created: 18-Jun-2002 11:53:09 AM
'Last Updated: 22-Jun-2002 12:59:19 PM
'Created By  :  Steve Workings
'Updated By  :  Robert Tishkevich
' Detail - OnRecord Event
'---------------------------------------
Dim tbl as P
tbl = table.open("tblPages", file_rw_shared)
tbl.enter_begin()
tbl.Beat = tblDetail->beat
tbl.Rank = tblDetail->rank
Tbl.Officer = tblDetail->Officer
Tbl.Page_No = System->PageNumber	
tbl.enter_end(.t.)
tbl.close()
END

There is no error trapping in the above script but that would be a worthwhile addition.

Once the above script enters all of the current, up to date information into the previously emptied table, the last line of the script closes tblPages.

Next, the user clicks on the Index 2 Rpt button, which plays rptPages. This report is obviously based upon the data table we just filled above.  The List of Officers & Assignments report looks like this:


Figure 3: Three Column List of Rank, Officer, Beat Assignment, and Page Number

Note 1:

While srptDetail is printing, it takes awhile to empty and fill tblPages with all of the pertinent data for the Officer Index.  Therefore, I created a 2nd report called srptDetailNoIndex, which does not have any scripts in the OnPrintInit and Detail-OnRecord sections.  This report Print-Previews the July 4th Detail instantly.  Since an officer index will be needed only at the very end of the process, this is the report that will be used most often.  The button that plays this script free report is called "Preview Detail".

This gives the user the option of quickly previewing the July 4th detail report at any time without having to wait around while tblPages is being emptied and re-filled.  However, at the end of the data entry process, when everything is finished and she eventually needs a cover index for all 2,000 names, she will click on the two buttons in succession, Index 1 Rpt and Index 2 Rpt and sit back while Alpha Five does all of the work automatically.

Note 2:

I also created a second form called frmCommanders, which is accessed via a link on the Data Entry form, where the user can enter the names and assignments of all Force Officials and their command responsibilities.  Of course the user can print out that list merely by clicking on a button called Command Rpt. Once again, don't you just love my creativity?

So, in addition to printing out an alphabetical index of all officers, their rank / assignment, and the page number where their assignments are described in detail, there will also be a simple, easy to read cover sheet showing all Force Officials with their command responsibilities.

Note 3:

All names are fictitious.

Robert Tishkevich
Email: rtishkevich@yahoo.com
Special thanks to Steve Workings for his help.

7/14/02

Return to home