If you are familiar with Alpha Five, you know the program is filled with a vast array of powerful, easy-to-use features that make it stand out from other PC-based database applications. I'm confident there is one little-known feature that is rarely used by many Alpha Five users and/or possibly even programmers, and that is Sub-Reports. Hopefully this article will provide some ideas and/or some motivation for many of those people to take advantage of this incredibly useful and versatile feature. Why? Because it gives you the power to efficiently retrieve and professionally display data based upon multiple One-to-Many relationships in a set.

Before we go any further, let's ask and hopefully answer some questions. What are sub-reports? Who needs them and how do we use them?

Sub-reports give you the ability to create one or multiple mini-reports based upon each child table that is part of a One-to-Many Link in the set [one child table per sub-report], within the overall framework of a larger Master Report. You can print one sub-report based upon a single child table that already combines both charges and payments in that lone table. However, in many cases, you will instead decide to print two different sub-reports, which itemize those same records, divided into totally separate child tables for charges and payments. Maybe this will make more sense if we discuss how and when they are utilized.

A typical use would be an Invoicing application. All companies send invoices to their customers which list charges for items ordered, payments and/or credits issued against those items, and then summarize the overall totals, producing a current balance due for the invoice.

Invoice reports are generally grouped by an Invoice Number field [Inv_No], and they are linked, via a One-to-One relationship, to the customer via a Customer ID field [Cust_ID]. The customer information is stored in a separate table, which Alpha Five uses to lookup the customer data via the aforementioned Cust_ID field. A one-to-one relationship is utilized because there is only one customer per invoice.

Now that you have an Invoice Number and the associated customer information, you have to print out a list of items ordered and/or payments received for that invoice. This requires the use of at least a single One-to-Many Relationship between the parent Invoice Table [or whatever you decide to name it] and the child table linked to that invoice which itemizes billing/payment details.

Yes, you can store both billing and payment/credit details in the same child table if you prefer that technique. However, this is where the option of utilizing sub-reports comes into play. Instead of packing all billing and payment details into one child table, you can opt to separate the data into two, or in some cases, three separate child tables. This obviously requires the use of multiple One-to-Many Relationships.

It may sound like a complicated alternative, but in my opinion, it is actually much easier because this technique allows you to store data in a more organized, intuitive manner. Allow me to discuss this option and explain why I prefer the use of separate child tables, where charges are stored in one child table, while payments/credits are stored in a second child table, both linked to the same parent invoice table. Let's examine our available strategies.

One Child Table Method:

First we will examine the option of using only one child table, a single One-to-Many relationship that stores all information related to that invoice, including both charges [debits] and payments [credits] in the same table, let's call the child tblOrderDetails.

Many developers utilize this method and it can work well. However, I have concerns regarding three issues when choosing this option, two that are functional, and one that is based merely upon my personal preference.

If all charges and payments are stored in one table [tblOrderDetails], the developer must utilize some type of methodology that tells Alpha whether each transaction recorded in the child table is a DEBIT or a CREDIT. The potential flaw of this method is the fact that it requires a manual coding choice by the end user, which is always subject to error.

For example, most developers will opt for a pop-up list where the data entry person must select the appropriate code designating the transaction as a charge, fee, payment, or a credit issued to the customer's account. What if the data entry person is rushing and inadvertently selects the wrong code? An item ordered by the customer could inadvertently be recorded as a credit, while a payment by the customer could be incorrectly recorded as a debit. Obviously either scenario would create a serious flaw in your application.

The above methodology utilizes the principle of a "running balance". Once that running balance is inaccurate because of a coding error, or the admittedly rare chance of a computer malfunction, all future transactions will yield an inaccurate amount, hence, your invoice balance due will always be incorrect. That potential for coding errors and/or that rare possibility of a computer malfunction make me uncomfortable utilizing the "running balance" methodology.

That brings me to my 2nd objection that such a method, in my humble opinion, needlessly increases the complexity of the application, creating additional work for the developer.

My 3rd issue involves nothing more than personal choice. I prefer organizing information into neat, easy to understand, and intuitive types of categories. In this case, I like the idea of storing all charges [debits] in one table and all payments [credits] in a totally separate table, providing a very clear picture for that much-needed audit trail.

More important than how my mind works, this eliminates the need for the data entry person to manually code transactions as a debit or a credit. Of course it doesn't totally eliminate the chance of an error occurring, because with this system, the individual could possibly place the transaction in the wrong table. But in my opinion, this is far less likely to happen than choosing the wrong data entry code from a pop-up list.

This brings us to the option of utilizing multiple One-to-Many relationships where the need to create sub-reports arises.

Multiple Child Tables - Sub-Reports:

In light of the reasons cited above, I decided to store charges [debits] and payments [credits] in separate tables. Please keep in mind that if necessary, you are not limited to only two separate One-to-Many related child tables. For example, you may want a third table that stores administrative costs billed to your customers such as copying, postage, telephone, and shipping costs. Hence, you would create a third One-to-Many relationship for this child table that stores only administrative costs and then create a third sub-report to itemize the aforementioned fees. Please don't interpret this as three being some type of limit. If you have a need, you can utilize a larger number of sub-reports.

Several years ago, the big criticism against utilizing this methodology was the possibility that it would significantly slow down data entry. However, with the advent of today's high speed processors and lots of available RAM on virtually all new computers, that is no longer a valid concern. Since we don't have to worry about taking a performance hit, we can concentrate solely upon the merits of organizational functionality by choosing the option that best serves your needs.

Sample Set for this Article:

I generally use an Invoice set with at least a single One-to-One relationship plus two One-to-Many relationships, which means I need two sub-reports. In addition, the invoice set may involve additional One-to-One or One-to-Many relationships that may distract us from the primary goal of this article, which is of course sub-reports. Since I want to keep our example as simple as possible, I will create a set, which has only two One-to-Many relationships between the parent and the child tables and no One-to-One lookups. This should make our mini-application even easier to understand. OK, let's get started.

I am a long-time user of Quicken, which is admittedly a terrific program, but I love Alpha Five even more, so I'm in the process of building an Alpha Five application which tracks my monthly recurring bills. In our mini-application, our set is called setPayBills and the parent table in the set is named tblAccounts. This is where telephone, electricity, cable TV, charges cards, and all other account information for each recurring bill is stored. Each account is of course given a unique ID_No.

All charges [debits] issued by those accounts will be recorded in the child table named tblCharges and all payments or credits on those bills will be stored in a child table named tblPayments. The key field in both tables that links them to the parent, via two One-to-Many relationships, is the aforementioned ID_No field. Here is a view of the Set Design:


1. Set Design for our setPayBills created for this article

Now that we have our Invoice Set in place, we will obviously need a data entry form.


2. Data Entry form for setPayBills

There are two fields in the parent [tblAccounts] that were created solely to store totals originally entered into the appropriate child tables. Those two fields are Tot_Charges for each account and Tot_Paid made on each account. There is a third field, Bal_Due, which is simply a calculated field finding the difference between the two fields: Tot_Charges - Tot_Paid.

Every time a new transaction is recorded in either tblCharges or tblPayments, the child table's OnSave event runs an Xbasic script, which fetches through all of the child records and calculates the total amount of charges and/or respective payments for that specific account. Let me emphasize that Alpha does not have to fetch through the entire child table to make the calculation. Since we're in a One-to-Many set, it only fetches through the child records linked to that specific ID_No, so the calculation process works very fast.

The script then posts the relevant total into the appropriate field in the parent table, replacing the old value. Each time a child record is completed, the script recalculates the totals from scratch. This is a very different approach than the "Running Balance" methodology described earlier in this article. Hopefully the example below, showing all of the current records for my Verizon Telephone Account, will make this a little clearer.


Balance Due = $28.25

[1] tblCharges: On 09-20-2002, after the latest charge of $28.25 is entered, the tblCharges OnSave event kicks in, runs an Xbasic script that fetches through all 9 records, and calculates the total charge as $ 248.87. That amount is posted to the Tot_Charges field in the matching record of the parent table, tblAccounts, replacing the old value in that field.

[2] tblPayments: On 08-19-2002, after the last record was entered, the tblPayments OnSave event kicks in, runs an Xbasic script that fetches through all 8 records and calculates the total payments made as $220.62. That amount is posted to the Tot_Paid field in the matching record of the parent table, tblAccounts, replacing the old value in that field.

[3] tblAccounts: There is a calculated field named Bal_Due [described above] which calculates the current Balance Due for my Verizon Telephone account as $28.25.

Wheeeeeeeeeew, explaining all of the above was the hard part. Next, we have to create an Account Balance report with two sub-reports embedded into the detail section. However, before doing such, I am going to print out the results of a report that does NOT utilize sub-reports. I think once you see what a report, based upon multiple One-to-Many Relationships, looks like without the use of sub-reports, you will agree they are extremely useful.


3. Here is an example of what will happen with at least Two [2] Multiple One to Many Relationships in One report, without the use/benefit of Sub-Reports. Note the blank values in the paid column.

As you can see, Alpha's set design creates a composite record for each transaction [from both child tables], so we wind up printing multiple records with blank values. We can of course eliminate this problem by creating two sub-reports, one for tblCharges and one for tblPayments. Here are the steps involved.

Report Design - Creating Sub-Reports:

You can use the toolbox to create a sub-report, however, we'll go through the steps using Alpha's Report Design menu at the top of the screen.

[1] Create a report based upon setPayBills
[2] Click on Object / New
[3] Select Sub-Report
[4] Click the down arrow to select tblCharges / Click OK
[5] Alpha will add a little gray, rectangular sub-report box at the top left corner of the screen. Drag that gray box onto the left half of the detail section. Double Click on the gray box and add of the pertinent fields from tblCharges to the sub-report.

[6] Repeat steps 1 through 5 above, however, this time select tblPayments and place the new sub-report in the right half of the detail section. Double Click on the gray box and add of the pertinent fields from tblPayments to this 2nd sub-report.

Now you have two sub- reports in the detail section of the report that are parallel with one another. If you don't see that, check to make sure you dragged both reports to the detail section, placing them side-by-side and resize them if necessary. Oh, and don't forget to rename each of them to something meaningful such as SubCharges and SubPayments.

On the left you will see a list of all charges in the subCharges report, and on the right, you will see a lists of all payments and/or credits issued thus far in the SubPayments report.

Here is what your report design screen should look like.


4. Billing Report in design mode with two Sub-Reports embedded in the Detail Section. Remember, each of the sub-reports is based upon only one child table.

When you print out the Master Report above, all of these details will be transparent to the user because it will look like one seamless report. Please check out graphic number five [5] below.


5. Verizon Telephone Account with Sub-Reports embedded into a Master Report. The individual reading the report sees one seamless compilation of data, they have no idea that sub-reports were utilized.

Here are some things to keep in mind. I am not aware of any specific Alpha Tool that will automatically make fields and lines the same height in each sub-report, thereby ensuring a professional appearance all the way across the screen. Working with multiple sub-reports in the detail section will take some manual fine-tuning and practice on your part, but it isn't a big problem.

I like the idea of storing both sub-reports parallel to one another on the same level. However, you may have a need, or if you simply prefer to place one sub-report on top and the 2nd sub-report directly below the first one, go for it.

I'm sure there are many Alpha users who have come up with some nifty, creative uses for sub-reports. Please let me know if you have, I would love to see what you've done. E-mail those ideas to rtishkevich@yahoo.com.
[PRINTER FRIENDLY VERSION]