ComboBox help needed... 1

[stanlyn]

Hi,

Using VFP9sp2 and the fixes, I've struggled for years for an elegant solution for combo boxes. The behavior I've used before works but is kluggy and way too many steps. Maybe someone can steer me toward an elegant solution. I suspect it to be a common usage use.

Lets say we have 2 tables, Invoices and PayTypes, where Invoices has a field that contains the pay type used for that particular invoice. The PayTypes table contains a list of available pay types such as visa, cash, net30, m/c and amex.

Now, on the Transaction data entry form I need the combobox's displayvalue to shows the value saved for that particular invoice. The displayvalue will change as you skip about the Invoice table's records.

Now, when clicking the combobox, it should show the list of available types populated from the PayTypes table with the saved Invoice's selection already selected, therefore by pressing enter it would not change anything, however if you select any other type in the list, the combo would close and save that selection to the Invoice and show it in the displayvalue.

To get this functionality, what pems and their settings is required. I've fought this on and off for years now and have really never found a proper and simple solution. Please be specific as possible...

Thanks,
Stanley

 

[EzLogic]

I had the same issue and what i did is I created a container class with a textbox and a button to pick (like a picker)
so, the value of the paytype displayed in the textbox and if they clicked "..." to pick/see other values, they have a small modal form that displayed a nice little grid with ALL payment types and 2 buttons "Select" and "Exit"

I hate using combo boxes for things like this.



Ez Logic
Michigan

 

[stanlyn]

Hi EzLogic,

Yes, that is an very elegant solution, but not easy as it requires a lot of wireup time every time we need this functionality. I can see creating a modal form and passing in the title bar text, table name, index, selected value, field list and more in an effort to make it generic. Then call the form with params...

All of that kind of defeats the general simplicity of what a combo box should be able to do, doesn't it?

The best I ever done with it was:

The invoice form has a textbox bound to a field and represented the saved value in the Invoice table's field.
Underneath the textbox was a combobox whoose displayvalue was equal to "". I used the combobox as a lookup to the PayTypes table and once an item was selected, I copied its value to the textbox value. This process was hiding and showing the textbox when necessary. The down arrow on the combobox was always visible, however the textbox set ontop and obscured the left side of the combobox. This is why I call it kluggy... It worked great and sometime I had display issues if a different system used a different font or size. This was however easy and fast to implement.

When you were experiencing the same issues, did you seek help elsewhere?

Anyway, great idea...

Thanks,
Stanley

 

[Olaf Doschke]

Why? This is a basic use of the combobox.

Style: 2 - Dropdown box. No typing allowed, only picking values from the list.
Controlsource: Invoices.PaytypeID - the value controlling the selected item and being set by choosing another.
RowSourceType: 6 - Fields
RowSource: Paytypes.name,ID
BoundColumn: 2
Columncount: 2
ColumWidths: 100,0 - so you only see the pay type name, not the ID.

Just using the standard combobox builder you get these values set, too.
And just to make clear: BoundTo has to stay .F., as you want to bind the controlsource and not the List array. The BoundTo property very often is misunderstood to cause binding at all, so it must be .T., but that's not the case. Reading the help topic of properties really helps understanding the control much better than experimenting. Especially if you do so for years. Just spend half an hour to half a day and you know how to use the combobox. And note the topics of properties typically cover more than just the control you come from, so you learn more than just the combobox.

Now there are some optional things like incrementalsearch and more, but that's essentially all to do. Admitted, it's many properties.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

I'll try your setup and study the docs. I'm also confused when you say BoundTo must be .F. and the next sentence you say it must be set to .T.

So how will the combobox's displayvalue reflect the Invoice's value as you skip around in the Invoice table. I see nothing that ties it to the Invoices. All your listed properties are referencing the PayTypes table.

Thanks,
Stanley

 

[Olaf Doschke]

In the second sentence I said:

The BoundTo property very often is misunderstood to cause binding at all, so it must be .T., but that's not the case.

With this I'm just stressing out the fact, that .T. is what people assume is correct, but it's not.

Bye, Olaf.

 

[TamarGranor]

The only time BindTo needs to be .T. is when the BoundColumn is numeric and you want Value to be set to the actual numeric value.

When the BoundColumn is numeric and BoundTo is .F., Value contains the ListIndex instead.

Tamar

 

[Olaf Doschke]

You're right. The help on BoundTo says - in short:

.T. - The Value property is determined by the List property

.F. - (Default) The Value property is determined by data type of the variable or field specified in the ControlSource property.
If the variable or field specified in the ControlSource property setting is of character type, the Value property is determined by the List property.
If the variable or field specified in the ControlSource property setting is of numeric type, the Value property uses the index number from the ListIndex property.

And so, if the ID is numeric (eg int, autoinc or normal int) I'm wrong and you have to set BoundTo to .T. - I'm way too long using GUIDs as ID, which means charavter IDs.

Eerlier on I tried with Northwind - A combobox showing the customer, who made the order. CustomerID in that case is a char ID, the famous ALFKI etc. keys.
Now I copied these two tables to have a numeric id:

Select *, Recno() as intid from customers into cursor curCustomers nofilter
Select *, Int(Rand()*_tally)+1 as intcustomerid from orders into cursor curOrders readwrite

Then using intcustomerid as controlsource and intid as boundcolumn it still works with BoundTo = .F., but only because in this case intID is the same as recno and as ListIndex.
If adding an offset to the IDs by Recno()+100 and setting corresponding higher intcustomerids, BoundTo has to be set .T.

OK, you got me there. So the final truth is: it depends. On the type of ID.

Bye, Olaf.

 

[Dan Freeman]

Historical trivia:

The LACK of BoundTo (in VFP3) is why the CodeBook framework (and the Tastrade sample application) used character fields for primary keys. Both were designed to use integers until we discovered how comboboxes actually worked at the time.

BoundTo was added for a good reason.

 

[Mike Lewis]

The LACK of BoundTo (in VFP3) is why the CodeBook framework (and the Tastrade sample application) used character fields for primary keys. Both were designed to use integers until we discovered how comboboxes actually worked at the time.

Well, I'll be durned. I never knew that.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

Well, I'll be durned. I never knew that.

We discovered the behavior a week before shipping. It was a hectic week.

 

[stanlyn]

So, what is best practice for primary keys, char or integer?

I'm taking this to a new question...

Stanley

 

[stanlyn]

Ok, so how do we sort the combobox contents?

and, how do we apply a filter to the comboboxes? such as a combobox for looking up customers for a specific division?

I can get that list filtered and ordered if I use sql type instead of fields, but thats when things go haywire...

Thanks,
Stanley

 

[TamarGranor]

I pretty much never use the original table or cursor as the RowSource for a combo. I usually use an array.

My combo "base class" has an array property aItems and a method called GetItems. The Init and Requery methods both call GetItems:

This.GetItems()

Then, when I drop a combo from this class onto a form, I put the necessary code to populate the combo into the GetItems method. Generally, that's a query that fills the aItems array.

So, when I need to change what's in the combo based on some other control, I call the combo's Requery method, which refills the array and updates the combo.

Tamar

 

[Olaf Doschke]

And even if using a DBF we have indexes and SET ORDER. I typically use a cursor and of course you can query using a where clause and order by. SQL has all you need.

Bye, Olaf.

 

[stanlyn]

>> And even if using a DBF we have indexes and SET ORDER. I typically use a cursor and of course you can query using a where clause and order by. SQL has all you need.

And then I lose the other needed functionality...

I am experiencing now all the issues what I've had over the years with comboboxes. Go back to the top of this thread and re-read... The only spec I forgot to mention is the ordering and filtering or the lookup. The Displayvalue must always show the value of the field we are in, whether or not its valid (there is no ZZZ paytype in the PayTypes table). So, if the record's PayType field has "ZZZ" in it, it should show as the DisplayValue because the controlsource is set to that field and it is in that field.

I can get the ordering and filtering of the lookup table with sql and type=3. The lookup works perfectly, but then the dispalyvalue goes blank. I found an extensive article Marcia done on this (

http://www.tek-tips.com/viewthread.cfm?qid=1059851

) , and it had issue too. I've read all the help that VFP provides for comboboxes as well as the related commands and functions. I spent surely 8-9 hours on trying to get this seamingly simple request to work.

So, I'm back to my old way of doing it with a few small changes... I use a combo and textbox, where the combo is responsible for doing a lookup of available values that can be selected. I've resized the combo down to where the textbox is gone, only the drop down arrow exists. Also changed some its coloring. Now the textbox is responsible for containing the table's field value, so when you skip about the table the correct values are always shown. There is no connection between the 2 controls, other than when a selection is made using the combo, the selected value is copied to the field with a "rename table.field with the combo's value. The only thing I'm missing is when clicking the combo's down arrow and I'm shown the selection list, it always start at the top, where it would be better if it used the textbox's value and increm search to it.

I'm still open for suggestions and may attempt the customized class the Tamar and Marcia has done... I followed all the directions mentioned in this thread with no real solution as the base class seems to be lacking...

Thanks, Stanley

 

[Olaf Doschke]

No, the index order does only scramble listindex, but with the right settings, which I even learned about in this thread, the controlsource is bound to the primary key field and so a foreign key X always let's the combo display the corresponding record, no matter how data is sorted.

Anyway, with SQL-SELECT you can create a cursor in the wanted order and add a empty record for no selection. ID 0 if you want. That's always the way out. In the end you are modifying the foreign key in the controlsource. Whether the rowsource comes from the original table or a combobox cursor you prepare for the selection doesn't matter. Or use an array. I don't see it's working much easier.

Bye, Olaf.

 

[Olaf Doschke]

I just ensured and checked my combobox sample form using northwind (char keys) and the cursors I created (int keys) and both comboboxes continue working as wanted, when sorting the table or cursor with SET ORDER.

Bye, Olaf.

 

[Olaf Doschke]

>The Displayvalue must always show the value of the field we are in, whether or not its valid (there is no ZZZ paytype in the PayTypes table). So, if the record's PayType field has "ZZZ" in it, it should show as the DisplayValue because the controlsource is set to that field and it is in that field.

If you have a wrong foreign key, your relation is wrong. And no, what is displayed is determined by rowsource, not controlsource. Controlsource navigates to a record in rowsource. And if none is found your data is wrong. You don't copy over a paytype, you copy the paytypeid, and the paytype name is looked up.

If you need some additional paytypes, then you add it to the paytype list, not in invoices. The paytypes are not only a suggestion and any other values are allowed. No other paytypes are possible in any other table referring to paytypes. If you want to be able to limit the selectable paytypes without breaking references of old invoices, then add an additional field "selectable", only set .T. for active paytypes, and filter on that for new invoices, but not for old invoices.

If you want this kind of combobox just helping with suggestions and want a free text field, then you don't want a foreign key, you want a combobox in combo mode or an autocomplete textbox merely suggesting paytypes. But then how do you process payment for unknown paytypes? Your code may handle some, bit not any fantasy paytype or ZZZ. You can only enforce a known and programmed paytype processing, if you only allow a hard choice.

Bye, Olaf.

 

[stanlyn]

Why are we talking about pks and fks here? The invoice's pay_type field has nothing to do with the PayTypes table other than the PayTypes table is providing a lookup so it can be selected. Once a selection is made the selected value changes the controlsource field's value. If for some reason the invoice's paytype field has a value in it that is not in the paytypes table, will your solution allow that value to show in the displavalue of the combo? I have the controlsource set to invoice.paytype which contains something like cash, visa, m/c, etc... So how does this pk-fk make sense here?

Thanks, Stanley