Dynamic dbGrid

Dynamic dbGrid objects;
How do they do that?

A Data Access Worldwide White Paper
by Vincent Oorsprong

October 2002
Last Edited: October 11, 2002

Overview
One of the strengths of Visual DataFlex is the ability to create of all kinds of objects "on the fly", dynamically. However, some classes (like dbGrid), seem to not have this feature, the system fails or generate errors. This white paper explains how you can use the same techniques that the Database Explorer utility uses in your applications, in cases where you want to make it possible to show a dbGrid based on user selected fields.

Figure 1: a Dynamic build dbGrid

The dbGrid structure
A "normal" dbGrid (created via the IDE, for example) looks like this:

   Object oOrderDtl_Grid is a dbGrid
      Set Main_File to Orderdtl.File_Number
      Set Server to oOrderdtl_DD
      Set Ordering to 1
      Set Size to 63 377
      Set Location to 90 3
      Set peAnchors to anAll
      Set peResizeColumn to rcAll
      Set Wrap_State to TRUE
      Set pbEmbeddedPrompts to TRUE

      Begin_Row
         Entry_Item Invt.Item_id
         Entry_Item Invt.Description
         Entry_Item Invt.Unit_price
         Entry_Item Orderdtl.Price
         Entry_Item Orderdtl.Qty_ordered
         Entry_Item Orderdtl.Extended_price
      End_Row

      Set Form_Width item 0 to 55
      Set Header_Label item 0 to "Item Id"

      Set Form_Width item 1 to 119
      Set Header_Label item 1 to "Description"

      Set Form_Width item 2 to 55
      Set Header_Label item 2 to "Unit Price"

      Set Form_Width item 3 to 43
      Set Header_Label item 3 to "Price"

      Set Form_Width item 4 to 43
      Set Header_Label item 4 to "Quantity"

      Set Form_Width item 5 to 55
      Set Header_Label item 5 to "Total"
   End_Object // oOrderDtl_Grid

The dbGrid is quite a complex object. Besides that, there are many messages that make it possible to show data, accept data, and communicate with Data Dictionary Objects. The dbGrid contains several child objects. One of these child objects, and the most important one for this white paper, is created by the BEGIN_ROW...END_ROW command pair. The name of this particular object is always called ELEMENT and that name cannot and may not be changed (there is code that relies on the name of this object). The class this object is instantiated from is Prototype_Entry. The dbGrid uses this object for the creation of each row (existing or blank rows). It is the ELEMENT object that contains the information of which column to connect to which table and which field. This information is, via the ENTRY_ITEM command, stored in items.

If you have tried to make a dynamic dbGrid object, you probably have discovered the fact that the ELEMENT object is not prepared for dynamic item creation. In fact, if you open protoent.pkg and look up the contents of the BEGIN_ROW...END_ROW commands, you can see this. These commands use the ITEM_LIST and END_ITEM_LIST command pair. Dynamic item creation (with the messages ADD_ITEM, INSERT_ITEM and DELETE_ITEM) inside these delimiters is not possible. Some classes that usually use the ITEM_LIST...END_ITEM_LIST command pair allow replacing the ITEM_LIST and create the items dynamically but this Prototype_Entry class does not allow this.

The CM_ENTRYLIST class
In Database Explorer and in your own programs at design (coding) time, we do not know the number of fields nor any other field information, therefore dynamic item creation is required. We need a substitute for the Prototype_Entry class to base our ELEMENT object on. The ideal substitute is a class from the DataFlex character mode class set called CM_ENTRYLIST. The characters "CM" stand for Character Mode. New items in an object of this class need to be created via the ENTRY$ITEM command (the undocumented variant of ENTRY_ITEM).

The ENTRY$ITEM command
The ENTRY_ITEM command uses this ENTRY$ITEM command when:

It is used within an ITEM_LIST
When the argument is not a file.field identifier
When more than one parameter is used (the entry option enclosed by curly braces '{}').

The use of entry options is superceded by Field_Options in Data Dictionary Objects.

In all other cases, the object is assumed to have only one item (e.g. dbForm) and in that case the command sets two properties instead of creating an item. This explains why we need to use the ENTRY$ITEM command.

Use a loop to create the ELEMENT object contents
In the following code, we use a loop for the item creation in the ELEMENT object:

   Object Element Is A CM_EntryList
      Set Focus_Mode To NonFocusable
      Move (Item_Count (hoFieldList) - 1) To iItems
      For iItem From 0 To iItems
         Get Select_State Of hoFieldList Item iItem To bSelected
         If (bSelected) Begin
            Get Aux_Value Of hoFieldList Item iItem To iField
            Entry$Item |FN0,0
            Move (Item_Count (Self) - 1) To iElementItem
            Set Data_File Item iElementItem To iFile
            Set Data_Field Item iElementItem To iField
         End
      Loop
      Move (Item_Count (Self)) To iColumns
   End_Object

hoFieldList is a variable that contains the object id of a LIST object that contains the field names and numbers of a certain table. The user can select one of more fields and we will find those fields by processing all the items in the list and checking whether they are selected or not.

The ENTRY$ITEM has |FN0,0 as parameter because the file and field number to be used will be assigned via the Data_File and Data_Field item properties.

Number of columns
The dbGrid needs to be informed about the number of columns before properties like Header_Label and Form_Width can be set. We do that via:

   Get Displayable_Rows To iDisplayableRows
   Set Item_Limit To iColumns
   Set Line_Width To iColumns iDisplayableRows
   Set Matrix_Size To iColumns iDisplayableRows

Setup the column properties
We use a second loop to create the column properties of the dbGrid:

   Move 0 To iColumn
   For iItem From 0 To iItems
      Get Select_State Of hoFieldList Item iItem To bSelected
      If (bSelected) Begin
         Get Value Of hoFieldList Item iItem To sFieldName
         Get Aux_Value Of hoFieldList Item iItem To iField
         Get_Attribute DF_FIELD_LENGTH Of iFile iField To iFieldLength
         Get_Attribute DF_FIELD_TYPE Of iFile iField To iFieldType
         Case Begin
            Case (iFieldType = DF_DATE)
               Set Form_Datatype Item iColumn To DATE_WINDOW
               Move 55 To iFormWidth
               Case Break
            Case (iFieldType = DF_BCD)
               Get_Attribute DF_FIELD_PRECISION Of iFile iField To iDecimals
               Set Form_Datatype Item iColumn To iDecimals
               Move (iFieldLength * 8) To iFormWidth
               Case Break
            Case (iFieldType = DF_ASCII)
               Set Form_Datatype Item iColumn To ASCII_WINDOW
               Move (iFieldLength * 8) To iFormWidth
               Case Break
            Case (iFieldType = DF_TEXT)
               Set Form_Datatype Item iColumn To ASCII_WINDOW
               Move (iFieldLength * 8) To iFormWidth
               Case Break
         Case End
         Set Header_Label Item iColumn To sFieldName
         Set Form_GuiWidth Item iColumn To iFormWidth
         Move (iTotalWidth + iFormWidth + 1) To iTotalWidth
         Increment iColumn
      End
   Loop

The field list of this sample
The sample used to build figure 1 uses a list object in which the fields of a particular table are shown. This list of fields is instantiated from the LIST class and filled dynamically upon activation. The code of this object is as follows:

   Object oFieldList is a List
      Set Size to 200 80
      Set Location to 5 5
      Set Select_Mode to Multi_Select

      //AB-StoreStart
      Property Integer piFieldListFile Vendor.File_Number

      Procedure DoReadFields
         Integer iFields iField iFieldListFile iFieldType
         String sFieldName

         Send Delete_Data

         Get piFieldListFile To iFieldListFile

         Get_Attribute DF_FILE_NUMBER_FIELDS Of iFieldListFile To iFields
         For iField From 0 To iFields
            Get_Attribute DF_FIELD_TYPE Of iFieldListFile iField To iFieldType
            If (iFieldType <> DF_OVERLAP And iFieldType <> DF_BINARY) Begin
               Get_Attribute DF_FIELD_NAME Of iFieldListFile iField To sFieldName
               Send Add_Item Msg_None sFieldName
               Set Aux_Value Item (Item_Count (Self) - 1) To iField
            End
         Loop
      End_Procedure // DoReadFields

      Procedure Activating
         Forward Send Activating

         Send DoReadFields
      End_Procedure // Activating
      //AB-StoreEnd

   End_Object // oFieldList

Destroy and rebuild
Each time the "rebuild grid" button is hit, the previous dbGrid - if present - will be deactivated and destroyed. The dynamic dbGrid logic shown in this white paper cannot use the same dbGrid object multiple times.

   Get phoSpecialdbGrid To hoSpecialdbGrid
   If (hoSpecialdbGrid > 0) Begin
      Send Deactivate Of hoSpecialdbGrid 0
      Send Disconnect_Deos_From_Servers To hoSpecialdbGrid Detach_Always
      Send Destroy Of hoSpecialdbGrid
      Set phoSpecialdbGrid To 0
   End

Notice the "0" behind the Deactivate message. Omitting this will cause the view to be deactivated.

Notes
The dynamic dbGrid shown here is capable to handle any file, it uses file and field information in a quite dynamic way. The use of such a real dynamic dbGrid is very rare. You often know what table the grid should use and field information may come from configuration data.

Download the attached source of the view, register it as an external component in the Order Entry sample workspace in the VDF IDE and click the Test Compile/Run button. The source code was created in Visual DataFlex 8.2.

^top

Data Access Worldwide
14000 SW 119 Ave
Miami, FL 33186
305-238-0012
Domestic Sales: 800-451-3539
Fax: 305-238-0017
email: sales@dataaccess.com
Newsgroup Server: news.dataaccess.com
Internet: http://www.dataaccess.com

Data Access Worldwide - Asia Pacific
Suite 5, 333 Wantirna Road, Wantirna VIC 3152 Australia
Phone: +61 3 9800 4233 f: +61 3 9800 4255
Sales: asiapacific@DataAccess.com
Support: support.asiapacific@DataAccess.com
Internet: http://www.DataAccess.com/AsiaPacific

Data Access Worldwide - Brasil
Av.Paulista, 1776 - 21st.Floor
São Paulo -SP - Brazil
CEP 01310-921
Phone: 5511-3262-2000
Fax 5511-3284-1579
Sales: info@dataaccess.com.br
Support: suporte@dataaccess.com.br
Internet: http://www.dataaccess.com.br

Data Access Worldwide - Europe
Lansinkesweg 4
7553 AE Hengelo
The Netherlands
Telephone: +31 (0)74 - 255 56 09
Fax: +31 (0)74 - 250 34 66
Sales: info@dataaccess.nl
Support: support@dataaccess.nl
Internet: http://www.dataaccess.nl

Data Access Technical Support
800-451-3539 / 305-232-3142
email: support@dataaccess.com
Visit our Support Home page to see all of our Support options: http://www.dataaccess.com/support

Copyright Notice
This document is property of Data Access Corporation. With credit to Data Access Corporation for its authorship, you are encouraged to reproduce this information in any format either on paper or electronically, in whole or in part. You may publish this paper as a stand alone document within your own publications conditional on the maintenance of the intent, context, and integrity of the material as it is presented here.

DataFlex is a registered trademark of Data Access Corporation.
Windows is a registered trademark of Microsoft Corporation.

NO LIABILITY FOR CONSEQUENTIAL DAMAGES
To the maximum extent permitted by applicable law, in no event shall Data Access Corporation be liable for any special, incidental, indirect, or consequential damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or any other pecuniary loss) arising out of the use of or inability to use any information provided in this document, even if Data Access Corporation has been advised of the possibility of such damages. Because some states and jurisdictions do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you.

^ top