[Solved] Help with queries and forms

[mialau]

Hello! So, I am supernew to OpenOffice base and I need some help.

I want to make some forms so that the are easy to use by the users, insert a new row in the database, etc.

The one thing I'm having problems with is the queries in a form.
I want the user to be able to select a dropdown from one of the related tables and get back the data just from the selected option in the dropdown and I have no idea how to do that tbh. If you could help me!

Thank you so much, I'll attach the DB (it's just a simple example so that you can help me solve the problem)

[UnklDonald418]

Look at the Example Form in the attached .odb file
It uses a ListBox to select/display the "currentPlacement"

Open the Form in the Edit/Design Mode
Right click on the column CurrentPlacement and select Column to open the Listbox Properties dialog.
On the Data tab the List content contains the query.

Code: Select all

SELECT "currentPlacement", "idPlacement" FROM "Placement_TABLE" ORDER BY "currentPlacement" ASC

ListBox queries take the general format of

Code: Select all

Select "Display Field", "Bound Field" From "Some Table"

The ORDER BY clause is optional and probably not needed since you only have 5 choices.

"Bound Field" is the integer value corresponding to the "Display Field" in "Some Table"

Club01.odb

(11.95 KiB) Downloaded 97 times

[mialau]

Hello! But now I can see where I can just show in the List the data that is currently in the DB table, but I can't see how to still do what I want to do.

What I meant is that when the user opens the form, it will have that List Content where every single currentPlacement is displayed and when you select one it shows me a table with the results for that one, for example Winger

Thank you so much

[UnklDonald418]

What I meant is that when the user opens the form, it will have that List Content where every single currentPlacement is displayed and when you select one it shows me a table with the results for that one, for example Winger

That can be accomplished with a simple Form and SubForm.
Here is a link to a Base User Manual https://documentation.libreoffice.org/a ... eGuide.pdf

In your main database window select Forms >Use Wizard to Create Form
From the 1st page of the Form Wizard select Table: Placement_TABLE
Add both of the Available fields to Fields in the form
Next to get to page 2 of the wizard.
Check Add Subform then Subform based on existing relation and select players
Next to get page 3 of the wizard and add all the Available Fields to Fields in Form.
Next should take you to page 5 of the wizard.
For this use Table controls (the wizard will display as Data Sheet) for both main form and subform
Next to get page 6 of the wizard
Next to accept the defaults on page 6 and move to page 7 of the wizard
You can use the default or pick a different color combination for your new form.
Next to page 8 of the wizard
You can use the default or type in a new Name of the form
Check Work with the form
select Finish
the new form will open.
select a placement in the upper table and the lower table will display all players with that placement.

[Villeroy]

[Example] Relations reflected by list boxes in forms