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 with any table, for the same purpose.
Some considerations
If we want to browse any table and use it as a picklist, we have to tell our class a number of things:
Hands on
Therefore I chose to create a custom class, placed in a prg file called PICK_OBJECT.PRG, as follows:
DEFINE CLASS pickobject AS custom Name = "pickobject" cTableName = .F. nFieldsToShow = .F. cField1 = .F. cField2 = .F. cField3 = .F. cField4 = .F. cCaption1 = .F. cCaption2 = .F. cCaption3 = .F. cCaption4 = .F. cIndex1 = .F. cIndex2 = .F. cFormCaption = .F. cIndex3 = .F. cIndex4 = .F. cFieldToReturn = .F. ENDDEFINE
In my example, however, I show a form with two buttons, that call the grid with two different tables, one with only two fields to show, the other showing three fields.
Figure 1: The calling form
This form is very simple and has no mystery: It will simply call the grid, instantiating the pickobject, and after the selection is made, the chosen value will be shown in the textbox. The key to doing this is to place the following code in the click event of the button. In this example, I will show the code for the Choose a customer button.
oPicker = newobject('pickobject',"pick_object.prg") with oPicker .cTableName = "customers" .nFieldsToShow = 3 .cField1 = "account" .cField2 = "company" .cField3 = "address" .cCaption1 = proper(.cField1) .cCaption2 = proper(.cField2) .cCaption3 = proper(.cField3) .cIndex1 = "custnum" && index tags from cdx file .cIndex2 = "custalf" .cIndex3 = "cusaddress" .cFieldToReturn = "company" .cFormCaption = 'CUSTOMER SELECTION LIST' endwith do form picker name oPickList linked with oPicker with thisform .myTextBox.value = oPickList.cFieldToReturn .refresh endwith oPickList.release oPickList = null oPicker = null
The picklist will be called with the keywords NAME and LINKED. This use of the DO FORM command was explained in detail in the last part of the article on the April 2002 issue, so please refer to that explanation.
In this example we instantiate the form with the NAME clause and create an object called oPickList. When the return value is read from the form, the release method is called and the form, which will be HIDDEN when the user makes the selection, will be definitely destroyed and erased from memory.
The last two lines of code make sure that there is no extant references to any of the two objects
Figure 2: The picklist form
It is a very simple form.
Figura 3: property sheet for this form
use in PickTable
And finally, let us look at the set_grid method:
* Set_Grid method code local lW,lcCol,lcFname,lcCaps,lnFieldSize,lnGridWidth local lnHeaderWidth,lnColumnWidth lnGridWidth = 0 with this.PickGrid .ColumnCount = this.lnFieldsToShow .DeleteMark = .f. .FontBold = .t. .ReadOnly = .t. .ScrollBars = 3 .RecordSource = "PickTable" for i = 1 to this.lnFieldsToShow lW = alltrim(str(i)) lcFname = "this.cField"+lW lcFname = &lcFname && get field name lnFieldSize = fsize(lcFname) && get field size lcFname = "PickTable."+lcFname && get controlsource lcCaps = "this.lcCaption"+lW lcCaps = &lcCaps && get header caption with .columns(i) .controlsource = lcFname && set column's controlsource .header1.caption = lcCaps && set column's header caption .header1.alignment = 2 && center *!* determine column width *!* it should show the full width of the header caption lnColumnWidth = lnFieldSize * 10 lnHeaderWidth = len(.header1.caption) * 8 .width = iif(lnHeaderWidth > ; lnColumnWidth,lnHeaderWidth,lnColumnWidth) endwith endfor .column1.alignment = 1 && right alignment *!* determine grid width for i = 1 to .columncount lnGridWidth = lnGridWidth + .columns(i).width endfor .width = lnGridWidth + 35 endwith this.width = this.PickGrid.width + 50 && set form width ** center grid on form this.PickGrid.left = ( this.width - this.PickGrid.width ) / 2 this.autocenter = .t. && center form on screen
How it works
The form receives the pickobject instance (oPicker) as a single parameter in its init event. All properties are assigned the oPicker object property values. And finally, a call to the set_grid method configures the grid and the form
The form properties contain the number of columns to show, and the fields to show. A series of calculations make the column widths large enough as to show the whole field, the headers centered and never wider than the columns that contain them, center the grid on the form and finally, center the form on the screen.
Each column has the following code snippets:
* textbox keypress event LPARAMETERS nKeyCode, nShiftAltCtrl if nKeyCode = 13 thisform.cFieldToReturn = eval(thisform.cFieldToReturn) thisform.hide endif * textbox doubleclick event this.keypress(13) * each column header click event set order to (thisform.cIndexTag1) this.parent.parent.refresh
The call to the cIndexTag1 property would make the column be sorted by the first index tag. The cindextag property should be changed for each index to be used in the other columns. So, the column2.header1.click code would use thisform.cIndexTag2 and so on. Finally, instead of cluttering the form with help messages on an edit box, as I did in the April 2002 example, I decided to use a label and its tooltiptext property. When the user leaves the mouse for a few seconds on the label, a short help message tells him/her how to make a selection.
Figure 5: The picklist
The companies and address data in the above example have been modified, to avoid showing actual company names and addresses. (I used a real table from one of my customers, with due permission). You can download code and data here.
Conclusion
I have shown how to make a generic class using an object to pass parameters to a form, how to receive those parameters as one single object and how to select one field from one record in any table, using a grid.
The selection methods can be easily modified to enable the selection of more than one field.
However this is a limited example on how to use grids for general purposes. In my opinion grids are a great way to show or select data from tables. I will present in a later issue other ways to interact with data by using grids.
