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:
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:
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 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 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.