[libreoffice-users] Re: List and Combo boxes
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" -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: List and Combo boxes
Alexander Thurgood wrote: Le 12/06/12 21:08, Dan a écrit : 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: List and Combo boxes
Le 12/06/12 21:08, Dan a écrit : 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: List and Combo boxes
Dan wrote: Dan wrote: 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 Perhaps an important point: The field used in the table for the list box is also the primary key. This guarantees that the field contains DISTINCT values. --Dan -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: List and Combo boxes
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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: List and Combo boxes
Le 13/06/12 13:50, Andreas Säger a écrit : 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 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: List and Combo boxes
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 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. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: List and Combo boxes
Le 12/06/12 21:37, Andreas Säger a écrit : 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: List and Combo boxes
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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
