The dbList class unmystified

The dbList Class unmystified

By Wil van Antwerpen

This article is work-in-progress, it is far from complete, but it seems for me that it is better to publish it now as it is, as just wait until it is finished. The bases of this document is written after a lecture that I gave at a UK Sig meeting.
All comments are welcome.

The meaning of this document is to provide you some extra information about how to use the dbList class in addition to what is currently available in the online help file. We advise you to first read the dbList class topic in the helpfile.
The helpfile shows you a somewhat simplified image of the class structure in the help. It tells you that it is based like this:

EntryList
WideList
    DataList
       dbGrid
       dbList

When you investigate the packages folder, you will see a slightly different picture.
dbList Class Inheritance.gif (24401 bytes)

This does not directly help us in gaining a clearer picture of the dbList class. It does tell us however that the DAC presentation of the classes are easier to understand as the actual real thing. Having this total view of the classes inheritance is a must have as soon as you start investigating the package files.

The next thing that we have a look at is the object structure of a dbList object instance. Every dbList object will have the following child-objects; records, element, selected_items, colck_box, statushelp_array and sbar. All of these child-objects are arrays and give some extra column or row-based settings.

dbList Object.gif

Element object = prototype object
Record object = recordnumbers (with the default: batch_state = false)
Selected_items object = selected recordnumber (batch_state = false)

A problem with customizing a lookuplist is very often that there's no overview of when to take action. The thing to do in that case is to start up the debugger and run a trace from the point of interest. What we have done for you is running a few standard traces from the customer lookuplist in the order entry example. From the tracelist we took the most interesting messages and put these here down below. We divided this into three traces. The first during popup, the second when scrolling thru the list (page down on bottom row) and a third one when selecting a record by clicking on the Ok-button in the list.

Simple popup trace

Send activating
Send Seed_List
Send Read_Next_Record
Send Initialize List
Send Read_Next_Record for every row
Send activate
Send Entering
Send OnSetFocus
Send OnSetFocus

Simple scroll trace

Send Down_Row
Send Add_Row
    Set Current_Item
      Send Item_Change
        Get Row_Changing
          Get Fill_Next_Row
            Send Read_By_Recnum
            Send Read_Next_Record
            Send Display_Row
          Send Trim_Page
            Send Delete_Row
          Send Display_Other_Ui
            Send Display_Ui
              Send Request_Assign To hoServer

Simple select trace

Send Onclick To oOk_bn
Send Ok To hoSellist
    Send Move_Value_Out
      Get First_Selected_Item
      Get Current_Record
      Send Find_By_Recnum To hoServer
     Send Find_By_Recnum To (Server(hInvoking))
     Get Display_Buffer_Msg Of hoSellist
   Send Cancel_Scope To hoSellist
     Send Deactivate To hoSellist

Click here for a detailed trace

The Datadictionary connection

The dbList is a bit strange in comparison to the other database enabled controls.
In Visual DataFlex we are used to having a data-entry control displaying one record that is tied directly to the datadictionary. In fact you are encouraged to get the datadictionary values instead of the item value, just to make sure that you are using the same data as is in the file’s buffer.

The datadictionaries settings ‘tell’ the control how it should behave and what the business rules are.
The multiline classes such as the dbList and the dbGrid classes do have this same behaviour, but the technique used is slightly different.
During construction of the dbList object several internal objects are created.
One of them is the element object and is normally referenced as the prototype object.
The prototype object is created by the macro commands Begin_Row/End_Row. This object is used as some sort of template for the creation of the rows.

During activation the datadictionary rules are bound to the prototype object and this object functions as a model for the columns on every row.
It contains the data_file, data_field, options, masks and entering/exiting/validation messages of each field.
The prototype object is copied to a new row for each new record that needs to be displayed.
This way every new row is exactly the same for the entire column.

The record of the datadictionary is only connected to the current row.

All the other rows get their data-values directly from the recordbuffer and are masked with the values set in the column. For work on scrolling like dynamically coloring cells the entry_display message can be augmented to do the work for you. With this message you have to use the value of the recordbuffer.

The following properties have these value for the dbList object above:

Property values:
Displayable_rows: 9
Current_row: 5 (zero based = base_item/item_limit)
Current_col: 1 (zero based = current_item-base_item)
Current_item: 21 (zero based)
base_item: 20 (zero based)
item_count: 36 (zero based)
row_count: 9
item_limit: 4

note: during scrolling the row_count will be temporarily incremented by one until after the scroll.

Common questions and answers regarding dbLists or dbGrids:

Q: How can I show an aggregated value in a column. For example, how can one display an inventory value of items on hand?

A:

There are several ways to do this.
The first one is to use an expression as a column. Example:

Object oSelList is a dbList
Set Main_File to Invt.File_Number
Set Ordering to 1
Set Size to 105 273
Set Location to 6 6

Begin_Row
    Entry_Item Invt.Item_id
    Entry_Item Invt.Description
    Entry_Item (Invt.Unit_price * Invt.On_hand)
End_Row

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

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

Set Form_Width item 2 to 48
Set Header_Label item 2 to "OH Cost"

//AB-StoreStart
Set Currency_Mask item 2 to 6 2
//AB-StoreEnd
End_Object // oSelList

The second solution would be to use a formula for to calculate the value:

Object oSelList is a dbList

//AB-StoreTopStart
// Note that you must use the recordbuffer instead of the DDO values, because
// this gets executed during entry_display.
Function InvtOnHandCost Returns String
    Function_Return (Invt.Unit_price * Invt.On_hand)
End_Function // InvtOnHandCost
//AB-StoreTopEnd

Set Main_File to Invt.File_Number
Set Ordering to 1
Set Size to 105 232
Set Location to 6 6

Begin_Row
    Entry_Item Invt.Item_id
    Entry_Item Invt.Description
    Entry_Item (InvtOnHandCost(Self))
End_Row

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

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

Set Form_Width item 2 to 48
Set Header_Label item 2 to "OH Cost"

//AB-StoreStart
Set Currency_Mask item 2 to 6 2
//AB-StoreEnd
End_Object // oSelList

The third and in this particular case the best solution would be to add an extra field into the file that contains the OH cost. This way you can add an index to it, so that your users can sort on the cost column. This would need the following code in the Invt Datadictionary:

Procedure Update
Forward Send Update
Add (Invt.Unit_price * Invt.On_hand) To Invt.OHCOST
End_Procedure // Update

Procedure Backout
Forward Send Backout
Subtract (Invt.Unit_price * Invt.On_hand) From Invt.OHCOST
End_Procedure // Backout

Q: What is a good hook to use in the dbGrid/dbList class when changing rows?

A:

There are several hooks but one of them is the function Row_Changing

Function Row_Changing Integer iFromItem Integer iToItem Returns Integer
Local Integer iDestItem
Local Handle hServer
Get Server To hServer

// before the row change happened
// function current_row has the current row#
// current record is in the buffer of the DDO

Forward Get Row_Changing iFromItem iToItem To iDestItem

// After the row_changing.
// If (Current_Record(hServer) =0) we are at a new row
// If iToITem NE iDestItem we are either scrolling or the
// row did not get saved

Function_Return iDestItem
End_Function // Row_Changing

Q: How should one set up an optional constraint with a selectionlist, that is different depending on the view that invokes it?

A:

The first solution is to use the datadictionaries from the invoking view, you can do this by setting the property Auto_Server_State To True

This will not always give you what you want especially when you have the need for a dynamic constraint.
A way to achieve this is to set the constraint in the DDO of the Lookup object depending on the value of a property.
We take the orderentry workspace again as an example and decided that we want to see a constrained lookuplist of all articles cheaper as 100,- in the orderentry view and all articles in Invt view.

First we define a property to use for our settings in the dbModalPanel lookup and set up a constraint for it.

CD_Popup_Object Invt_sl is a dbModalPanel

//AB-StoreTopStart
Property Integer pbCheapArticles Public 0
//AB-StoreTopEnd

Set Minimize_Icon to FALSE
Set Label to "Inventory List"
Set Size to 148 287
Set Location to 4 5

//AB-DDOStart

Object Vendor_DD is a Vendor_DataDictionary
End_Object // Vendor_DD

Object Invt_DD is a Invt_DataDictionary
Set DDO_Server to (Vendor_DD(self))

//AB-StoreStart
Procedure OnConstrain
    If (pbCheapArticles(Self)) Begin
      Constrain INVT.UNIT_PRICE Lt 100
    End
End_Procedure // OnConstrain

//AB-StoreEnd

End_Object // Invt_DD

Set Main_DD to (Invt_DD(self))
Set Server to (Invt_DD(self))

//AB-DDOEnd

Object oSelList is a dbList
    Set Main_File to Invt.File_Number
    Set Ordering to 1
    Set Size to 105 273
    Set Location to 6 6

    Begin_Row
      Entry_Item Invt.Item_id
      Entry_Item Invt.Description
      Entry_Item Invt.Unit_price
      Entry_Item Invt.On_hand
    End_Row

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

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

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

    Set Form_Width item 3 to 40
    Set Header_Label item 3 to "On Hand"


    //AB-StoreStart
    Set Currency_Mask item 2 to 6 2
    Set Numeric_Mask Item 3 to 6 0
    //AB-StoreEnd

End_Object // oSelList

Object oOK_bn is a Button
    Set Label to "&Ok"
    Set Location to 115 119

    //AB-StoreStart
    Procedure OnClick
      Send OK To oSelList
    End_Procedure
    //AB-StoreEnd

End_Object // oOK_bn

Object oCancel_bn is a Button
    Set Label to "&Cancel"
    Set Location to 115 174

    //AB-StoreStart
    Procedure OnClick
      Send Cancel To oSelList
    End_Procedure
    //AB-StoreEnd

End_Object // oCancel_bn

Object oSearch_bn is a Button
    Set Label to "&Search..."
    Set Location to 115 229

    //AB-StoreStart
    Procedure OnClick
      Send Search To oSelList
    End_Procedure
    //AB-StoreEnd

End_Object // oSearch_bn

//AB-StoreStart
On_Key Key_Alt+Key_O Send KeyAction To oOk_bn
On_Key Key_Alt+Key_C Send KeyAction To oCancel_bn
On_Key Key_Alt+Key_S Send KeyAction To oSearch_bn

Procedure Exiting_Scope Integer hNew_Scope
    Set pbCheapArticles To False
    Forward Send Exiting_Scope hNew_Scope
End_Procedure // Exiting_Scope
//AB-StoreEnd

CD_End_Object // Invt_sl

Since we are connecting the selectionlist to the datadictionary, we need to make sure that the previously defined constraint is not used if we invoke the lookup from another view. Clearing the property is enough since a rebuild_constraint is automatically sent during activation (see the detailed invoke lookup trace list for more info)

The order entry view, is extended a bit with the prompt callback message to set up the dynamic constraint. Note that you have to sent the rebuild_constraint to activate it. You have to do this manually because the prompt_callback message is sent to the invoking object after the rebuild_constraint that is sent by the activation schema.

Object OrderDtl_Grid is a dbGrid

Set Main_File to Orderdtl.File_Number
Set Server to (Orderdtl_DD(self))
Set Ordering to 1
Set Wrap_State to TRUE
Set Size to 63 377
Set Location to 90 3

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 116
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"

//AB-StoreStart
//-------------------------------------------------------------------
// Change: Table entry checking.
// Set child_table_state to true which will
// cause table to save when exiting and
// attempt to save header when entering.
//-------------------------------------------------------------------

Set child_table_state to TRUE // Saves when exit object

// Called when entering the table. Check with the header if it
// has a valid saved record. If not, disallow entry.
//
Function Child_entering returns Integer
    Integer rval
    // Check with header to see if it is saved.
    Delegate Get Save_Header to rval
    function_return rval // if non-zero do not enter
End_Procedure

Procedure Prompt_Callback Integer hLookup
    Local Integer iColumn
    Get Current_Col To iColumn
    If (iColumn = 0) Begin
      Set pbCheapArticleS Of hLookUp To True
      Send Rebuild_Constraints To (Server(hLookup))
    End
End_Procedure // Prompt_Callback

//-------------------------------------------------------------------
// Change: Assign insert-row key append a row
// Create new behavior to support append a row
// Optimize the table refresh
//-------------------------------------------------------------------

On_key kADD_MODE send append_a_row

// Add new record to the end of the table.
procedure Append_a_Row // Q: how would a keyboard do this?
    Send End_Of_Data // A: Go to end of table and
    Send Down // down 1 line to empty line
End_Procedure

// The way this table is set up, items can never be added out
// of order. New items are always added to the end of the table.
// By setting Auto_Regenerate_State to false we are telling the
// table to never bother reordering after adding records. This is
// a minor optimization.
Set Auto_Regenerate_state to false // table is always in order.

//AB-StoreEnd

End_Object // OrderDtl_Grid

The third answer to this question is the quick and dirty solution: Create different selectionlists. If the other ones take too much time, this may help quite good. This is not elegant and it isn't satisfying, but if it works....

Q: I don't like the Color settings of DAC for the current row and cell, can I change these?

Yes you can very easily by applying some registry settings in your workspace. The following link will set up a new color schema for the order entry example.
In one of our projects we have a configuration dialog to change this dynamically. Eventually this will be published on the site, but as long as it is not, you may contact me personally to get a copy by email.

Q: Is it possible to change the style of a checkbox in a dbList?

Yes, it is very easily in fact the checkboxes that you see in a dbList or dbGrid are in fact just bitmaps. Here is a small excerpt from the dfdata.pkg that shows you how a checkbox is displayed.

// This can be called from with entry_display to display a
// checkbox. This is used for non-data entry checkboxes (most
// often in sel-lists).
// Displays a checkbox for the item passed based on passed state.
// Display different ckboxes for displayonly and not. This can be
// sent to display a checkbox. If a developer wishes to use different
// bitmaps, they can augment this message.
procedure DoDisplayCheckBox integer iItm integer bSt
local integer bSh
Get item_shadow_state item iItm to bSh
set form_bitmap item iItm to ;
(if(bSt, if(bSh,"ChkOnD.bmp","ChkOnE.bmp"),;
if(bSh,"ChkOffD.bmp","ChkOffE.bmp") ))
end_procedure

You can get some bitmaps here to replace the standard checkboxes. Copying these in the bitmaps folder of your workspace will do.

standard style:

attached style1:

attached style2:

Hints:

Do Not Use OnSetFocus/OnKillFocus as a hook message unless you don’t care that OnSetFocus is sent twice and OnKillFocus once during activation of your selectionlist.

Another reason for this is that OnSetFocus is also sent after switching applications with for example <Alt-tab>

Set Line_Display_State to True if you use request_assign, otherwise the entire dbList object gets repainted. You wont really see this on your development system, but you will on a network with a reasonably sized database.

Current Known problems with the dbList Object:

Searching on a multi-segment index doesn’t initialize the buffer
Search on a numeric field shows the value as if it is a real number (changed in VDF7)
Thumb behaviour is a bit odd (fixed in VDF7)

Related articles:

DAW Technical Knowledge base

194-Slow Filling dbLists in VDF
1058-EXAMPLE: Automatically saving a dbgrid lineitem and moving to next row
1047-dbList 'select all' code
864-Constraining a selection list based on the data in the view
856-Suggestions for Use of Load_Buffer_msg in dbList Class
331-creating multi-select and display lists

Online Helpfiles

DbList class