On Mon, 01 Feb 2010 11:59:11 -0800,
"Girvin R. Herr" <girvin.h...@sbcglobal.net> wrote:
[...]
> 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
Thanks Girvin! I ended up using Drew's suggestion earlier in the
thread, since I don't have a function like concat(), but the || operator
in PostgreSQL. To deal with NULLS (which Drew eagle-eyedly suggested),
I used:
SELECT coalesce(Col1, '(none)') || '|' || coalesce(Col2, '(none)') AS
"DisplayText", "fkey" FROM referenced_table
binding field 1 (the "fkey") to the field in the form. It gives a bit
of table-like formatting with the "|" character. It lets me choose the
correct foreign key (a meaningless serial).
Cheers,
--
Seb
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@dba.openoffice.org
For additional commands, e-mail: users-h...@dba.openoffice.org
