Articles
Search: 

VFP and Accounting (Part II)
Wednesday, June 5th, 2013
Rafael Copquin, Estudio Copquin
Public Accountant and Certified Internal Auditor; Fox programmer since 1987, starting with FPD and upgrading to VFP. Vast experience in conversions from FPD to VFP, specialized in business and accounting systems. Member of the Microsoft Users Group of Argentina's Board of Directors as Treasurer.
When Microsoft discontinued development of Visual FoxPro, many would-be writers on VFP issues stopped sending articles to the UniversalThread and as a result, no more articles on VFP were published, with a few exceptions.

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:

  • Accounts.DBF (this is a listing of all the accounts in the system)
  • Journal.DBF (used to register all the accounting entries)
  • Details.DBF (used to register descriptions for the accounting entries)
Let me describe the use of each table and how they are part of an accounting entry. The ACCOUNTS table contains all the accounts to be used in the system. These accounts can be divided, among others, into grouping accounts or posting accounts.

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

Bank Amount
Citibank 23,456.43
Bostonbank 15,800.00
Banco de la Nacion Argentina 55,245.65
Royal Bank of Canada 33,457.85
Banco de la Provincia de Buenos Aires 10,250.00
Total BANK ACCOUNTS 138,209.93

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:

Account Description DEBIT CREDIT
11001 Citibank 5,254.00
11000 Cash 5,254.00
Deposit slip # 1223

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:

  • Accounts.dbf (a listing of all the accounts in the Chart of Accounts)
  • Journal.dbf (the entries “live“ in this table)
  • Jldetail.dbf (the short description that identifies the entry)

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
First we set multilocks to ON because we are going to be working with buffered tables. Second, we set the buffering for the journal and the jldetail tables. We use the 5 option, optimistic table buffering. Then we create a cursor which will be the recordsource of the grid and finally, we insert a blank record, to start entering data into the grid right away. Note the use of the NAME clause in the SCATTER and in the INSERT commands.

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
As grids loose their configuration when the recordsource is changed, I use this method to reconfigure the grid every time a change in the underlying cursor is necessary. The construct would be:
* 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.)
as can be seen in the VALID code for that textbox:
* 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
And the LOSTFOCUS code only fires if the lFound property is true. The accounts table is selected so we take the account code and description into local properties of this method, replace the cursor record accordingly and place the account description in the second column of the grid. That column has a RETURN .F. statement in the WHEN event, to prevent the user from entering into the description field, thus jumping directly into the DEBIT column. with thisform
    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 KEYPRESS event of this column’s textbox fires when the F1 key or the Escape key are pressed. In the first case, a call is made to an account selection form, which lets the user choose an account from the Accounts table. This is just a private data session form that shows the accounts in a grid. When the user presses enter or double clicks on the grid, the form hides and the account and its description are brought into the entry grid.

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
Finally, a call to the ADD_DBCR method of the form performs the following action:

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
When the user presses the enter key, the keypress method fires. I save the record in the nRecord local property and the debit and credit amounts in the nDebit and nCredit properties. If any of them is greater than zero, then I make a call to the General Ledger form (the brown form), let the user enter the G/L description and position the pointer back on the record by going to nRecord, replace the applicable field with the legend and perform the line addition action:

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
If the entry balances out, that is, the debit and the credit side are the same (remember the question the girl asked me when I was in the Ottawa company? See the heading of the first article of this series), then we have to calculate the next entry number. This can be done in many different ways, by using a separate table, or a special function. To make things simple and for the purpose of this article, I chose to simply get the next higher value from the journal table.
* 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
To save space, I did not explain the EraseLines method, but direct the readers to try it by themselves.

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 !!!

More articles from this author
Saturday, April 1st, 2006, by Rafael Copquin
This class is especially designed to build cursor adapters by simply passing a few parameters, such as table name, cursor name, cursor adapter object name, updatable or not, empty or not, complex or simple select statement.
Monday, April 1st, 2002, by Rafael Copquin
Those of us who have ever programmed FoxPro DOS applications, are marveled and at the same time overwhelmed with the possibilities of enhancement in the appearance, the performance and the different ways of doing the things that were difficult, or outright impossible to do in DOS, that we observe in Visual Fox Pro. The concept of "object", which enables us to modify the appearance or the behaviour of a screen, control or basic routine, by just a few clicks of t...
Saturday, June 1st, 2002, by Rafael Copquin
In the April 2002 issue, I showed the way to use a grid as a picklist, and in order to make the example clear and readily understandable, I did not worry about code reusability. The example simply showed how to program methods in the form containing the picklist grid in order to enable the user to select a customer name from a customer table, and place the returned value in a textbox on the calling form. In this issue I will demonstrate the use of a generic class, that can be used...
Tuesday, October 1st, 2002, by Rafael Copquin
Background Early in my development experience, back in the days of Fox Dos, I had to develop a better way to make invoices than the one I had been using. What I had been doing was using the SCROLL statement to make the screen move upwards every time a new item was added. Whenever the user wanted to review the invoice, especially if it contained a large number of lines that would disappear from the screen's limited drawing area, I had to create an array dynamically from the records saved...
Wednesday, March 1st, 2006, by Rafael Copquin
In October 2002 the UTMag published the first article on the subject of making invoices with grids. Ever since, I have received numerous emails from readers asking me how to save the invoice thus created in the server tables. This article explains how to use cursor adapters to save the data.
Saturday, July 1st, 2006, by Rafael Copquin
When we are examining data from a table, using generally a grid control, even if we did it with a browse window, we are in reality viewing a small part of a, perhaps, enormous quantity of data. We are literally viewing only the amount of records that fit in the grid or the browse window. If we want do see more records, as Visual FoxPro users used to the ease of handling of the product interfase, we simply click on the page down and page up buttons and, if the cursor is on the top or on the la...
Thursday, August 1st, 2002, by Rafael Copquin
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 ...
Thursday, March 1st, 2007, by Rafael Copquin
Accounting and computer systems should not be tied to any one language in particular. You can design a computer system to be written in Fortran, Pascal, Cobol, VFP or Visual Basic.NET. However, the love of my life as far as computer languages are concerned is Visual FoxPro and that is why the rest of this article will show examples written in VFP.
Friday, September 1st, 2006, by Rafael Copquin
The title sounds quizzical, doesn’t it? What has the Visual FoxPro menu system in common with XML? Not much. However, it is possible to use XML as a means to generate separate menus for every user. It consists of a series of case statements that would define pads, popups or menu bars, according to the status of certain boolean properties. If the properties were set to true, the corresponding lines of code would be “allowed” or “disallowed” if they were set to false.