[Solved] Adding Listbox to Form - Where is the wizard?

[RhinoCan]

I'm new to Base but I've developed in various languages for many years so I'm not new to developing.

I'm trying to create a list box on a form in Base. After trying to puzzle the technique out on my own for a while, I tried using the Help and found the topic on Form Controls. That topic assures me that if I click on a list box over on the left and draw it on the form, the List Box Wizard will appear to help me construct the list box and populate it. When I created the list box though, no wizard began. This video I found on YouTube - https://www.youtube.com/watch?v=FDQYvc3VYVw - also assures me that the List Box wizard will appear when I use the technique it describes. The video is only a few months old and is presumably for the current version of Base, 4.1.2, which is what I'm using.

What am I doing wrong? How do I make the wizard appear?

Most programs give you multiple ways to do the same thing and Open Office doesn't seem to be an exception. I right-clicked on the list box that I created and looked at the tabs. I thought perhaps I could define my list box contents there but no dice. I have a simple lookup table and I wanted to define the list box as the contents of one of the columns of that two-column table. I chose a list type of SQL (native) and wrote: select agency_code from agency. I've been using SQL for over 30 years and I know that is valid SQL but the error message says "table not found in statement [select agency_code from agency]". I *think* this is saying that Base can't find a table named agency in Base, which is nonsense because that table is present and populated. Or is it saying it doesn't understand which part of this trivial SQL statement is the table name??

Just to see if it would work, I tried to change my list type to valuelist but when I tried to enter the five valid values, they all wound up on the same line in the list box, not as five separate values. I tried separating the values with carriage returns but as soon as I typed my first one, it closed the field so that I couldn't add more values; that was clearly NOT what it wants. I tried using commas between the values. I tried using double quotes around the values. None of those options accomplishes the desired effect of listing 5 discrete values, one on each line.

This is really getting exasperating. Looking at the tutorials in the forum was not helpful either. They all seem to be written in dodgy English that is hard to follow or refer to ancient versions.

I'm trying to be as self-sufficient as I can be but I'm not having a lot of luck. Can anyone help me? What I'm trying to do seems like it should be simple but I'm spending hours on what I thought would take minutes and losing my momentum in my little project....

[Villeroy]

Forget the wizards. All of them except for the report wizard. The wizards stay too far behind the possibilities. Sometimes they are misleading.

A 2-column list box with one visible column #0 and a bound column #1 goes like this:

Linked field: a foreign key of this form's record set where the value will be stored. Usually this is an integer pointing to another table's primary key.
Source type: SQL
Source: SELECT "visible field", "primary key" FROM "other table" ORDER BY "visible field" (the [...] button opens a query designer if you want)
Bound field: 1 (the second field)
This is how I set up 99% of my list boxes and they do work as expected out of the box. Sometimes the "visible field" may be a concatenation such as "Surname"||','||"Forename". Sometimes the visible field is the same as the bound field.

[RhinoCan]

I appreciate the speedy reply but I'm not following your answer.

You use the terms "visible" and "bound" and I'm not sure what either means in this context. I know there's a numeric spinner for the "bound field" in the Data properties for the list box. Are you saying that this spinner needs to point to the column that is providing the values for the list box? If so, is it a 0-based value or a 1-based value?

In my case, the form is for a table called Sales_Tracker. The third column in Sales_Tracker (counting the 1st column as 1, not 0) is Agency_Code. The values for that column should come from the first column of a table called Agency. Agency has two columns, Agency_Code and Agency_Name. Therefore, do I set that "bound field" to 0 or 1 to refer to the Agency_Code column of the Agency table? As far as primary keys goes, Sales_Trackers primary key is the Order_Number and in Agency, the primary key is Agency_Code.

Also, what's the difference between choosing "SQL" and "SQL Native"?

--
Before you start typing a long answer, I changed my "SQL [Native]" to "SQL" and now everything works the way I wanted. The list box populates with the Agency_Code values and each appears on a separate line in the list box. That's the only change I made so I apparently got everything else right. I'd still like to know the difference between "SQL" and "SQL Native" and what this "hidden" and "bound" stuff is all about. My "bound" value is 1 but your answer suggests that 1 is the *second* column of the table (Agency_Name) so I'm a little confused by why this works. I *think* it is because there is a 1-to-1 correspondence between the Agency_Code and the Agency_Name. (The Agency_Code is just an abbreviation of the Agency_Name so they are ALWAYS referring to the same thing.). (I don't mean to dwell on this 1-based vs. 0-based business but some languages/systems I work with use 0-based and some use 1-based. For instance, Java says that January is the 0th month of the year in its built-in methods while we normally think of January as the first month of the year.)

[Villeroy]

So you want to see a list of agency names ("visible") and write the corresponding agency code ("bound") into the linked field.

Linked field: Agency_Code (meaning the Agency_Code in the Sales_Tracker table)
Source type: SQL
Source:

Code: Select all

SELECT "Agency_Name","Agency_Code" FROM "Agency" ORDER BY "Agency_Name"

This works in "native" mode so you don't have to change that.
Bound field: 1

This is set up within a minute without the help of a lousy wizard and it works.
If you have many of the same agency list boxes you may switch to source type "Query" and specify the name of a query with the exact same SQL. This is 100% equivalent, saves some typing and you can modify the content of many boxes in one place.

[RhinoCan]

Okay, thanks very much Villeroy. The tip about using Query if I have multiple list boxes with the same content is especially helpful.