[Solved] Populate values from linked table in a List Box

Creating and using forms

[Solved] Populate values from linked table in a List Box

Postby nzeed » Tue Jan 17, 2017 10:49 pm

Hi there,

I'm having an issue with populating some data into a form List Box. I have created a many-to-many relationship using a linking table and the form I have created displays that data fine. I used https://www.youtube.com/watch?v=GYawYO8u3_s to get this far. I would now like the form to display data from a further table that is linked.

In my example, the form is based on tbl_DummyEpisode and displays data from tbl_AdversariesTest. The Species value in tbl_AdversariesTest is a foreign key linked to tbl_SpeciesTest. In the form, the SubForm contans the relevant field mapping and the Adversaries List Box contains the SQL lookup code (the Data field is set to AdversaryID).
Code: Select all   Expand viewCollapse view
"SELECT "Name", "ID" FROM "tbl_AdversariesTest""

As I said, that data is all displaying fine. However, I would like to display the Name value in tbl_SpeciesTest (instead of the current integar value from tbl_AdversariesTest).

relationship.jpg

form.jpg

Hopefully, this'll be an easy one for someone that knows what they're doing :)
Last edited by nzeed on Thu Jan 26, 2017 12:48 am, edited 2 times in total.
OOo 4.1.3 (Embedded database/HSQL db engine), Windows 10 - OO Base Newbie
User avatar
nzeed
 
Posts: 7
Joined: Tue Jan 17, 2017 10:01 pm

Re: Populate values from linked table in a List Box

Postby UnklDonald418 » Tue Jan 24, 2017 11:18 pm

In situations like this you are more likely to get a response to your question if you to upload a sample database, we hate having to create and populate a bunch of tables to test possible solutions.

That said try:
open your form in Edit mode then right click on the column"Species as text" to open the Properties: List Box dialog . On the Data tab select Sql as the Type of list contents then for the List Content enter
Code: Select all   Expand viewCollapse view
SELECT  "Name", "ID" FROM "tbl_SpeciesTest";
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1330
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Populate values from linked table in a List Box

Postby nzeed » Wed Jan 25, 2017 12:34 am

Thank you for responding. I'm getting some text in the "Species as text" feld now. However, it seems to be picking up the Name based on the ID from AdversariesTest, rather than from SpeciesTest (so in this example, "The Master" should be "Time Lords", not "Cybermen").

I've attached a sample database (thanks for the tip).

Who.odb
(14.21 KiB) Downloaded 169 times
OOo 4.1.3 (Embedded database/HSQL db engine), Windows 10 - OO Base Newbie
User avatar
nzeed
 
Posts: 7
Joined: Tue Jan 17, 2017 10:01 pm

Re: Populate values from linked table in a List Box

Postby MTP » Wed Jan 25, 2017 9:42 pm

Try this for the list content:
Code: Select all   Expand viewCollapse view
SELECT S."Name", A."ID" FROM "tbl_SpeciesTest" S INNER JOIN "tbl_AdversariesTest" A ON S."ID" = A."Species"
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Populate values from linked table in a List Box

Postby nzeed » Thu Jan 26, 2017 12:34 am

Top man, that works a treat. Thanks MTP.
OOo 4.1.3 (Embedded database/HSQL db engine), Windows 10 - OO Base Newbie
User avatar
nzeed
 
Posts: 7
Joined: Tue Jan 17, 2017 10:01 pm


Return to Forms

Who is online

Users browsing this forum: No registered users and 5 guests