Re: list box displaying multiple columns

Seb wrote:
> Hi,
>
> I cannot find how to have a list box showing multiple columns from a
> table.  This is for a field in a form that is a foreign key (many side)
> of the table containing the columns to display.  I thought I could do
> this by doing "control properties -> data -> type of list contents
> [Sql]", and then under "list content" have a SQL statement such as:
>
> SELECT "col1", "col1", "col2", "col3"
>
> with "bound field" set to the default '1'.  How should this be done?
> Thanks.
>
> Cheers,
>
>   
Seb,
Ahh, Another Base user needing multiple columns in a drop-down list 
box!  I have been asking for this feature in Base since October, 2007.  
There were some threads back in July 2008 on this forum which dealt with 
my problem and although the solution the forum came up with is not 
elegant, it does give me the information I need to decide which item in 
my list box to chose.  You don't say which database engine (server) you 
are using, but this solution works with my MySQL 5.x.  The key is to 
write an SQL statement in Base for your listbox, and use the CONCAT_WS 
or CONCAT SQL function.  CONCAT_WS allows you to specify a field 
delimiter, which makes it easier to see the columns.  The in-elegancy I 
stated above is that the columns, as displayed in the Base listbox, are 
not aligned as columns, they are concatenated together and variable 
string lengths of data cause them to be messed up horizontally.  Here is 
an example of the CONCAT function I am currently using.  CONCAT_WS is 
similar but allows specifying the delimiter to be added between the columns.

SELECT concat(`Name`, `Address`, `SupplierID`) FROM 
`inventories`.`suppliers`

To use this in Base, select your listbox (green tags on the corners, 
etc.).  Right click in the selected listbox.  Select "Control" in the 
drop-down list.  In the "Properties: listbox" window, select the "Data" 
tab.  Select "sql" in the "Type of list contents" box.  Enter your 
CONCAT (or CONCAT_WS) statement in the "List content" box.  The "Bound 
field" box should contain the field index {0..n-1} of the field in your 
CONCAT function to link to.  My bound field is set to 2, which is the 
`SupplierID` (key) argument in the concat example above.  Note: I am 
still using OO.o 2.4, so your version may be slightly different.

Note also that the quotes are not the normal quotes of the " / ' key.  
They are the other single quote.  On my keyboard it is with the ~ 
(tilde) key.  I couldn't get this to work until someone in the forum 
mentioned to use the other quote.

Hope this helps.
Girvin