Background
Many VFP applications deal with accounting data in ways that, in my opinion, are not very efficient, from the program standpoint, or very clear to the user.
An example of this is an account statement. Now, an account statement can be any accounting statement that portrays the transactions made in an account, such as a bank statement, a general ledger account, a detail of cash receipts and disbursements, a customer account, a supplier account, etc.
What all these statements have in common is the layout of the information in a columnar format, containing at least the following data:
Many programs that I have seen deal with the issue of presenting this information by resorting to edit boxes, report forms (in paper or in the screen) or even combinations of @ row(), col() constructs. Sometimes, sofisticated listboxes are used, because they enable easy navigation "up and down" of the data.
In the days of Fox Dos, browse windows could be used. In fact, in my early accounting applications, written in FoxPro 2.6 for DOS, I used browse windows extensively. They can still be used in VFP, but we would be missing all the benefits of object oriented programming if we resorted to the old browse.
So, when faced with the challenge of converting my old DOS applications to VFP, I decided to use grids instead of browses in all instances. And the statements of account are a particularly challenging aspect of this conversion.
Problems involved
The problems involved in presenting the information in a grid are the following:
In order to understand why I mention the above as problems, let us consider the table we would need to save all the transactions and how we would retrieve the data.
Initially, I think that the most efficient table structure should contain at least the following relevant fields
Account
C
Date
D
Details
Amount
idTransaction
I
So, if we are dealing with a customer account, an invoice would be saved as a positive amount, and its payment would be saved as a negative amount. Of course, if we were dealing with a supplier account, the invoices would be credits and the payments would be debits, but even though these are the constraints placed on us by the Accounting Science, as programmers we would pay heed to that, but from our point of view, the problems presented above still stand.
As we know from previous experience, there are many ways to solve the above problems in VFP, namely, and just to mention a few:
Using a filter is not a very good option, because of the punishment in performance it entails. It can be argued that with good indexes and Rushmore, filters can be used with no performance hits, but the question is: what kind of indexes do we need to filter the data ?
If we use the second solution, an index on account, date and transaction, we could select the customer table, pull the customer data relevant to the account exposure (company name, account number, address, telephone, etc) and, by establishing a relation to the accounts table, we could show the data for a particular account number in the grid. The relation would be something like:
Select customers Set relation to account into accounts Set skip to account
In this manner, we could filter the account data and show it in the grid.
Likewise, a better way to do this (in my opinion) is to use either a SQL statement or a read only view.
It would be something like this:
Local cAcct Select customers cAcct = customers.account Select date,; details,; iif(amount > 0.00, amount, 0.00) as debit,; iif(amount > 0.00, abs(amount), 0.00) as credit,; 000000000.00 as balance,; customer.company ; from accounts ; left outer join customers on accounts.account = customers.account ; where accounts.account = cAcct; order by date, idTransaction; into cursor cards readwrite
This statement uses the READWRITE clause, a new addition to VFP 7. If you are using VFP 6 you would obtain a read only cursor. By selecting this cursor again, you can make it readonly like so:
Use cards again alias thecards in 0
A read only view would use the same statement as above, but it would be parameterized, so the WHERE clause would read:
WHERE accounts.account = ?cAcct
But what do we notice in the examples above?
The balance is nowhere to be seen, except that in the case of the SQL statement, we obtained an extra empty column, the balance column, which contains only zeroes.
Therefore, we must calculate the balance. Since the cursor obtained is ordered by date and transaction, all the transactions are neatly placed in ascending date order, which corresponds to the real world.
So, all we have to do is start scanning the cursor from the top and calculate the balance, line by line in the following manner:
Local nBal nBal = 0.00 Select cards Go top Scan nBal = nBal + debit - credit replace cards.balance with nBal endscan go top
But as pointed out before, "there are many ways to skin a fox". SQL fans would rightly say that we could use one single SQL statement, as show below, to obtain both the account data and the carrying balance:
Select ; t1.date,; t1.details,; t1.amount,; sum(t2.amount) as balance,; t1.idTransaction as trans1, ; t2.idTransaction as trans2 ; from accounts as t1 ; join accounts as t2 on t1.account = t2.account and ; t1.idTransaction >= t2.idTransaction and ; t1.date >=t2.date ; group by t1.date, ; t1.idTransaction; order by t1.date, ; t1.idTransaction ; where accounts.account = cAcct ; into cursor cards readwrite
It looks convoluted, doesn't it ? Well, it is. But it does the job. This statement was suggested to me not long ago by someone from a VFP forum and of course I tried it and it works.
However, over a LAN, the combination of SQL statement and scan - endscan takes fractions of a second whereas the above statement takes almost 3 seconds, in a modern computer, and to pull some 30 records. Both ways work, but the first solution is faster.
Needles to say, if we had used the index approach mentioned in the first place, we would have needed an extra field in the table to place the account balance.
How do we show all this in a grid?
We place the grid on form and set it with the following construct:
With thisform.grid1 .columncount = 5 .readonly = .t. .recordsource = "cards" with .column1 .controlsource = "cards.date" .header1.caption = "Date" endwith * (and so on) Endwith
A finished form as an example
I use this approach to present statements of account and add lots of "perks" to my forms, such as:
Conclusion
A grid can be a very good option to show account details. Not only it duplicates the real world (just compare it to your bank statement), but it enables you to be creative and efficient and your clients will love it.
