[Solved] List Box - Display Multiple Columns

Discuss the database features
Post Reply
User avatar
Steve R.
Posts: 163
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

[Solved] List Box - Display Multiple Columns

Post by Steve R. »

In converting my magazine database from MS Access to BASE, an outstanding issue that i encountered was how to combine a last name and a first name in a control and display the combined name while storing the primary key as a BIGINT. After some searching I found this post which proved useful. List box displaying multiple columns

My initial problem was that I had believed that the bound column was based on the underlying table which it wasn't. Turned out that the bound column refers to the "pseudo-column" in your SQL statement. Not only that but it is offset by -1, meaning that "pseudo-column" #3 is really bound column #2. See the attached screen shots. As always, since I am new to BASE, if there is any further advice/clarification, please provide.
Data Entry Form, First and Last Name Fields Combined
Data Entry Form, First and Last Name Fields Combined
Screen shot of the SQL Matrix.  Note that the ArtistIDnum is in Column #3 in the SQL Matrix
Screen shot of the SQL Matrix. Note that the ArtistIDnum is in Column #3 in the SQL Matrix
Attachments
Note that the Bound Field is identified as Column #2, even though it is Column #3
Note that the Bound Field is identified as Column #2, even though it is Column #3
Last edited by Steve R. on Thu Nov 18, 2010 5:57 pm, edited 2 times in total.
Ubuntu 16.04 and Windows 10
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Combo Box - Display Multiple Columns

Post by Hagar Delest »

Why do you tag your question as solved if you still have a question??? I've removed the tag else, you may not receive many answers. Feel free to add it back afterward.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
Steve R.
Posts: 163
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

Re: Combo Box - Display Multiple Columns

Post by Steve R. »

It is "solved" from the context that I now know how to display multiple columns. In researching this "how to", I did not find a clear explanation, so I decided to post for the benefit of everyone. Since, I am at the bottom of the learning curve and looking up a steep slope, I may not have a full comprehension of how all this goes to together - so I was also looking for feedback from those who know more.
Ubuntu 16.04 and Windows 10
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: List Box - Display Multiple Columns

Post by DACM »

Steve R. wrote:My initial problem was that I had believed that the bound column was based on the underlying table which it wasn't. Turned out that the bound column refers to the "pseudo-column" in your SQL statement. Not only that but it is offset by -1, meaning that "pseudo-column" #3 is really bound column #2. See the attached screen shots. As always, since I am new to BASE, if there is any further advice/clarification, please provide.
Breaking it down, a List Box has a Display field, a Bound (source/primary key) field, and a Data (target/foreign key) field.

The real power behind a List Box in Base is that it allows us to transfer selected (Bound field) data from a source Table/Query to a target Table (Data Field) using a Form. The source Table/Query is known as the 'List source' of the List Box. The target Table is determined by the associated Form. This design can be used in support of "normalized" databases whereby the 'primary key' of the source Table/Query is assigned as the Bound field, which upon user-selection is stored to the Data field of the target Form Table as a 'foreign key'.

Since working with 'key' fields is not user-friendly, the List Box employs a Display field as well. The Display field is used to populate the List Box allowing us to differentiate among source Table/Query records.

Then it just comes down to the designed mechanization of a Base List Box:
  • The List Box Bound field is not available in all cases. The Bound field option requires a multi-column 'Type of list contents' including Table, Query, SQL, or SQL (native).
  • A List Box always uses the first column of the 'List content' as the Display field. Call it what you will (perhaps column "0") but it's really just the Display field.
  • Now if the List Box has a Bound field option, the available Bound fields start with the second column so it becomes option #1 and so on. Obviously, this is confusing because we don't inherently know about the mechanics of the design. As an aside, I think the devs should simply display the available Field/Column names instead of this rather confusing number scheme. But-it-is what-it-is for now.
     Edit: In recent versions of *Office, the Bound field may be set to the Display field -- which in fact is column "0". This allows us to save the Display field to the target table, without creating a redundant, two-column query/SQL as the source List Content. This practice effectively turns a List Box into a Combo Box, but without the free-text feature. Either way, we typically avoid saving the Display field text to the target table, except when working with a flat-file table/database. So a Bound field set to "0" does have it's utility, but this would be a rare practice when working with a properly 'normalized' relational database. 
  • The ability to select a Bound field among multiple columns (even using the lame numbering scheme) becomes important when you use a Table or existing Query as the 'List content' due to the sheer number of columns and their relatively fixed order. So the Display field is always the first column, but the Bound field is any column starting with the second column (column '1') as selected by the Form designer.
  • A dedicated source Query as the 'List content' becomes quite powerful when you consider the power of SQL such as CONCATENATION of multiple fields and ORDER BY clauses, etc. as shown in the example below:
Boundfield.jpg
BoundfieldSQL.jpg
So the devs gave us a cool and flexible design under-the-hood, but then failed to follow-up with something as simple as the column-name in the Bound field spinner. And I find the List Box Wizard to be even more confusing as it seems to use off-the-wall terminology and no graphics for clarity. On the bright-side, the numerical Bound field avoids issues otherwise associated with changing column names.

BTW, your thread title should probably read "Listbox..." because a Base "Combo Box" doesn't have a Bound field function.
Last edited by DACM on Tue Oct 08, 2013 2:46 pm, edited 7 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Steve R.
Posts: 163
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

Re: Combo Box - Display Multiple Columns

Post by Steve R. »

DACM wrote:BTW, your thread title should probably read "Listbox..." because a Base "Combo Box" doesn't have a Bound field function. (emphasis added)
Changed. I ran across this issue last night and finally realized that. In MS Access, both the List Box and Combo Box have the bound field property. I was contemplating an additional post reviewing this distinction, but then I re-read what you wrote again. Thanks for pointing that out. This should be a helpful tidbit for others searching the forum.
Ubuntu 16.04 and Windows 10
User avatar
jenny143
Posts: 82
Joined: Wed Oct 19, 2011 1:40 am

Re: [Solved] List Box - Display Multiple Columns

Post by jenny143 »

good Morning
Nice thread.... :super:

what if i used different tables for this?

example ItemSize from TblItem and UnitName TblUnit???
Windows XP and Open Office 3.3
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] List Box - Display Multiple Columns

Post by RPG »

Hello

I think it is better to asked your question in a new thread. Give also more information about your problem. This is an already solved thread and only for that reason not a good place to ask a new question.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Vedat
Posts: 1
Joined: Tue Jul 02, 2013 9:07 pm

Re: [Solved] List Box - Display Multiple Columns

Post by Vedat »

Sorry for warming this up, but this was exactly what I needed. There is a piece of Information I want to share, which fits IMHO perfectly in this thread.

To access the Data field of the Listbox through OOo Basic, you could simply run following Macro code:

Code: Select all

Sub ListboxKundeSelect
	Dim MyForm As Object
	Dim MyBox as Object 

	MyForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
	MyBox = MyForm.GetByName ("MyListboxName")
	MsgBox  MyBox.ValueItemList(MyBox.SelectedItems(0)) & " - item from the 2nd column list"	
End Sub
LibreOffice 4.0.4.2 on Win 7
Post Reply