Steve R. wrote:My initial problem was that I had believed that the bound column was based on the underlying table which it wasn't. Turned out that the bound column refers to the "pseudo-column" in your SQL statement. Not only that but it is offset by -1, meaning that "pseudo-column" #3 is really bound column #2. See the attached screen shots. As always, since I am new to BASE, if there is any further advice/clarification, please provide.
Breaking it down, a List Box has a
Display field, a
Bound (source/primary key) field, and a
Data (target/foreign key) field.
The real power behind a List Box in Base is that it allows us to transfer selected (
Bound field) data from a
source Table/Query to a
target Table (
Data Field) using a Form. The
source Table/Query is known as the 'List source' of the List Box. The
target Table is determined by the associated Form. This design can be used in support of "normalized" databases whereby the 'primary key' of the
source Table/Query is assigned as the
Bound field, which upon user-selection is stored to the
Data field of the
target Form Table as a 'foreign key'.
Since working with 'key' fields is not user-friendly, the List Box employs a
Display field as well. The
Display field is used to populate the List Box allowing us to differentiate among source Table/Query records.
Then it just comes down to the designed mechanization of a Base List Box:
- The List Box Bound field is not available in all cases. The Bound field option requires a multi-column 'Type of list contents' including Table, Query, SQL, or SQL (native).
- A List Box always uses the first column of the 'List content' as the Display field. Call it what you will (perhaps column "0") but it's really just the Display field.
- Now if the List Box has a Bound field option, the available Bound fields start with the second column so it becomes option #1 and so on. Obviously, this is confusing because we don't inherently know about the mechanics of the design. As an aside, I think the devs should simply display the available Field/Column names instead of this rather confusing number scheme. But-it-is what-it-is for now.
Edit: In recent versions of *Office, the Bound field may be set to the Display field -- which in fact is column "0". This allows us to save the Display field to the target table, without creating a redundant, two-column query/SQL as the source List Content. This practice effectively turns a List Box into a Combo Box, but without the free-text feature. Either way, we typically avoid saving the Display field text to the target table, except when working with a flat-file table/database. So a Bound field set to "0" does have it's utility, but this would be a rare practice when working with a properly 'normalized' relational database. |
- The ability to select a Bound field among multiple columns (even using the lame numbering scheme) becomes important when you use a Table or existing Query as the 'List content' due to the sheer number of columns and their relatively fixed order. So the Display field is always the first column, but the Bound field is any column starting with the second column (column '1') as selected by the Form designer.
- A dedicated source Query as the 'List content' becomes quite powerful when you consider the power of SQL such as CONCATENATION of multiple fields and ORDER BY clauses, etc. as shown in the example below:
So the devs gave us a cool and flexible design under-the-hood, but then failed to follow-up with something as simple as the column-name in the Bound field spinner. And I find the List Box Wizard to be even more confusing as it seems to use off-the-wall terminology and no graphics for clarity. On the bright-side, the numerical Bound field avoids issues otherwise associated with changing column names.
BTW, your thread title should probably read "Listbox..." because a Base "Combo Box" doesn't have a Bound field function.