Display Value of Combo Box In Grid

[IForgot]

I have 2 related tables:

Table_1 Table_2
Field1 Field1 Field2
ABC ABC Tom
ABC Carol
---------------------------------
DEF DEF Jane
DEF Bill
---------------------------------
XYZ XYZ Leane
XYZ Thomas

The index and relation are set up and working fine.

In a VFP7 application I have a Grid on a Form which is using Table_1 as its Grid.RecordSource.
That also works fine for Columuns 1 & 2 (field not shown).

In Column 3 of the Grid I have Combo box objects which I want to display only those Child Table (Table_2) records specifically related to the individual Parent Table (Table_1) records.

How can I make this work?

What needs to be set up in the ComboBox's RecordSource and ControlSource, etc.?

Thanks,
I_Forgot

 

[BPeisch]

Are you showing the combo, even when it doesn't have focus, or are you only showing a textbox when it doesn't? Is the combo supposed to show a user's selection from that subset of records? Sorry for all the questions, but I'm trying to get a better feel for what you're looking for.


-BP (Barbara Peisch)

http://www.peisch.com

 

[IForgot]

I have deleted the Textboxes from these Columns so all that is left is the ComboBox object.

I have also set the Column.Sparse = .F. so that the ComboBox does indeed show when the Form is run.

But nothing appears in its 'rows'.

The setting combinations that I have tried are:
ComboBox.RowSource = Table_2.Field2
ComboBox.RowSourceType = 2 && Alias
ComboBox.ControlSource = <undefined>

ComboBox.RowSource = Table_1.Field2
ComboBox.RowSourceType = 2 && Alias
ComboBox.ControlSource = Table_2.Field2

Neither of which worked to display the "Child" table's records which were related to the "Parent" table's record.

I forget which one, but one of the setting combinations displayed ALL Table_2.Field2's - disregarding the fact that only 2 Table_2 records were related to the selected row.

Any suggestions?

Thanks,
I_Forgot

 

[darrellblackhawk]

Here's a possible start as it almost works.

The main problem is with the values shown in the
textbox of the combobox when you downclick the combo's
list arrow, and move one of the grids scroll bars.

Haven't got the display portion of the combo to at
least show the first related child record yet.

This will probaby be possible by keeping the textbox
and setting the DynamicCurrentControl of the special
column to switch between the textbox and the combobox
when the user moves to a given record.

Don't have time to play with it anymore right now, but
maybe this will help you in some way.


Darrell

Local oForm

oForm = CREATEOBJECT("clsGridWithRelatedChildRecordsInComboBox")
oForm.SHOW()

Read EVENTS

Set DATABASE TO testdata
Close DATABASE

Define CLASS clsGridWithRelatedChildRecordsInComboBox AS FORM
  DoCreate = .T.
  AutoCenter = .T.
  DataSession = 2
  Width = 800
  Height = 490

  Add OBJECT grdCustomerNOrders AS GRID WITH;
    TOP = 10, ;
    LEFT = 10, ;
    WIDTH = THIS.WIDTH - 20, ;
    HEIGHT = THIS.HEIGHT - 20, ;
    RECORDSOURCE = "customer"

  Procedure LOAD
    Create CURSOR tmporders (order_id c(6))
    Use _samples+"\data\customer.dbf" ORDER company IN 0 SHARED
    Use _samples+"\data\orders.dbf" ORDER cust_id IN 0 SHARED


    * Turned off relation for now
    *!*	    Select customer
    *!*	    Set relation to cust_id into orders addi
  Endproc

  Procedure INIT
    This.REFRESH()
  Endproc

  Procedure DESTROY
    Clear EVENTS
  Endproc

  Procedure grdCustomerNOrders.INIT
    With THIS
      .ADDCOLUMN(3) && Insert a column, but new columns index will still be the max index
      
      * Played around with leaving the textbox in the grid so
      * I set the textboxes controlsource to orders.order_id and
      * then using the DynamicCurrentControl property to perform
      * some slight of hand. Took it out to at least show it almost
      * works.

      .COLUMNS(.COLUMNCOUNT).REMOVEOBJECT("text1")
      
      .COLUMNS(.COLUMNCOUNT).ADDOBJECT("cntOrders","clsCntWithCombo")
      .COLUMNS(.COLUMNCOUNT).SPARSE = .F.
      .COLUMNS(.COLUMNCOUNT).VISIBLE = .T.
      .COLUMNS(.COLUMNCOUNT).cntOrders.combo1.ROWSOURCETYPE = 3
      .COLUMNS(.COLUMNCOUNT).cntOrders.combo1.ROWSOURCE = ;
        "select order_id from orders a where a.cust_id = customer.cust_id into cursor tmporders"

      .COLUMNS(.COLUMNCOUNT).header1.CAPTION = "Orders"
      .COLUMNS(.COLUMNCOUNT).WIDTH = .COLUMNS(.COLUMNCOUNT).cntOrders.combo1.WIDTH
      .ROWHEIGHT = .COLUMNS(.COLUMNCOUNT).cntOrders.combo1.HEIGHT
    Endwith
  Endproc

Enddefine


Define CLASS clsCntWithCombo AS CONTAINER
  BorderWidth = 0
  BackStyle = 0
  Add OBJECT combo1 AS COMBOBOX

  Procedure combo1.LOSTFOCUS
    This.LISTINDEX = 0
  Endproc
Enddefine

 

[IForgot]

I have a "Sort-of" working approach, but it is not 100% working yet.

Perhaps, with this as a basis, you can get me headed in the right direction.

In the over-all Grid's WHEN method I am now attempting to populate the ComboBoxes with individual record's Related "child" table values as the user scrolls through the Grid records.

SELECT Table_1 && "Parent" table
mcAppl = Table_1.Field1 && <<-- Common Field Value Between Tables

FOR i = 4 TO 6
* --- Only ComboBox Columns 4, 5, & 6 ---
WITH THISFORM.PAGEFRAME.TabPage3.Grid.COLUMNS
.ComboBox.RowSourceType = 0
.ComboBox.CLEAR

SELECT Table_2 && Related "Child" Table
LOCATE FOR APPL = mcAppl
DO WHILE APPL = mcAppl ;
AND !EOF()
* --- Add In Values From Related Table Records ---
.ComboBox.ADDITEM(Table_2.Field2)
SKIP
ENDDO

.ComboBox.ENABLED = .T.
.ComboBox.VISIBLE = .T.
ENDWITH
ENDFOR
SELECT Table_1 && "Parent" table

But the results seem to set ALL of the ENTIRE column's ComboBox to the Table_1.Field1's 'child' table value from record 1.

It did not set each individual "row's" ComboBox values to the individual row's related "child" table record values as anticipated.

Any suggestions?

Thanks,
I_Forgot

 

[darrellblackhawk]

The problem is due to the fact that the controls in the
grid are only one per column.

Because of this it's impossible to populate the comboboxes
with different values for different rows at the same time.

Maybe a simpler approach would be to just add a separate
combobox to the form that gets populated when the row in the grid changes.

Darrell

Example:

Local oForm, cCurDBC
cCurDBC = JUSTSTEM(DBC())

oForm = CREATEOBJECT("clsMyForm")
oForm.SHOW()
Read EVENTS

Set DATABASE TO testdata
Close DATABASE

If !EMPTY(cCurDBC)
  Set DATABASE TO (cCurDBC)
Endif


Define CLASS clsMyForm AS FORM
  DoCreate = .T.
  AutoCenter = .T.
  DataSession = 2
  Width = 800
  Height = 490
  Caption = "Customers with orders"

  Add OBJECT lblCust AS LABEL WITH ;
    TOP = 10, ;
    LEFT = 10, ;
    CAPTION = "Customers"

  Add OBJECT grdCustomers AS GRID WITH;
    TOP = THIS.lblCust.TOP + THIS.lblCust.HEIGHT + 4, ;
    LEFT = 10, ;
    WIDTH = THIS.WIDTH - 120, ;
    HEIGHT = THIS.HEIGHT - 74, ;
    RECORDSOURCE = "customer"

  Add OBJECT lblOrders AS LABEL WITH ;
    TOP = 10, ;
    LEFT = THIS.grdCustomers.LEFT+THIS.grdCustomers.WIDTH + 5, ;
    CAPTION = "Orders", ;
    AUTOSIZE = .T.

  Add OBJECT cboOrders AS COMBOBOX WITH ;
    TOP = THIS.grdCustomers.TOP, ;
    LEFT = THIS.lblOrders.LEFT, ;
    ROWSOURCETYPE = 3, ;
    ROWSOURCE = ;
    "select order_id from orders where orders.cust_id == customer.cust_id into cursor tmporders"

  Procedure LOAD
    Use _samples+"\data\customer.dbf" ORDER company IN 0 SHARED
    Use _samples+"\data\orders.dbf" IN 0 SHARED
  Endproc

  Procedure DESTROY
    Clear EVENTS
  Endproc

  Procedure CustRowChanged()
    Thisform.cboOrders.REQUERY()
    Thisform.cboOrders.LISTINDEX = 1
    Thisform.lblOrders.CAPTION = "Orders: "+TRANSFORM(RECCOUNT("tmporders"))
  Endproc


  * Control(s) Methods:

  Procedure grdCustomers.AFTERROWCOLCHANGE
    Lparam nColIndex
    Thisform.CustRowChanged()
  Endproc

Enddefine

 

[BPeisch]

I'm still not sure of the point of the combo. Are you allowing users to select a name to store in another table, or are you only using the combo to show related records? If you're using the combo to show related records, this doesn't sound like the correct interface.


-BP (Barbara Peisch)

http://www.peisch.com

 

[darrellblackhawk]

Barbara:

I think what they are trying to accomplish is show related
records in a combobox in the same grid that's based on the
parent table. Of course this is almost impossible to do
because of the way grids work, although it would be nice
if it did.

I tried to fake it by placing a combobox inside of a
container in the grid, but that doesn't quite work properly
either.

Ergo...

I recommended placing a combobox directly on the
form and requerying(?sp) it when the grid row changes.

Darrell

 

[BPeisch]

If it's really necessary to use a combo to show related records, I'd rather set the column's Sparse to .T. and only show the combo when it gets focus. I'd then requery the combo in the GotFocus().


-BP (Barbara Peisch)

http://www.peisch.com

 

[darrellblackhawk]

I agree.

The one problem they'll encounter is if they don't
set the listindex of the combo to zero when it looses
focus and clicks one of the grid's scroll bars.

In that case some of the psuedo combos will show the
last diplay value; which of course is incorrect.

Darrell

 

[IForgot]

Barbara -
"I'm still not sure of the point of the combo. Are you allowing users to select a name to store in another table?" Yes

I need to display the all options available (resident in related 'child' table) so that the user may make their desired selection and store the selection to the 'parent' table.

At this point I am even closer to getting it to work (not 100% yet, but closer).

I am no longer relying on the Relation in order to get my ComboBox Row items.
Instead I put a SQL Query into the ComboBox WHEN Method to interrogate the 'child' table for the appropriate values and then, similar to above, I populate the ComboBox 'rows'.


* --- From Grid's ComboBox WHEN Method ---
SELECT Table_1
mcParentVal = Table_1.Field1
mcValue = This.DisplayValue

IF EMPTY(mcValue)
* --- Only re-populate 'rows' if EMPTY() ---
WITH THIS.Parent
* --- Eliminate Any Old ComboBox 'Row' Data ---
.ComboBox.RowSourceType = 0
.ComboBox.CLEAR

* --- Get New 'Row' Data ---
SELECT Field2 ;
FROM Table_2 ;
WHERE Table_2.Field1 = mcParentVal ;
NOCONSOLE ;
INTO CURSOR temp && Related "Child" Table

SELECT Temp
SCAN
* --- Add In Values From Related Table Records --
.ComboBox.ADDITEM(Table_2.Field2)
ENDSCAN
USE

.ComboBox.ENABLED = .T.
.ComboBox.VISIBLE = .T.
ENDWITH
ENDIF
SELECT Table_1

This approach seems to be providing me with the appropriate 'child' table values in each of the Grid Row's ComboBoxes.

The one last remaining challenge is 'persistence'.
The ComboBox values in the Grid show up fine when selected, but if I scroll the row off-screen or if I go to another Form Tab page and return, the ComboBox values disappear.

Perhaps my ComboBox.ControlSource is not set up correctly so as to return the saved value as its 'default' display.

Thanks,
I_Forgot

 

[BPeisch]

What kind of field are you using in the parent table to store the name selected? Are you using a field that stores the name itself, or are you just storing a foreign key that points to the record in the child table? If you're using a name field in the parent, then just using that as the ControlSource should be adequate.


-BP (Barbara Peisch)

http://www.peisch.com

 

[IForgot]

My progress has been slow, but at least it has been primarily in the forward direction...

Originally I had Deleted the TextBox from the ComboBox Columns, but I was advised to add them back in.

Now I have added them (one per column) back in.
I have also set the initial properites as follows:
Column5.Sparse = .T.
Text1.ControlSource = Table_1.SavValueFld
Text1.Visible = .T.
Combo1.ControlSource = Table_1.SavValueFld
Combo1.Visible = .F.

Now, when I enter a new record I reverse the VISIBLE settings so that
Text1.Visible = .F.
Combo1.Visible = .T.
the ComboBox appears and the choices are available.

And on the VALID Method I do the following:
1. REPLACE Table_1.SavValueFld WITH Combo1.DisplayValue
2. Re-Set the VISIBLE
Text1.Visible = .T.
Combo1.Visible = .F.
3. Grid.Refresh

This is getting me yet closer to what I need.

My one remaining problem is that when I change to a different PageFrame Tab Page and then return to the Grid's Tab page, the Value's have once again disappeared.

Thanks,
I_Forgot

 

[darrellblackhawk]

I think you are fighting a losing battle.

Try - as suggested - adding a separate combobox to the
form which gets populated as the grid row changes.

I don't think you'll ever get rid of some of the
annoyances due to the way grids work.

Darrell

 

[BPeisch]

I don't think it was necessary to add the textboxes back, but that's water under the bridge.

The problem with switching pages may be fixed by adding a This.Refresh() to the activate method of the page with the grid. FWIW, I have created many grids with combos. I use an integer foreign key in my tables to point to the selected child record. The way I've found that works best is to create a view that has both the foreign key and the name from the child record. I bind the column to the name field in the view, and in the combo's valid, I replace the foreign key with the appropriate column from the combo.


-BP (Barbara Peisch)

http://www.peisch.com

 

[MarciaAkins]

Hello I_Forgot.

>> In Column 3 of the Grid I have Combo box objects which I want to display only those Child Table (Table_2) records specifically related to the individual Parent Table (Table_1) records.

How can I make this work? <<

First of all, leave the grid column's Sparse setting as .T. Then what you want to do is to set up a parameterized view to use as the RowSOurce of the Combo box. Then, use code something like this in the combo's GotFocus():

LOCAL lcGridAlias, vp_cl_key
DODEFAULT()
WITH This
*** Requery the locations view to obtain all the locations for
*** The client displayed in the current grid row
lcGridAlias = .Parent.Parent.RecordSource
vp_cl_Key = &lcGridAlias..Cl_Key
REQUERY( 'lv_location' )
*** Refresh the combo
.Requery()
.Refresh()
ENDWITH

You can get some sample code and a white paper from my web site from a session that I gave at WhilFest in 2000 called "How to put a combo box in a grid".


Marcia G. Akins

http://www.TightlineComputers.com