Combo box, how to get more than one field to appear in the list content?

[docbda]

I am attempting to set up a combo box so that data from multiple fields (LastName, FirstName, DOB, id) will appear in the list contents of a combo box. Despite hours of research, I can only get one field to appear in the list contents. The table that my form is based on and the table containing fields that I would like to appear in the list contents of the combo box are linked properly. I have tried both Base assisted query design, and native mySQL queries in the “type of list contents” and “list content” options under the “data” tab of the combo box properties without success. In both instances the queries run without error and give the appropriate results, but only the field in the first column of a Base assisted query or the first field listed under a SELECT command in a native mySQL query will populate the list contents.

In the Base documentation, it does state that it is impossible to have more than one field listed in the list contents of a combo box if the combo box wizard is used. The documentation does imply that it is possible if the wizard is not used, but it does not give an example of how this is done. I have turned off the wizard and filled in the properties by hand and obtained a functional combo box, but not one with more than one field in the list contents. There is an example in the LO Base tutorial on page 108 that shows multiple fields in a list box list selection. I have tried to duplicate the syntax of the mySQL commands listed under “bound field” in the list box properties shown in the example, but with no luck.

Any help would be appreciated.

Thanks,

[peterwt]

You can do this with a List Box. Normally using SQL as the List Box source the SQL has two fields - the first the information shown in the List Box and the second the bound field - the default setting of the List Box is the bound field is the second field.

In your case you could put -

SELECT "LastName" , "ID" FROM "Tablename"

this would show the LastName in the List Box with the bound field beinf ID.

You can however concatenate a number of fields into a single field for the List Box display -

SELECT "LastName" || ' - ' || "FirstName", "ID" FROM "Tablename"

This would show in the List Box -
Smith - John.

You can put as many fields as you need.

If the DOB is a date field you may need to convert it to Text in the SQL statement.

[EasyTrieve]

Good, but this is not really two fields, it is one field, with info from other fields in it. I’m coming from MS Access where you can really have two or more fields showing in your pull down.

[docbda]

Thanks peterwt,

I was finally able to get the combo box to do what I wanted. The good news is that the answer you gave works for a combo box as well as a list box. It’s actually easier with the combo box because you don’t have to mess with that pesky “bound field” selection found in the list box properties.

There were a couple of modifications from your example, but it was close enough for me to figure out what I needed to make things work.

One important item that I failed to tell you is that I was using Base linked to a mySQL database via JDBC. Forgive me if you know this already, but there is a quirk in translating SQL to mySQL syntax regarding concatination. SQL uses “||” for concat, but mySQL uses the function CONCAT(). It took me a while to figure out why the syntax you gave me wasn’t working. Also, instead of using “sql” under the “type of list contents” option under the data tab of the combo box properties, I used “sql [native]”. For those who don’t know the difference between these two options, the “sql” option is linked to the Base assisted query editor, where the “sql [native]” is linked to a SQL editor that allows you to type in the SQL (or in my case mySQL) commands directly.

Another important item is that under the form properties (for the active form you are working in) again under the “data” tab, the “analyze SQL command” option must be set to “yes” so that the SQL/mySQL commands are parsed properly. Otherwise, you end up with a blank combo box.

I hope this explanation helps some folks out. I think this is kind of an important item to be so poorly covered in the Base documentation. But hey, what do you expect for free? LO still beats the hell out of Microsoft.

Thanks again, peterwt

docbda

[VHM]

Hello,
I am trying to use this code. I am at MAC OS. When I update the values of the form, this message appears: Error updating the current record. java.lang.NumberFormatException.

I don’t know how to fix it if anybody could help I appreciate.

Thank you,

[Ratslinger]

Please ask as a new question and add details. It appears you form is incorrect. You may also want to refer to this post.