Swat those pesky Zero Balance Accounts from your Report

by Barry Rochford

Ever run a Report of Customers With Balances Due? How did you handle a Customer with say, 5 Invoices - all of which had zero balance, or a Customer that had no activity this month? Just recently, a User had that problem. At first I thought, Oh that's easy, after digging in to his problem a little, I began to see that it is a bit more complex than you would think. Yes, a lot depends on how you originally did the Systems Design. In any case, I saw that this situation could happen and decided I would spend some time and hopefully, give this User a way to print his report not showing any the zero balance accounts.

Only two Tables and one Set were involved, I set up some test tables and a Test Set as shown in Figures 1 - 3:

Looking at his tables & Set, about all my client could do in his Report is filter on the child records when Total = zero. By adding a small table to his set (TEST_MASTER), adding an Index ("CUST_NON_ZERO"), rearranging the Set and adding a short Xbasic Script to his "Print Report" Button, I was able to print a Report showing only Customers that had one or more non-zero TEST_DET records.

If the problem had been printing Monthly Statements, there would be other questions needing answers, along with some re-design. A Statement usually shows Last Month's Balance. That brings up various interesting questions. Fortunately, so far anyway, this User has not mentioned anything about Statements, so let me show you my solution.

I added a new Table:

I created an index, Cust_non_zero, on test_det. The index field is Cust and the filter for the index is Total>0.

Revised the Set:

I needed to read through all of the Test_Det records and create a Test_Master record for any Customer that had at least one non-zero value in Total. I first wrote down my pseudo code to check out the logic and see if there were any loose ends:

ZAP the existing "Test_Master" table
Open "Test_Master" for writing
Open "Test_Det" for reading
Set index to my new Index "Cust_non_zero"
Read Detail records one at a time until Cust Number changes
Write out a Test_Master record with the Cust Number
After the End of File, Close both files

My pseudo code looked good, pretty logical, but I discovered one serious omission: at End of File, I was not writing the last record. Adding that in, my pseudo code now looked like this:

ZAP the existing "Test_Master" table
Open "Test_Master" for writing
Open "Test_Det" for reading
Set index to my new Index "Cust_non_zero"
Read Detail records one at a time until Cust Number changes
Write out a Test_Master record with just the Cust Number
After the End of File, Write the last new record, Close both files

After converting the pseudo code to Xbasic, here is the Script which replaced the original Print in the "Print Report" button OnPush Event.

"Xbasic 'NOTE: Line numbers are added just for your reference, dim items as p 'they are not included as part of the Actual Script.
dim Master as p
dim safety as L
dim cust_save as c
1 master = table.open("Test_Master",file_rw_exclusive)
2 safety = .t.
3 master.zap(safety)'clear out old records
4 master.close()
5 master = table.open("Test_master",file_rw_exclusive)
6 items - table.open("Test_Det",file_ro_shared)
7 items.index_primary_put("Cust_non_zero")
8 items.fetch_first() 'get the first detail
9 cust_save = items.cust 'save first Cust no.
10 while .not. items.fetch_eof() 'start reading each detail record, 1 at a time
11 if items.cust <> save_cust then
12 master.enter_begin()'write out a Master record
13 master.cust = cust_save
14 master.enter_end(.t.)
15 cust_save = items.cust'save the new cust no.
16 end if
17 items.fetch_next() 'get next detail & go back
18 end while 'got to EOF
19 master.enter_begin() 'don’t forget to write out last record!
20 master.cust = save_cust
21 master.enter_end(.t.)
22 master.close()
23 items.close()
24 :Report.preview("Balance_Report",,"Cust") 'preview/print your report

All the code is fairly straightforward, but maybe a little further explanation on several things may make it more easily understood.

Lines 1-3. To Zap a table, the file must opened in exclusive mode and have a logical field for confirmation (I called it "Safety"). The Zap command can fail for several reasons. Because of possible failure, you must also set up a means of telling the User if the command has failed for any reason. You would set up a "On error" trap routine displaying a message such as: "Warning", "Test_Master Zap Routine Failed". An error routine may be extensive or not, but it should tell your User what happened and what to do next. Refer to your Professional Edition Reference Manual under "ZAP()" or "ON ERROR GOTO" for additional information.

Line 7. I set the Test_det index to my new Index ("Cust_non-zero") so I will be accessing only those I want on my Report.

Lines 8 & 9. After Fetching the first Test_det record, I immediately save the Cust Number in a variable "Cust_save". This habit is a carry-over from my Mainframe days, it works just as well with Alpha 4 & 5. It makes testing for a Cust change much easier, no need to be concerned about checking "is this the 1st Record?".

Lines 10 - 18. This "while" loop presents each Test_Det record. When the Cust Number changes, a Test_master record is written with the contents of the Variable "Cust_save". Then the variable is written over with the new Cust Number. The while loop continues until it has read the last record and recognizes it has reached the End of File.

Lines 19 - 24. The last record is written, now close the files and print the report.

This is just one way of ridding Zero Balance Customers from this user's report. Please remember, you should always include some On Error coding to your Script, and don't forget to Close() every file you have opened.

An alternative approach (Dr. Wayne's editorial comment) As Barry mentions, his approach is only one way to filter a report based on values in a child table. I'll show you another way that doesn't require the use of any Xbasic at all. Alpha Five has a little-understand and underused function, scanning(). Here is the definition of scanning() from Alpha Five's on-line Help: Syntax: Logical = SCANNING(TableName) Description: Returns TRUE if the current composite record buffer includes data from the specified table (TableName). The table referenced by the SCANNING function must be a child table from a set. This function is useful for determining whether a parent record has been linked to an existing child record. If the SCANNING function applied to the child table returns FALSE, there is no child record related to the current parent record. Instead of creating a new table, then, we create a new filter for the detail records for the report. The filter is scanning("test_det") .and. test_det->total>=.01: I put some test data in the set: As you can see, Barry is all paid up, while Peter and Greg owe money. If I run the report without the filter, I see all customers, although I only see bills with balances still outstanding: If I insert the detail record filter, I will not print a blank group for Barry Rochford: So - now you've seen how Barry does it and how I did it. Which way is better? That's the wrong question to ask. The two methods actually give different results. Barry's approach gives all invoices, including fully paid-up ones, for any patient with even one unpaid invoice. My approach only lists open invoices. Depending on what you want, you can choose one approach over the other. Thanks to Finian Lennon for his helpful comments on this article.

Don't forget to send us your comments on this article.

Return to home