Over the years, I kept receiving requests from many readers for a continuation of the VFP and Accounting article published in March 2007. I extend my thanks to Michel Fournier for allowing me to publish its continuation.
In the first part of this article I explained the fundamental parts of an accounting system, the most important accounting principles, the tables that should be used, the reports to expect and briefly described how automated postings could be achieved.
It is evident that the most important data entry screen that we have to build is the accounting entry (shown in figure 1). In Visual FoxPro there are many ways to enter data. However, I prefer to use grids. They are very easy to use, provide for a lot of flexibility, and their columnar layout is the most adequate for the depiction and handling of accounting entries.
The tables needed
As described in the first article, all you need is but a few tables, namely:
Grouping accounts are those that only serve to show group totals of related accounts. For example, if the Company operates with five different banks, we would use one posting account for each bank but only one grouping account for the total balances of all banks, as follows:
BANK ACCOUNTS
You can very easily show the above summary using a VFP report.
Postings to the General Ledger, for transactions involving Banks, can be exemplified with the following entry:
The above entry reflects a very simple transaction: depositing cash into the Citibank account. Following the accounting principle of double entry bookkeeping, the Bank account (Citibank) shows the money going into the bank account (deposit) and the Cash account shows the money going out of the company and into the bank.
How do we use a grid to show this transaction ?
You need a form, a grid, a few labels and textboxes and some ingenuity, which I will describe below.
The accounting entry screen
As you can see, we need a four column grid for the account code, the account description and the debit and credit amounts. The white rectangle at the top is in reality two textboxes that will be used to enter a short description for the accounting entry. When the entry is printed, this description will appear at the bottom, right after the last account. But it is easier to enter the entry description before we enter the accounts, so I’ve placed it at the top.
Look at figure 2 and see how the accounts are entered, with their respective debit or credit amounts. Each account has also a short description to appear later on in the General Ledger sheet for the account. It is shown in the middle, in the brown form, which appears briefly after the amount is entered. After the description is entered and the user presses the enter key, this brown form is hidden, enabling the user to enter another account in the next line.
When the entry is complete, the user presses the save button and a dialog asks whether she wishes to save the entry. The save operation performs a series of actions that I will describe shortly.
But before going into the intricacies of the form, let us take a look at the data environment
The data environment
Only three tables are needed, namely:
Simple isn’t it ?
Initial steps
But let us analyze the form methods now, to understand how it all works. Since the first method that fires after the data environmet is the LOAD method, we’ll start from there:
Set Multilocks On CursorSetProp("Buffering" , 5 , "journal") CursorSetProp("Buffering" , 5 , "jldetail") create cursor curJournal (account I ,; amount N(12,2) null ,; dbcr C(1) null ,; debit N(12,2) null ,; credit N(12,2) null ,; accdesc C(30) ,; gllegendC(30) null) * Create a new first blank record for the grid * making it ready for entering data Select curJournal Scatter name oJnl blank Insert into curJournal from name oJnl
The INIT method fires afterwards and it simply makes a call to a grid configuring method.
Thisform.set_grid()
The SET_GRID method makes sure the grid is configured properly:
with thisform.grid1 .ColumnCount = 4 .FontBold = .f. .DeleteMark = .F. .Height = 200 .Left = 41 .Panel = 1 .ScrollBars = 2 .Top = 114 .Width = 527 .RecordSource = "curJournal" .column1.ControlSource = "curJournal.account" with .column2 .ControlSource = "curJournal.accdesc" .Width = 261 endwith .column3.ControlSource = "curJournal.debit" .column4.ControlSource = "curJournal.credit" endwith
* Kill the recordsource Thisform.grid1.recordsource = “” * Recalculate the cursor with a select statement or other * Call the grid reconfiguring method Thisform.set_grid()
Entering data
Now it is time to start entering data. The first thing that happens is that we enter the date of the transaction. The textbox used for the date has code that validates the date entered, making sure that an error message appears if a non valid date (such as for instance February 31st is entered). By the way, once the date is validated, is is stored into a form property, dDate, to be used at the time of saving the entry.
Likewise, the two textboxes at the top store the details into two form properties, cDet1 and cDet2, also used at the time of saving the entry.
The next thing is to start entering accounts into the grid.
The account column
In the INIT method of the textbox for this column, I added a property that will be helpful in the account validation
this.AddProperty("lFound",.f.)
* This construct allows for moving the mouse outside the grid without * running the below validation code If Empty(this.Value) or ; Lastkey() = 27 or ; && escape key lastkey() = 28 && F1 function key this.lFound = .f. Return 1 EndIf * From here, the account validation code if this.lFound = .t. Local nAccount nAccount = this.Value If Seek( nAccount,"accounts","account") this.lFound = .t. Return 1 && exit the valid event and enter the lostfocus event Else this.lFound = .f. this.Value = "" Wait "INVALID CODE" window at 30,45 timeout 1 Return 0 && stay here until a valid code is entered or F1 or escape key is pressed EndIf endif
If this.lFound = .t. Local nAccount,cDescrip nAccount = accounts.account cDescrip = accounts.descrip This.parent.parent.column2.Text1.value = cDescrip replace curJournal.account with nAccount,; curJournal.accdesc with cDescrip thisform.refresh EndIf endwith
The escape key causes the form to close if the grid is empty or fires the SAVE method if it contains any records.
LPARAMETERS nKeyCode, nShiftAltCtrl do case case nKeyCode = 28 && F1 function key thisform.accounts() case nKeyCode = 27 && escape key Select curJournal Locate for account > 0 If not Found() Release thisform && if the grid is empty, get out else thisform.save() && else attempt to save the entry endif otherwise endcase
The debit and credit columns
The LOSTFOCUS event of this two columns have the same code. The value entered is made absolute, just in case the user enters a negative value (which would cause havoc in the accounting system by preventing the debits and credits to balance). In the case of the debit column, the value entered is stored into the DEBIT field and the DBCR field is replaced with a ‘D’. Contrarywise, the code in the credit column would store the amount into the CREDIT field and a ‘C’ in the DBCR field.
Please take a look at the third snippet. If, after an amount was entered into the debit side and the user realizes that she made an error and the amount should have gone into the credit side, she would simply repeat the amount in the credit column. However, the debit column would be made zero automatically. The same happens in the credit column. If a value were to be entered in error there, when it should have gone into the debit side, an equivalent snippet would zero the credit side. This is just a little “perk” I introduced in the code, to make error correction easier.
* LostFocus event of debit column * same code for the credit column (only dbcr field is replaced with 'C' * for credit,debit field with 0.00 and credit field with value) this.Value = Abs(this.Value) replace curJournal.debit with this.Value,; curJournal.dbcr with 'D' If this.Value > 0.00 replace curJournal.credit with 0.00 endif * Adds the debit and credit columns and * determines the difference between them thisform.add_dbcr() this.Parent.Parent.Refresh
Applies redundancy by ensuring that the ‘D’ or ‘C’ marks are stored in the DBCR field and fills up the AMOUNT field with the amount (positive) of the debit column or (negative) of the credit column. In other words, at the time of saving, only the AMOUNT field will be stored in the journal table. The DEBIT and CREDIT fields in the curJournal cursor are only used as auxiliaries for purposes of the grid. But the real thing will be either a positive or a negative amount, as applicable, into the AMOUNT field of the journal table.
In addition, both columns are added and the difference between the debit side and the credit side is shown at the bottom, as well as the totals for those columns. In this manner, the user is always aware of the fact that there is a difference between the debit and the credit side. Only when the difference is zero, she will be allowed to save the entry (see the save method below).
The next line
One thing that I noticed by looking at how other programmers add a new line to a grid, if they use it for data entry, is that they resort to a separate form with textboxes, where the user enters the data for each field and then an insert is made into the grid cursor from this form. To me, this is cumbersome and not very elegant.
So I use the last column of the grid for this purpose, or rather, the KEYPRESS method of its textbox, thus:
LPARAMETERS nKeyCode, nShiftAltCtrl if nKeyCode = 13 && enter key with thisform local nDebit,nCredit,nRecord nRecord = recno() nDebit = .grid1.column3.text1.value nCredit = .grid1.column4.text1.value if nDebit <> 0.00 or nCredit <> 0.00 .gllegend() && enter legend for G/L account Go nRecord replace curJournal.gllegend with thisform.cGL_Legend Append Blank keyboard "{dnarrow}" This.Parent.parent.column1.setfocus endif endwith endif
Append a blank record, send a down arrow movement with the KEYBOARD key word and set the focus to the first column of the grid.
Make sure the AllowAddNew and DeleteMark properties of the grid are set to false. This is so to avoid accidentally deleting a record when clicking on any line of the grid and also to prevent a new record to be added if the down arrow is pressed.
That is all there is to it. Simple and straight forward.
The Save method
When the user intends to save the entry, the first control to be carried out is to see whether the debit side equals the credit side. If this is not the case, an error message is shown and the cursor goes back to the first column of the grid. This is done by the CHECKTOTALS method:
* Checktotals method Local lOK,nDb,nCr lOK = .t. Select curJournal Sum debit,credit to nDb,nCr for not Deleted() Do case Case ( Abs(nDb) = 0.00 and Abs(nCr) = 0.00 ) lOK = .f. MessageBox("All accounts have zero values",16,"Attention!!!",3000) otherwise If Abs(nDb) - Abs(nCr) <> 0.00 MessageBox("The entry is not balanced",16,"Attention!!!",3000) * if debit side is not equal to credit side return false lOk = .f. EndIf EndCase Return lOK
* ENTRYNBR METHOD * Get the next entry number local nEntryNum Select journal If Reccount("journal") = 0 nEntryNum = 1 else Calculate Max(entrynum) for not Deleted() to nEntryNum in journal nEntryNum = nEntryNum + 1 endif return nEntryNum For clarity, below is the SAVE METHOD in full Local nEntryNum,lOK if !thisform.checktotals() && if entry does not balance If MessageBox("Do you want to exit",36,"") = 6 Release thisform Else ThisForm.grid1.column1.setfocus endif Else && if entry balances, then proceed to save it nEntryNum = thisform.entrynbr() && get next entry number BEGIN TRANSACTION Select journal cTable = "journal" && save table name for error message * Create an empty record object for journal.dbf * Remove PK field because it is readonly and is entered * automatically (it is autoincremental) * This avoids the "attempt to save to read only field" error Scatter name oEntry blank fields except jnlid Select curJournal * Save only those lines that have a non zero value and *!* that have not been deleted Select curJournal Delete For ; (curJournal.debit = 0.00 and curJournal.credit = 0.00) Scan for ; (curJournal.debit > 0.00 or curJournal.credit > 0.00) and not Deleted() With oEntry .trandate = thisform.dDate && from txtDate && lostfocus method .entrynum = nEntryNum .account = curJournal.account .dbcr = curJournal.dbcr .amount = curJournal.amount .gllegend = curJournal.gllegend endwith Insert into journal from name oEntry endscan lOK = TableUpdate(.t.,.t.,"journal") If lOK = .t. cTable = "jldetail" Select jldetail Scatter name oDetail blank fields except dtlid With oDetail .entrynum = nEntryNum .details1 = thisform.cDet1 && from txtDetails1 lostfocus method .details2 = thisform.cDet2 && from txtDetails2 lostfocus method endwith Insert into jldetail from name oDetail lOK = TableUpdate(.t.,.t.,"jldetail") endif If lOK = .t. END TRANSACTION Else ROLLBACK TableRevert(.t.,"journal") TableRevert(.t.,"jldetail") =aError(aErrorArray) && Data from most recent error MessageBox("An error occurred when ; attempting to save"+Chr(13)+; "this entry.The entry was not saved"+Chr(13)+; "Table : " + cTable +; "Error #: "+; Transform(aErrorArray(1))+Chr(13)+; "Error message: "+aErrorArray(2),16,"Save error") endif endif thisform.cleanup() The cleanup method * This method prepares the grid for a new entry Select curJournal zap Scatter name oJnl blank Insert into curJournal from name oJnl Go top in curJournal With thisform .txtDetails1.value = "" .txtDetails2.value = "" .cDet1 = "" .cDet2 = "" .cgl_legend = "" .dDate = {} .grid1.column1.SetFocus .Refresh endwith
In a subsequent article, I will describe how to show the accounting information in screens and reports.
For now, have fun with the accounting entry grid !!!
