[Solved] How Do you Populate List Box

[merlot]

Hi,

I have looked at various tutorials and a struggling to populate the list box on a form. Progress as follows:

Two tables created - Visits with ID and about 8 fields one called Status. Second table called Status with ID and 4 fields. Relationship created between Status field on Visits table and ID on Status table. Form sucessfully created and list box added with wizard popping up. Status selected from first dialogue box which successfully lists the fields in the Status table, ID selected. The next box asks for "Field from the value table" Status selected and "Field from the list table" ID selected and finish. No drop down arrow and no list in the list box. I cannot see any other selections that I can make, I also tried ID and ID on the final box, but it made no difference. I am obviously doing something wrong so can anyone help?

Merlot

[Villeroy]

Forget all those bloody wizards. In most practical use cases they are counter productive.

List box properties, tab "Data":
Linked field: The foreign key (other table's primary key)
Source type: SQL
Source: SELECT "Visible Field", "ID" FROM "Status" ORDER BY "Visible Field"
Bound Field: 1 (0 is the visible field, 1 is the ID field to be written into the linked field).

Instead of your field named "Visible Field" you can also use concatenations like this:
SELECT "Field1" || ',' || "Field2" || ',' || "Field3" AS "Visible Field", "ID" FROM "Status" ORDER BY "Visible Field"

[merlot]

Many thanks for that. However I am a rank novice and I am afraid you have lost me.

Under the list box properties data tab I have the following choices:

Data Field: Visits table fields are listed and I have selected ID. (Could be Status but I think ID is the "other table's primary key")
Input required: "Yes" already selected.
Type of list contents: I have selected Sql
List content: Blank no choices and cannot follow your instructions on what to put in here.
Bound Field: 1 already selected.

I think we may be on different versions of Base or a different set up.

Merlot

[Villeroy]

List content: Blank no choices and cannot follow your instructions on what to put in here.

Then you've got to learn quite a bit. This is a tool for database developers. The resulting input form is aimed at end users.

Check out the [...] button right of the input box. Create a query with 2 fields, the visible text followed by the ID.

[merlot]

List content: Blank no choices and cannot follow your instructions on what to put in here.

Then you've got to learn quite a bit. This is a tool for database developers. The resulting input form is aimed at end users.

Check out the [...] button right of the input box. Create a query with 2 fields, the visible text followed by the ID.

Yes I already stated that I was a rank novice. You appear to be suggesting that I am wasting my time trying to create a database if I am not a database developer. I thought this forum was to assist beginners?

Can you offer any assistance on how to "Ceate a query with 2 fields, the visible text followed by the ID.[/quote]" When I use the [...] button I get a popup box with the option to "add table" or "add query" when I select add query and OK nothing happens.

Please let me know if I have joined the wrong forum for beginner assistance and I will seek help elsewhere.

Many thanks for your help to date.

Merlot

[Villeroy]

You appear to be suggesting that I am wasting my time trying to create a database if I am not a database developer.

To some extent -- yes. The abstraction level is high compared to mere document creation in Writer or Impress.
I think this forum is the best place to learn anything about Base. We have the largest group of Base experts and a collection of tutorials. But Base is just a rather primitive tool set for databases of various kinds. It can not help you creating well structured databases. The wizards lead you to nowhere. The type of database Base can create from scratch is good enough for educational purposes and demos but it should not be used for productive tasks (risk of data loss).
A database developer who connects his existing 3rd-party database to this office suite may find the additional queries, forms and reporting facilities very helpful and easy enough to use. Re-using existing data pools within an office suite is vital for any office suite that claims to be professional. This is an administrative task or even a developer's task.

But starting a new productive database from scratch with no other tool than OOo Base is not an option. This facility deserves to be removed from the suite together with all the wizards.

[merlot]

OK,

Thanks for your reply. I am trying to create a very simple data base for personal use and ease of manipulating and retrieving data. This is not for Lloyds Bank and any loss of data or major issues would not be a problem and I would revert to my spread sheets. Can you assist with the following query from my last post.

Can you offer any assistance on how to "Ceate a query with 2 fields, the visible text followed by the ID.[/quote]" When I use the [...] button I get a popup box with the option to "add table" or "add query" When I select add query and OK nothing happens.

If not, can anyone else look at my first post and provide simple, step by step instructions for a beginner to populate the list box on a form?

[Villeroy]

Copy this into the source box (or into some editor for easier editing):

Code: Select all

SELECT "Visible Field", "ID" 
FROM "Status" 
ORDER BY "Visible Field" ASC

Replace the double-quoted field names and the table name with your actual names. The first field has to be the name of the field to be displayed in the list box. This is also the field you want to appear in ascending order so the list box selects the right thing when you typing into the focussed box. The second field ("ID") should be the name of the corresponding row identifier (the primary key of the "Status" table that gets written into the data field).

This is a "one-to-many" relation (1-n). Each row in your form table has exactly one "Status" which is defined in the other table.
[Example] Relations reflected by list boxes in forms [notice the comment by ubiquity » 27 Aug 2012]

The "one" side is field with unique row numbers used to reference one distinct item (the primary key in your "Status" table, "ID" in my select statement).
The "many" side is a field in some other table that uses the items' row numbers as a pointer to one distinct item (the foreign key in your "Visits" table, the list boxes "data field" which takes one of the "Status" table's row numbers).
The user picks a status by name from the list box and a valid status-ID gets written into the visits table. Any information about the status can be referenced by the unique ID number.

[merlot]

Apologies, but I cannot follow you.

What source box?

As already stated, if I add a list box and cancel the wizard, as you suggested, then double click on the form to go to Listbox Properties/Data tab, I have five entries to make:

Data Field:
Input required:
Type of list contents:
List content:
Bound Field:

Also when I now open the properties window the [...] button has disappeared and the Bound Field is greyed out.

[F3K Total]

Hello,
it should look like this if "AS" is a column of table Status to be displayed in the listbox to select an item:

listbox.png (20.01 KiB) Viewed 28291 times

R

[merlot]

Many thanks for that, very clear.

I still cannot get it to work. I used one of the status conditions/columns in the Status table "Booked" and it did not work still a blank with no drop down. I then added the AS column into the Status table and typed is exactly as yours and still the same result a blank list box.

[F3K Total]

Hello,
find attached an example to study. Use Form Navigator in Edit-Mode to understand the forms structure.

R

[F3K Total]

I still cannot get it to work...

Is it possible, that you don't have any row in Visit table?
You first have to have at least one row, otherwise the listbox has no target to write to.
Have also a look at the data types of ID in Status and Status in Visits, shall be both Integer.
R

[merlot]

I think progress is being made, at least I have a drop down arrow now! Box is still empty. I have looked at your excellent attachment and think I have followed the logic.

Don't know how to insert image but a direct copy of the list contents box is: SELECT "To Book", "ID" FROM "Status"

To Book is the first field, after ID, in the Status table. I did not have an entry in the Visits table but I have one now. The data types of ID in Status and Status in Visits, are both integer. The relationship shows up as linked when I select relationships. The list box now has the drop down arrow and I have set the default backkground colour to light grey, but it turns bright blue when you click in the box out of design mode. Don't know if this is a clue.

[F3K Total]

Hello merlot,
you have the possibility to upload an example of you problem db here.
R

[merlot]

No problem.

CCS.odb

(13.96 KiB) Downloaded 493 times

[DACM]

Is this what you had in mind?

If so, then your List Box was setup perfectly...

...but the underlying Form was set to 'Add Data Only'
...and the "Status" table was setup with multiple fields whereby the field names contained the desired 'display' text , rather than a simple table with only two fields (ID and Status) with multiple records/rows containing the desired 'display' text.

[merlot]

Yes, that is exactly what I was trying to do! A million thanks for your assistance and to the other contibutors.

...but the underlying Form was set to 'Add Data Only'

For future reference, I take it this should be set to "Display all Data". I can see the option when setting up the form with the wizard, but how do you access that option after the form is created?

and the "Status" table was setup with multiple fields whereby the field names contained the desired 'display' text , rather than a simple table with only two fields (ID and Status) with multiple records/rows containing the desired 'display' text

Yes this is much simpler, although would the set up have worked had the "Display all data" been set? I believe the example posted by the other extremely helpful member had this set up as well, although this would be following on from my first post and my described set up.

Excellent assistance and following a response to the above queries I will mark this solved.

Many thanks to all.

[DACM]

...how do you access that option after the form is created?

1. Click Forms
2. Right-click the Form name > Edit
3. Right-click a Form Control (such as a Text Box) > Form...
4. Form Properties (dialog box) > Data (tab)

or as your Forms get more complex...
1. Right-click the Form > Edit
2. View > Toolbars > Form Designer
3. On the Form Designer toolbar: Click > Form Navigator (5th button)
4. Right-click a specific subform > Properties... > Data tab

...would the

• set up have worked had the "Display all data" been set?

No, a List Box cannot be populated with Column [Field] names derived from a Table's structure in any case. I've actually tried something similar in the past using the database's "Information Schema" within a nested SQL statement. I concluded that it's not possible without writing "stored procedures."

When populating a List Box using SQL, the Column [Field] names may be used only within the SQL-query, which in-turn retrieves the associated record content. That content is returned in the form of a list which populates the List Box 'display' field and 'bound' field respectively.

• The following SQL uses the Column [Field] names to specify which data to retrieve:
  

  Code: Select all

  SELECT "Name", "ID" from "Contacts"

  The SQL-query returns the following results, which are used internally by the List Box to populate the drop-down 'display' field and the hidden 'bound' field respectively:
  

  Code: Select all

  John       0
  Tammy      1
  Billy      2
  Tom        3

  ...so if the user selects 'Tammy' from the drop-down list, then the integer '1' is saved to the underlying Form's table. In other words, the List Box displays the "Name" field as a drop-down list, while saving the selected "ID" field value (primary key) from the "Contacts" table, to a record in the Form's underlying table, as a 'foreign key.'

The 'Add data only' property is unrelated to populating the List Box. This Form property 'only' allows the Form to generate/display new records, which may or may-not be your objective.

[det]

I have followed this thread with great interest. But I'm still stuck. I have tried all the suggestions. I get as far as a blank square as a list box. No entries, no scroll bar, nothing.
Can someone tell what I'm doing wrong?
The Base File is attached.
Det

[Villeroy]

You want to enter the right weekday number into the accounts table by selecting a weekday name from the list box.
Your form needs to be bound to the accounts table. Now it is the weekdays table.
Your list box needs to be bound to the weekdays table. Now it is the accounts table.

[det]

I downloaded your File. If I click on Form then "Edit", I see an empty Table with the headings "Expenses" and "WEEKDAY". To the right I see "Stand alone Variant" with an empty white field below it. I don't see any Listbox. If I click design mode on/off, Open Office crashes.
After recovery I now click Form, then "Open" and Open Office crashes again.
I have Mac high Sierra. could that be the problem?

[UnklDonald418]

There are a number of reports that indicate Mac users seem to have more problems with Open Office than other users, but if your uploaded database file doesn't crash then start from there.
Right click on the the form Accounting and select Edit to open it in the Design Mode.
First delete the existing control, it is useless on this form.
Look along the bottom of the window and select the Form Navigator to open the dialog.
In the Form Navigator dialog right click on MainForm and select Properties.
On the Data tab of the Form Properties dialog make sure Content Type is Table and Content is Accounts.
Next along the left margin make sure Wizards On/Off is selected (the background of the icon will be something other than white).
Also along the left margin above the Wizards icon is the list of Form Controls, select More Controls to get a pop-up of additional controls.
Select Table Control and click and drag the cross hairs to define an area for the table on your form.
You should now see the Table Element Wizard.
Select all 3 of the fields to add them to the table.
Right click on the WEEKDAY column and select List Box to open the Properties: List Box dialog.
On the Data tab change Type of List Contents to Sql
change List Contents to

Code: Select all

SELECT "WEEKDAYS", "WEEK-ID" FROM "Weekdays";

Type <Ctrl>S to save the form.
Now select Design Mode On/Off to go into live mode.
The table should now be populated with data from the Accounts table, and the WEEKDAYS column should display a down arrow and allow you to select a day from the list for each of the rows on the table.

[det]

Hello Mr Colorado,
Thank you for the detailed instruction and the time you spent.
I got as far as "Select Table Control and click and drag the cross hairs to define an area for the table on your form." Now I see the Table Element Wizard with 3 "Existing fields" on the left, which I drag over the right into "Selected fields".
(I changed the Column names to differentiate between names for Tables Forms and Headings)
What do I now?? If I click "Finish", I see an empty table with 3 columns headings, but no List Box,
When I right click the Weekday Column I get the Menu: "Insert Col, Replace with, Delete Col, Hide Col. and Column".
There is no "List Box" to open the Properties: List Box dialog. How do I get to the Listbox properties, if there is no List Box?
If I exit the design mode Open Office crashes again. Am I missing something? Should I create a List Box first before I create the Table? Or did the change of column names create havoc?
I attach my File again.
Det

[UnklDonald418]

Sorry, it appears I left out a detail or two, so let me try again.
Looking at your Visits table there is one issue that needs to be resolved. Change the wkday field from Text[VARCHAR] to Integer.
Now open the form Accounting in the Edit/Design mode.
Right click on the wkday column heading and first select Replace with and then select List Box.
You probably won't have a Properties dialog open so you will need to again right click on the wkday column heading and this time select Column to open the Properties: List Box dialog.
After setting the Type of List Contents to Sql and List Contents to

Code: Select all

SELECT "dayofweek", "wkid" FROM "Weekdays"

select the General tab and change Line Count to 8 so you can see all the possible list box selections without scrolling.

The only suggestion I have concerning the crashing problem is to try Libre Office and see if it works any better.

[det]

Thank you again. Everything works fine in Libre Office. If I click anything in the Form with design Mode = off, Open Office crashes. I have the impression that Libre Office is better suited for Mac. Especially the Sheet Tab in Calc are so tiny, I need a magnifying glass to read. This has been posted as a Bug in another thread of mine.
Det

[DouglasUK]

Thank you to those who have helped on this thread, particularly Merlot for posting an example.

I am far from a newbie to databases but was having trouble, having thought to use wizards as a short-cut to learning the specifics of OO Base front-end.

@Villeroy

Forget all those bloody wizards. In most practical use cases they are counter productive.

OK, lesson learnt. A wizard is supposed to be there to help do the simple, repetitive tasks without stopping later enhancement. A nice try from someone for coding up wizards but it's a pity they haven't been improved since (they are a bugger to code, generally).

My break-through came when I started without a wizard and managed to get an option on Controls|Data to set the 'Bound field'. The wizard not only sets this wrongly but also hides it from being available for edit, messing up any mildly serious font-end usage and completely messing me up in attempting to sort it out 'by hand'. Seeing the example from Merlot showed me what was missing.

If anyone knows how to get the 'Bound field' value to be accessible after it has been hidden, I'd like to know for future reference.