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).



To unsubscribe, e-mail: users-unsubscr...@dba.openoffice.org
For additional commands, e-mail: users-h...@dba.openoffice.org

Reply via email to