Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Articles
Search: 

Making invoices with grids and cursor adapters
Rafael Copquin, March 1, 2006
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.
Summary
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.
Description
In a previous article, issued in October 2002, Making invoices with grids, I showed how to make invoices using a grid as a data entry screen. In that example, I used a local cursor as the record source of the grid, for entering the detail lines of the invoice. For space reasons, the example indicated the methodology for entering data, but did not show how to save it to disk.

Ever since, two things happened: 1) I received several emails from readers, asking how to save the data to the server tables. 2) and most important, Microsoft issued Visual FoxPro 8.0, introducing the Cursor Adapter class and subsequently, with Visual FoxPro 9.0, cursor adapters reached a level of excelence, that make them very suitable for data entry purposes. Needles to say then, that I started exploring their use to make invoices.

The invoice making process

In the October 2002 article I detailed the process of making an invoice using a grid, so I am not going to repeat it here. However, I will summarize this process here, to help in the understanding of the rest of this article.

The steps are as follows:

  • Create a local cursor to serve as the record source of the invoice details grid
  • Input in different textboxes the invoice header data (customer name, customer address, invoice date, invoice taxes and totals, etc)
  • Those textboxes are bound to form properties
  • Include methods in the details grid textboxes to calculate the invoice subtotal, taxes and total, every time an item is entered, or the quantity, the unit price or line discount are changed
  • Include a method to extend the invoice
  • Finally, create a method to save the invoice data and then print it
As shown in the quoted example, all of the above involved the use of different tables, both for lookup (customer.dbf, items.dbf, VAT.dbf) and for saving the finalized invoice (invheader.dbf,invdetails.dbf,stock.dbf,custaccs.dbf,cash.dbf)

As pointed out above, the only thing I had not explained in my article was how to save the data to disk. However, I mentioned that the use of transactions was required, because, since several tables were to be updated in sequence, it was of paramount importance that all records in all tables were saved, or none at all. And transactions are the best way to achieve this goal.

In order to complete that article I will then briefly describe the method for saving the data, with a code example:

** LOAD METHOD

** create a cursor (see how in the Oct 2002 article)

** this is the cursor to be used in the invoice grid

create cursor invoice( etc, etc,etc.)  

** set table buffering for all tables to be saved, involved in the transaction

cursorsetprop('buffering',5,'invheader')
cursorsetprop('buffering',5,'invdetail')
cursorsetprop('buffering',5,'stock')
cursorsetprop('buffering',5,'custacct')

...
** SAVE METHOD

** local objects, created with SCATTER NAME are used to save the data. The table´s primary key
** fields are all autoincremental integers. The property thus created in the object must
** be removed because it is readonly and would cause an error if saving is attempted.

local lOK,nHeaderId

BEGIN TRANSACTION

** try to save the invoice header data
select invheader
scatter name oHeader blank
removeproperty(oHeader,"headerid")
with oHeader
  .date     = thisform.dDate
  .invno    = thisform.nInvNo
  .invtotal = thisform.nTotal

  etc, fill all properties for the record from the form properties bound to the textboxes 

endwith

insert into invheader from name oHeader

lOK = tableupdate(1,.t.,'invheader')

if lOK = .t.
   
  ** obtain autoincremental value of the headers table key field, to be used as a foreign key
  ** in the details table
 
  nHeaderId = getautoincvalue()
 
   ** now try to save in the details table
   ** reading from the INVOICE cursor, line by line

   select invoice
   go top
   scan for not deleted()
       scatter name oDetails
       removeproperty(oDetails,'detailid')
       
       oDeails.headerid = nHeaderId  && fill foreign key field with header file primary key
       
       insert into invdetail from name oDetails

   endscan   
   lOK = tableupdate(1,.t.,'invdetail')   && optimistic table buffering enables saving all 

records at once

   if lOK = .t.
      ** try to save in the stock table

      select invoice
      go top
      scan for not deleted()
          scatter memvar fields item,quantity

          select stock
          
          if seek(m.item,'stock','item')
             replace stock.quantity with stock.quantity - m.quantity
          else
             insert into stock(item,quantity) values(m.item,-m.quantity)
          endif

      endscan

      lOK = tableupdate(1,.t.,'stock') 
      
      if lOK = .t.

         ** finally, try to save in the customer account table

         select custacct
         scatter name oCustAcct blank
         removeproperty(oCustAcct,'custaccid')
         with oCustAcct
             ** fill all properties as appropriate here
         endwith
         
         insert into custacct from name oCustAcct 
         
         lOK = tableupdate(1,.t.,'custacct')
      endif
   endif
endif


if lOK = .t.
   END TRANSACTION
else
   ROLLBACK
   tablerevert('invheader')
   tablerevert('invdetail.dbf')
   tablerevert('stock')
   tablerevert('custacct.dbf') 
endif

The above technique for using transactions has been recommended by Tamar Granor and others in the "Hacker's Guide to Visual FoxPro 6.0"

So, this is the end of the mystery. Now the October 2002 article is complete with the missing save method explained.

Cursor adapters enter the picture

Cursor adapters are, in most cases, a very good replacement for updatable views. Why? Because they are a class, containing properties and methods and thus offering the possibility of subclassing, adding new methods and properties.

Another reason is that they reside in a form or program file, without the need to have them reside in a database container. This means that they are part of the code for each form (or prg file), and can be easily modified, in code or using the new cursor adapter wizard, which simplifies their definition and handling. The built-in cursor adapter wizard creates cursor adapters with very simple select statements and then, only in forms. For more complex select statements you should use code in either a form method or a prg file.

The fact that they reside in the form or a prg file and not in the database also helps solve two very important issues: database corruption and view inconsistencies.

I, as many others, have been beaten by both problems, which usually happen in a network environment, especially when two users use simultaneously the view, which is contained in the database. The trick, which is just that, a mere trick, but solves this problem, is to place a separate database container, containing only views, in each of the user machines. In this manner, you ensure that only one user has access to the view definition, because it is in his/her own machine, that there is absolutely no mixture between tables and view definitions and, if anything goes wrong at the user level, it will not affect the work of the other users of the network.

This is of course a compromise solution, but it entails having to modify the views database in each of the user machines every time there is a change in the view definition or underlying tables.

As for the view corruption itself, it usually happens if you change or add fields to any of the tables involved. In this case, you have to redefine the view, otherwise it will not work properly or will give you a nasty error.

With cursor adapters, all you do when you need to modify the base tables and therefore the select statement, is just that: modify the statement in the appropriate SelectCmd property.

In addition, cursor adapters enable saving the modified cursor to the appropriate base tables, by means of the three following properties:

  • SendUpdates (if set to .T. saves the data, if set to .F., data will not be saved
  • UpdateNameList (list the names of the cursor fields to be updated and their corresponding base table names)
  • UpdatableFieldList (the list of all fields to be updated)

A very important thing to remember here is that the primary key field should be included in both UpdateNameList and UpdatableFieldList, as well as in the KeyField property.

If you do not do the above, your cursor might show, but it will not be updatable.

And lastly, a very big advantage of cursor adapters is the fact that you can change the data source by simply changing the contents of two other properties: DataSource and DataSourceType, making it very easy to change the back end server from Visual FoxPro to SQL Server for instance.

How to use the cursor adapter class

You will need a number of updatable cursor adapters to handle all those aspects of the invoice where saving to the base tables is involved.

Remember that in an invoice you have a header table to hold the invoice number, date, total, taxes, discounts, customer account, etc., a details table to hold the items sold data and several other tables, depending on the type of sale, such as cash over the counter or on credit, that you have to use.

Examples of those tables are: invheader.dbf, invdetails.dbf, cash.dbf, custacc.dbf, stock.dbf, etc

You also need a series of lookup tables, such as the customer table, the items table, the VAT rates table, the freighters table, etc. These are necessary for the user to properly prepare the invoice. But since their use has been amply discussed in my previous article, I direct the reader to that article for details.

In any case, you could also use cursor adapters to create read only cursors for these tables, even though the use of CA's is not strictly necessary.

Actually, I have created a data handling object that has an Adapter method that creates cursor adapters on the spot, by simply sending a few parameters to it, such as the name of the cursor adapter object, the tables involved, the cursor alias, whether or not it is updatable and the corresponding select statement. The class handles all aspects of cursor adapter creation. I will write an article on this class in a future issue of the UTMag.

But whether you use a sofisticated cursor adapter creation class or simply create your adapters in a form method, the result is the same: you have a series of updatable cursors and a number of lookup cursors. These are all necessary for the making of the invoice.

A cursor adapter for the header table

local lOK,cSelectCmd,cUNL,cUFL,cSchema

lOK = .t.

** create the cursor adapter object

try

thisform.addproperty("oHeader",.f.)
thisform.oHeader = Createobject("cursoradapter")

catch to oError
   
    lOK = .f.
    messagebox( "Could not create the cursor adapter object"+chr(13)+oError.message )

endtry

if lOK = .f.
   return         && CA creation failed, exit routine
endif

if lOK = .t.

  cSelectCmd = [select * from invheader where 1=0] && this will create an empty cursor

  text to cUNL noshow pretext 15
      headerid  invheader.headerid,
      date      invheader.date,
      invnbr    invheader.invnbr,
      total     invheader.total,
      vat       invheader.vat,
      custacc   invheader.custacc
  endtext
  
  cUNL = Chrtran( cUNL, Chr(13) + Chr(10), " " )
  
  cUFL = "headerid,date,invnbr,total,vat,custacc" 

  text to cSchema noshow pretext 15
      headerid   I,
      date       D,
      invnbr     C(8),
      total      N(10,2),
      vat        N(10,2),
      custacc    C(6)       
  endtext

  cUFL = Chrtran( cUFL, Chr(13) + Chr(10), " " )

  try
      with this.oHeader
	.DataSourceType     = "NATIVE"
	.Alias	            = "theheader"
	.Tables             = "invheader" 
	.BufferModeOverride = 5
	.KeyFieldList       = "headerid"
	.SendUpdates        = .T.
	.SelectCmd          = cSelectCmd
	.UpdatableFieldList = cUFL
	.UpdateNameList     = cUNL
	.CursorSchema       = cSchema
	
        lOK = .CursorFill()
          
      endwith
  catch to oError
      lOK = .f.
      messagebox("Could not create the header cursor"+chr(13)+ oError.message )
  endtry   
 
  if lOK = .t.
     select theheader
     append blank
  else
     return
  endif
endif
The above code shows how to create a cursor adapter object for the header table. There are a few things worth explaining about this code.
  • The alias for the cursor must be a different name than the base table
  • the DataSourceType has been set to "native" meaning we are dealing with VFP tables here
  • I used the text - endtext construct just to clarify the code, the end result is a string, contained in cUNL or cSchema
  • The BufferModeOverride property was set to 5 (optimistic table buffering) so all updates will be performed only when tableupdate is issued
  • SendUpdates was set to .T. to make the cursor updatable
  • the select command, as shown, will create an empty cursor. I could have used .F. instead of "1=0", but then this would be no good if we wanted to switch to SQL Server
  • The schema is not strictly necessary. However, it helps clarify what we are getting when reading the code
  • We need to append one blank record to both the header cursor and the details cursor, so the mechanics of the invoice form work properly
In the same manner, we create empty updatable cursors for the details, cash, and customer account tables.

The stock table needs a full cursor (not empty) so during the saving process we can locate the record if it exists in the table and replace the stock quantity. If it does not exist, then we create a new one, as show below:

** use 1=1 in the where clause of the select command so we get all records and not an empty
** cursor

** save the quantity sold code snippet

select thestock     && assume the stock CA alias is "thestock"
locate for itemcode = cItem
if found()
   replace qty with qty - nQty
else
   insert into thestock(item,qty) values(cItem,-nQty)  
endif

Things to be aware of

There are a few things to bear in mind when dealing with cursor adapters in the case of an invoice (or any other data entry form, for that matter).

Each cursor adapter is a separate object, so we'll need to create as many different objects as cursor adapters we have.

In other words, the above example created the oHeader object to produce the "theheader" cursor. Likewise, we would have to create an oDetails object for the "thedeatails" cursor, another one called oStock for the "thestock" cursor and so on.

Also, in order for the invoice form to work properly, you would have to kill the cursor objects involved, after each save. And recreate them for the next invoice. So, you make one invoice, save it and if you want to make another invoice without closing the form, you will have to kill the applicable objects and rebuild them again.

The methodology

Let me show you another code snippet, from a method you would call after saving and also at the beginning of the invoice making process (in this case you would call it from the init method)

if vartype(thisform.oHeader) = "O"
   removeproperty(thisform,"oHeader")
endif

if vartype(thisform.oDetails) = "O"
   removeproperty(thisform,"oDetails")
endif

** etc, for each of the CA objects you will be creating
thisform.addproperty("oHeader")
thisform.oHeader = createobject("CursorAdapter")

thisform.addproperty("oDetails")
thisform.oDetails = createobject("CursorAdapter")

** and so on
Then you create each cursor, by setting all the appropriate properties (KeyFieldList, UpdateNameList, UpdatableFieldList, and so on)

Then you populate the grid you will use as the invoice detail with the thedetails cursor (remember to append one blank record at the beginning, do not use an empty cursor)

The rest of the invoice making process is the same as described in the October 2002 article.

Saving

Well, now again we must answer the question: how do I save the invoice? It is very simple. Remember that we are using the cursors created with the cursor adapter class. The updatable ones are the only ones we are interested in here. So we set up a transaction construct and start saving THOSE CURSORS.

After each one is saved (by either INSERT INTO or APPEND BLANK - REPLACE or GATHER) nothing will really be committed to disk on the server until you issue a tableupdate.

Cursor adapters are buffered by nature. In fact, we have buffered them as number 5, optimistic table buffering. Under buffering 5, only a succesfull TABLEUPDATE will commit the data. so it is important that you follow the transaction construct (as described by Tamar Granor's Hacker's Guide).

Here is that process in pseudocode:

local lOK
lOK = .t.
BEGIN TRANSACTION
attempt saving first table (insert into cursor adapter cursor)
issue tableupdate ( lOK = tableupdate(1,.t.,"Theheader")
if lOK = .t.  (saving was successful)
   now attempt saving second cursor adapter
   lOK = tableupdate(1,.t.,"thedetails"  
   if success carry on
     etc
   endif
endif  
if lOK = .t.
   END TRANSACTION
else
   ROLLBACK
endif

Conclusion

We can use cursor adapters to make our lives easier, especially in complicated data entry situations such as invoice making. They enable better control over the code because it is encapsulated inside forms or prg files. They can be subclassed and methods and properties can be added to them.

They enable easy switching from Native tables and databases to SQL Server, or other databases as back ends, or they can accept XML from any source.

And finally, they are much easier to handle and understand that the cumbersome code needed to create a view.

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.
More articles from this author
Rafael Copquin, April 1, 2006
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.
Rafael Copquin, April 1, 2002
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, th...
Rafael Copquin, June 1, 2002
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 th...
Rafael Copquin, October 1, 2002
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 w...
Rafael Copquin, July 1, 2006
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 wa...
Rafael Copquin, August 1, 2002
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 tran...
Rafael Copquin, June 5, 2013
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 continu...
Rafael Copquin, March 1, 2007
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 o...
Rafael Copquin, September 1, 2006
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 t...