How does List content's SQL know which field to use?

[dave2007]

In a list box (in the main form) I have the following:

Code: Select all

    Data field.......             subtable_fkey
    Type of list contents......   Sql [Native]
    List content......            SELECT "field1" AS "field1", "ID" AS "ID" FROM "subtable" ORDER BY "field1"
    Bound field.......            1

It works the way I want it to work (i.e. list box allows to select a record by "field1" from "subtable" but updates subtable_fkey in the mainform's table).

But now I wonder: How does it do that? How does it know to associate subtable_fkey with "ID" AS "ID" and not with "field1" AS "field1"?

The main motivation for my question is to basically understand how I can modify the SQL statement without breaking this amazing capability. The above SQL statement was copied from a tutorial without me really understanding how the fields are associated. For example, is it always the last field referred to in the "List content" SQL statement that gets matched with "Data field"?

Thanks,
Dave

[ufalke]

Hi, I am not an expert, but from my experience: it is always the second field stated in the Sql string which goes into the control's data field. If you wnat something like to display n fields in the control and have the n^th one in the data field, you need to combine the (1 ... {n-1}) fields in a CONCAT statement, which is counted as one as a whole at that point, leaving the intended n^th field as the second one for the logic of that control, i.e. the one to go into the control's data field.

@ the profs: correct me if I am wrong

Uwe

[dave2007]

Wow! ulfake, you are surely a greater expert than I am. This is the first time I hear about CONCAT. I guess I have to learn a lot before being able to do something useful with SQL.

Back to the original question, let me clarify my question with an example. In the following SQL query, which field will be associated with the control's Data field?

SELECT "field1" AS "field1", "field2" AS "field2", "ID" AS "ID" FROM "subtable" ORDER BY "field1"

Thanks,
Dave

[ufalke]

In the following SQL query, which field will be associated with the control's Data field?

SELECT "field1" AS "field1", "field2" AS "field2", "ID" AS "ID" FROM "subtable" ORDER BY "field1"

Sorry, I missed a bit (as I said, I am a novice too, but had a similar prob to yours just two weeks earlier : The field to go into the Ctrl's data field can obviously be determined by the parameter "bound field" in the data properties. Not sure why, but a value of 1 relates to the second field (field2 in your example), a value of 2 to the third one (ID in your example), but a value of 0 seems to not relate to the first one. However, the listbox shows only the field1 values. if you use

Code: Select all

SELECT CONCAT(field1,' ',field2), ID  FROM "subtable" ORDER BY "field1"

together with "bound field = 1" you might get what you want.

BTW: where does that "AS" come from, what does it do? No SQL documentation I looked at has been stating that clause.

Uwe

[JDHeinzmann]

Gentlemen,

I am no expert on this either, but I have successfully been using the ListBox to achieve this wonderful functionality. I thought I understood it but as I tried to put it in writing, I realized that I don't understand it. So I snooped around a little and found this thread in the OOo Base Developer's Forum: Another Listbox Question. On the previous page of that thread, Drew also said:

A list box always displays the first column from its datasource and writes the second column.

So, as I understand it, the first column of the resultset is used to populate the drop-down list and the second column of the resultset gets written into the field on the form specified by "Data field" when the item in the drop down is selected. And that leaves me wondering what the number in "Bound field" is for. I thought it was for specifying which column of the resultset would populate the drop-down list, but Drew's statements and an experiment I just ran show that not to be true.

Hopefully, someone will clarify this for us.

[JDHeinzmann]

Oh, and AS is used to create an alias (another name) for the column returned in the resultset. In your example the ASs seem ridiculous because they give each column its same name. I think you can mostly leave them out, but there are some cases which I seem to recall that Drew stated they were required.

Time to go to bed.

JD

[ufalke]

Gentlemen,
...
On the previous page of that thread, Drew also said:

A list box always displays the first column from its datasource and writes the second column.

So, as I understand it, the first column of the resultset is used to populate the drop-down list and the second column of the resultset gets written into the field on the form specified by "Data field" when the item in the drop down is selected. And that leaves me wondering what the number in "Bound field" is for. I thought it was for specifying which column of the resultset would populate the drop-down list, but Drew's statements and an experiment I just ran show that not to be true.

Hopefully, someone will clarify this for us.

Compiling my above response, I was checking and I found that the bound field value does indeed determine the field which goes into the data field of the control, just as I have written there (I confirm hereby that I had tested that
So, it is always the first field which is displayed in the listbox, and it is the one determined by the "bound field" value which goes into the data field (but seemingly that can't be the first one used for the listbox display).

[ufalke]

Hi,
reading Drew's statement referred to by JDHeinzmann I think I see a likely source of confusion:

...
So I snooped around a little and found this thread in the OOo Base Developer's Forum: Another Listbox Question. On the previous page of that thread, Drew also said:

A list box always displays the first column from its datasource and writes the second column.

...

This must IMHO be understood with respect to a temporary table which is just built up to represent the listbox, but not to the fields given in the SQL statement. Only then, Drew's statement does coincide with my observations.
For clarity: when using a statement like

Code: Select all

SELECT A, B, C, D FROM SOMETABLE;

together with "bound field =2", a temporary table is created with A in the first column (always) and C in the second one (determined by the "bound field"), and, of this table, the first column is displayed (i.e. populates the listbox) and the second goes into the control's data field.

Uwe

[viordiasko]

Gentlemen,

Sorry if this is redundant but I did find the Openoffice Basic Help to be quite informative:

Code: Select all

Bound field

If you delete the contents of the Bound field cell in the property browser, the first field of the result set is used to display and to exchange data.

This property for list boxes defines which data field of a linked table is displayed in the form.
If a list box in the form is to display contents of a table linked to the form table, then define in the Type of list contents field if the display is determined by an SQL command or the (linked) table is accessed. With the Bound field property, you use an index to specify to which data field of the query or of the table the list field is linked. 

The property Bound field is only for forms that are used to access more than one table. If the form is based on only one table, the field to be displayed in the form is specified directly under Data field. However, if you want the list box to display data from a table that is linked to the current table over a common data field, the linked data field is defined by the property Bound field.

If you selected "SQL" under Type of list contents, the SQL command determines the index to be specified. Example: If you specify an SQL command such as "SELECT Field1, Field2 FROM tablename" under List content, refer to the following table:
Bound field:Link
{empty}:The database field "Field1" is linked to the field specified under Data field.
1:The database field "Field2" is linked to the field specified under Data field.

If you selected "Table" under Type of list contents, the table structure defines the index to be specified. Example: If a database table is selected under List content, refer to the following table:
Bound field: Link
{empty}: The 1st column of the table is linked to the field specified under Data field.
1:The 2nd column of the table is linked to the field specified under Data field.
2:The 3rd column of the table is linked to the field specified under Data field.

[JDHeinzmann]

Thank you, viordiasko. Indeed, if one searchs for "bound field" in OOo Help, it is all laid out. (I thought I had already looked there!)

JD