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
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
