List and Combo boxes

users
#1

To populate a list or combo box, I have been using single field tables. Seems like it should be possible to use a single table for more than one list or combo box. (The SQL statement for selecting a field for a list or combo box is: SELECT "field name" FROM "table name".) Has anyone tried this before? Any problems doing this?

--Dan

#2

Am 12.06.2012 21:08, Dan wrote:

To populate a list or combo box, I have been using single field tables.
Seems like it should be possible to use a single table for more than one
list or combo box. (The SQL statement for selecting a field for a list
or combo box is: SELECT "field name" FROM "table name".) Has anyone
tried this before? Any problems doing this?

--Dan

In a combo box you "always" want:
SELECT DISTINCT "Field" FROM "Table" ORDER BY "Field"

In a list box you "always" want:
SELECT "Field or Concatenation", "Primary Key"
FROM "Table"
ORDER BY "Field or Concatenation"
Bound field=1
Data field=The form's foreign key

[Example] Relations reflected by list boxes in forms

#3

Hi Andreas,

In a combo box you "always" want:
SELECT DISTINCT "Field" FROM "Table" ORDER BY "Field"

In a list box you "always" want:
SELECT "Field or Concatenation", "Primary Key"
FROM "Table"
ORDER BY "Field or Concatenation"
Bound field=1

I seem to recall having seen somewhere on the OOo dev lists that it
should now be possible to directly select Bound field=0, and still have
your sorted distinct data on the other data field, or have I muddled
things up ? This was part of the development that went into OOo3.4, but
perhaps it didn't make it into LO ?

Alex

#4

Am 13.06.2012 09:56, Alexander Thurgood wrote:

I seem to recall having seen somewhere on the OOo dev lists that it
should now be possible to directly select Bound field=0, and still have
your sorted distinct data on the other data field, or have I muddled
things up ? This was part of the development that went into OOo3.4, but
perhaps it didn't make it into LO ?

Alex

A two minutes test reveals that LibO 3.6(beta) can not use the first field (field #0) as bound field.
I "always" sort list box entries by the visible field so I've got to write a quick query anyway. If I write <SELECT "Field1", "Field2"> or the other way round does not matter at all.
My receipt "always" works. It gives a pointer to another table's primary key which can be selected by typing and picking.
Contrary to a most simple SQL receipt, the list box wizard is a totally unproductive piece of crap. It can not even concatenate fields.

#5

Hi Andreas,

A two minutes test reveals that LibO 3.6(beta) can not use the first
field (field #0) as bound field.

Thanks for testing, I guess those changes were in cwsdb34 which didn't
make it into LO (for various reasons) :-/

I "always" sort list box entries by the visible field so I've got to
write a quick query anyway. If I write <SELECT "Field1", "Field2"> or
the other way round does not matter at all.

Agreed.

My receipt "always" works. It gives a pointer to another table's primary
key which can be selected by typing and picking.

Agreed.

Contrary to a most simple SQL receipt, the list box wizard is a totally
unproductive piece of crap. It can not even concatenate fields.

Yes, unfortunately, but it is not the only problem with concatenation
and the built-in SQL parser.

Alex

#6

Andreas Säger wrote:

Am 12.06.2012 21:08, Dan wrote:

To populate a list or combo box, I have been using single field tables.
Seems like it should be possible to use a single table for more than one
list or combo box. (The SQL statement for selecting a field for a list
or combo box is: SELECT "field name" FROM "table name".) Has anyone
tried this before? Any problems doing this?

--Dan

In a combo box you "always" want:
SELECT DISTINCT "Field" FROM "Table" ORDER BY "Field"

In a list box you "always" want:
SELECT "Field or Concatenation", "Primary Key"
FROM "Table"
ORDER BY "Field or Concatenation"
Bound field=1
Data field=The form's foreign key

[Example] Relations reflected by list boxes in forms

http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=40444

      OK, I see I was not explicit enough. The tables I use for list boxes always contain only ONE field using VCHAR as the field type. There are no repetitions in this field, so all values entered are DISTINCT.
      The benefits I get from this are:

1) If I enter a new value in the field, close the table, and open it again; the entries have rearranged themselves alphabetically.

2) The entries always appear in alphabetically order in the list box after clicking the refresh tool in the form.

3) Field A has been replaced with a list box (i.e., Monday, Tuesday, Wednesday) in a form. (The table for this contains one field whose values are Monday, Tuesday, and Wednesday.) When field A appears in a report, the only values showing are Monday, Tuesday, and Wednesday.
       If I were to use a table having two fields: ID, Day (i.e., 1,2,3 for ID & Monday, Tuesday, Wednesday for Day); the form has shown the Monday, Tuesday, Wednesday values while the reports show 1, 2, 3. Obviously, more has to be included in the SQL than what I wrote in the beginning. But with the limiting structure of my tables for list boxes do not require anything more.

--Dan

#7

Dan wrote:

#8

Hi Dan,

     To populate a list or combo box, I have been using single field
tables. Seems like it should be possible to use a single table for more
than one list or combo box. (The SQL statement for selecting a field for

I'm not sure I understand you correctly :

- do you want Table1.field1 and Table1.field2 to populate respectively
two separate list/combo boxes in your form ?

- or Table1.field1 to populate two separate list/combo boxes,
potentially displaying 2 different values (one for each combo/list box)
from the possible list of entries ?

Alex

#9

Alexander Thurgood wrote:

Hi Dan,

      To populate a list or combo box, I have been using single field
tables. Seems like it should be possible to use a single table for more
than one list or combo box. (The SQL statement for selecting a field for

I'm not sure I understand you correctly :

- do you want Table1.field1 and Table1.field2 to populate respectively
two separate list/combo boxes in your form ?

- or Table1.field1 to populate two separate list/combo boxes,
potentially displaying 2 different values (one for each combo/list box)
from the possible list of entries ?

Alex

     Your right, I was talking about two different tables and was not clear at all.
      What I do now: for list boxes I create single field tables (the field is the primary key and uses VARCHAR as the field type. I have a database I use for my budget that contains three similar tables for three list boxes. (Budget level 1, Budget level 2, and Budget level 3)
      What I was thinking was to create one table (call it Budget) for the three list boxes containing three data fields: Budget 1, Budget 2, and Budget 3. With this I would create a fourth field (ID) as the primary key with AutoValue set to Yes. Budget 1 is for Budget level 1, etc.
      Probably, the SQL mentioned by Andreas may be all that is necessary with this structure. If so, would this also mean that values shown in the list Box for Budget level 1 will also appear in reports containing Budget level 1. (In the past in a form I have had Budget level 1 values coming from Budget 1 of the Budget table while in a report, the Budget level 1 values came from ID. I was probably not using SQL for this then.)
      Is there a particular order for the fields of the Budget table? Perhaps ID should be the first field followed by Budget 1, Budget 2, Budget 3?

--Dan

#10

The order of row and columns in a database has no meaning. You can get any order of rows and columns as needed.
If the visible field is the same as the bound one use the same field for both columns:
SELECT "Field", "Field" FROM "Table"