how to display in the form combo box, two columns from the combo box

[titoneon]

Hi,
I attached a link that will show a form, that i have for data entry, in that form i have a combo1 control with a label named "Code desc" and this combo1 is bound to another table named "codedat" that has two fields named "code" and "descode", during the data entry when i select the combo box i am able to select the code i want wich is two character example "OA", and the field desc value for this OA is "machine wiring", ok at the point of selecting from the combo box i can see both columns cause i have a select sql command that will get the code and desc and will save it into the current table named "datatest", but later after the entry has been saved if use the navigation control to get back and forward thru out the records then the only thing displayed in the form combo1 is just the field value datatest.code, i can leave with but it is possible to display in the same combo1 the datatest.code + datatest.descode ?
please see if you can open the attachament, i did not know how to paste an image in this post
Thanks a lot
Ernesto

 

[Olaf Doschke]

collapsed the combobox always just shows column1, so you best do a query, which selects something like this:

select padr((code-",")+desccode,100) as codedesccode, code, desccode, id from datatest into cursor curCombo order by code, desccode

Let the combobox have 4 collumns for codedesccode, code, desccode, id and make columnwidths = "0,50,50,0" to only show code and desccode columns in dropdown mode, codedesccode is shown in collapsed mode anyway.

Bye, Olaf.

 

[titoneon]

Olaf,
I also forgot to mentioned in my post this
In the Combox controlsource i have: Datatest.code
in the combo1 destroy: use in select("Ccodedat")
in the " " interactivechange: public V_DESC, C1 where Vdesc and C1 are in the save button to replace the datatest.code with C1 and datatest.descode with V_DESC
in the combo1 lostfocus:
IF EMPTY(THIS.VALUE)
=messagebox("You need to select a Code, Press the Ok to input it" , 1)
retu 0
else
STORE THISFORM.COMBO1.VALUE TO C1
SELE CODEDAT
SET ORDER TO C_ODE
SEEK C1
store alltrim(CODEDAT.descode) to v_desc
endif
SELE DATATEST
GO RE_C
in the combo1 rowsource: select code, descode from codedat into cursor Ccodedat order by descode
" " " " rowsourcetype: 3 SQL statement
Property Style : Dropdown combo
Column count: 2
so all i want when displaying the records in the form is to show in the combo the code+ descode ?

Thanks

 

[titoneon]

Olaf.
select padr((code-",")+desccode,100) as codedesccode, code, desccode, id from datatest into cursor curCombo order by code, desccode

Where is code should go ? into the combo1 controlsource property ?
Ernesto

 

[Olaf Doschke]

Well, you still don' get it. The combobox, when closed, shows the first column of it's recordsource, no matter if array, table, fields or alias. Period, that's it, there's no way around that.
So if you want to display two columns, you need to put them into one column. That's what I did with the expression padr((code-",")+desccode,100).

Bye, Olaf.

 

[titoneon]

Olaf,
Thanks a lot i have to do something a little different but it worked, i got confused cause your "Select" was pointing to "datatest.dbf" and not to codedat.dbf" that is why i was asking if should i insert it on the Controlsource, but it is oK.
Regards

 

[Olaf Doschke]

Sorry to confuse you. I obviously didn't thoroughly understood your situation. Of course if thoese fields come from codedat change the select. I was just reading your quetion

"is it possible to display in the same combo1 the datatest.code + datatest.descode ?"[/code]

If you have fields code and desccode in both tables, the question is, if you handle primary and foreign key correctly in your database design. In my select I also added the last column "id", which is a placeholder for a primary key field. That should be the field you bind to and store in a controlsource foreignid field of another table. Then that also reselects the record, when you come back to the form later.

Don't have two foreign key fields. That only makes thing complicated.

Bye, Olaf.

 

[titoneon]

Olaf,
The primary key field in table "Codedat.dbf" is "code", the second field and the last one is "descode", ok so i have this table indexed by "code" so i use set order to C_ode cause i tagged as"C_ode", then in data entry form combo box control i have:
in the combo1 rowsource: select code, descode from codedat into cursor Ccodedat order by descode
" " " " rowsourcetype: 3 SQL statement
Property Style : Dropdown combo
Column count: 2

so when the user select the combo box, they will see the first column as "code" and the second as "descode" and course order by alphabetic order by "descode", but when i review a saved entry only the "code" is displayed. i did what you told me and i don't know why when i do it that way the combo box instead of selecting records from the table "Codedat" at the data entry process it is selecting or showing in the combo box data from another table i used before, during the data entry, i don't know why when using the padr command this is happening, once i remove the PADR and use as i have in the rowsource aboveb there is not problem but then i won't be able to see the two columns i dont why, maybe you meant that i have to have an extra field in the table "datatest" as "id" then use the select command as listed having the "id" colummn in the controlsource in the table "datatest" which is table that should record all the data entry ?,
Sorry it is a little confuse for me cause you are very advance programmer and i am not and maybe you assume i can get it really fast but sometimes for me is little dificult, ok not your fault at all.
Sorry for the inconvenient
Thanks

 

[Olaf Doschke]

Just turn away from the rowsourcetype sql statement. Do the SQL in code in init and use rowsourcetype alias and set rowsource to the alias.

Also, as code is your primary key you don't need an id, just bind to that column, let datatest.code be the controlsource and this way the choice of a record stores code in the datatest table, codedesc does not belong copied to datatest, or you introduce redundancy, but if that's causing trouble in other code you may stay with your way, that has nothing to do with the combobox functionality.

So what you need is:

Init-Code:
this.recordsource = ""
this.recordsourcetype = 0
select padr((code-",")+desccode,100) as codedesccode, code, desccode from Codedat into cursor curCombo order by desccode
this.recordsource = "curCombo"
this.recordsourcetype = 2

Let the combobox have 3 columns for codedesccode, code, desccode and make columnwidths = "0,50,50"
set boundcolumn 2 and controlsource datatest.code

That way the choice of a record automatically fills datatest.coe and datatest.code controls what record of the combobox items is shown. And you see that mechanism is fine for having single field primary and foreign keys, so you only copy over the primary key as foreign key to other tables and you always use that foreign key to lookup anything else in the original record. So any time in the future you'd need to show datatest.codedesc you'd rather take datatest.code and lookup the record in codedat with that code to find the codedesc. That's called normalisation, not only prevents redundancy, but also contradictory data and divergence.

Bye, Olaf.

 

[titoneon]

Olaf.
I will try those changes tomorrow monday, also i reliazed that you don't want me to save the value of codedat.descode into the table field
datatest.descode for redundancy but i print a report based only on table "datatest" so i use all the fields from that table to generate, that report, i know i can use also the codedat.descode in the report, but i will have then to link, create a relation or seek the code in codedat that macth the code in datatest and get the descode from codedat, that will make me do all these changes in an existing report
Thanks alot
Regards
Ernesto

 

[Olaf Doschke]

Well, you need to change someting anyway.

Either you need code in the combobox to copy over the desccode, but it's more usual in a normalized database design to need to query data into a cursor and base forms or reports on cursors anyway. For the sake of data integrity that's the price you pay, but it's not overcomplicated to do a single join query.

Bye, Olaf.

 

[titoneon]

olaf,
Correct me i am wron but

this.recordsource = "curCombo"
this.recordsourcetype = 2

these only applies to a Grid right ? no to a combo box. i don't find these properties for combo box and i put them into the "form init, i got error messages "property rowsource not found"
Thanks
Ernesto

 

[Olaf Doschke]

I always mistake the two rowsource and recordsource. Whatever it is for the combo, of course that's what you use. Since you get the error "rowsource not found", it doesn't seem related to the lines, though.

THIS.Reocrdsource of course cannot work in form init, since recordsource FOR SURE is not a property of the form, And in form init THIS is the form, in combobox init THIS is the combobox. THIS alway sis THIS, the object of which the code is. So both the property name and the location of the code is important.

Now let's look it up... the combobox has rowsource and rowsourcetype, but also these correct property names will only work in the combobox init, not in the form init.

Bye, Olaf.

 

[titoneon]

Olaf,
Ok, you got it, i have this now working as i want, the only thing when i am in the combo box and i want my mouse wheel to scrool up or down, it does not work, i know i went into the mouseWheelevent and i don't find and example there about this event how should work, do you have a link for an example on how to ?
Thanks a lot

 

[Olaf Doschke]

Try to update your driver.
A mousewheel will work, if the driver supports it, there is nothing you can do in foxpro to make it work, it either works or not.
If you tried to do anything in the Mouswheel event, simply use the property window to reset the mopusewheel event to default and the native behavior should work, if your mouse driver supports your mouse wheel.

Also, the mousewheel will only work, if there are enough items to have a scrollbar and most probably the mouse pointer needs to hover over the poped up items list.

Bye, Olaf.

 

[titoneon]

Olaf,
Another little thing a just realized is that when i open the form, the first thing that happened there is that the cursor for the combo box is initialized right away and if i want to scroll thru out the records on tABLE "DATATEST"using the navigation buttons, i am not able cause of the Cursor("curCombo") being the first to be active and available.

 

[Olaf Doschke]

Then do

SELECT DataTest

Bye, Olaf.

 

[titoneon]

Olaf,
Well i just came back to tell U, That was what i did in the combo box init while waiting for your answer and it worked
Thanks a lot again, hope no to bother U on this any longer.

 

[Olaf Doschke]

No, that's not bothering me.

In this case it would perhaps be best to do this in Form.Init() or even Form.Activate(), so no matter in what further controls you may create further cursor, finally the main table of the form is the active one.

Indeed, if you did your own navbar buttons the best solution would be to integrate selecting the table you navigate in the button clicks. If your navbar class is based on a container with several buttons, add a container property "alias" and set this to the form alias on the form you use your navbar. Then in the Skip button for example you do SELECT (This.Parent.alias), since the alias property is a container property and that code is in container.command1 for example, the object path from the button to the container is this.parent. And then you do the rest. If you don't do that you always depend on any code in your form to never change the current alias, and that is a restricting requirement and such requirements are bad, because they can easily be broken by yourself or third party code.

Bye, Olaf.

 

[titoneon]

yes i have this, navbar class is based on a container with several buttons, i will have to figure out how to work with that after i created the alias property in the form
Thanks, any advise as an example will be fine, you can check in my first post if you click in that link how my form and navbars looks like, not that it matters but just as a reference.