|  Organizer Advantage HowTo/Help  |  Designer HowTo/Help  | 

Designer Help > Build Database Simple > Music Collection

How can I link a combo box (or list box) to display values from another table?

How do we define the relationship between two tables where data from one can be used to populate the other table?

Music Collection Sample (CD, Vinyl Records, Tapes)

We maintain a database for artists and bands in the ARTIST_BAND table. Our music collections are stored separately in the COLLECTION table. In this context, we can link the ARTIST combo box to the ARTIST_BAND table, allowing us to select an artist or band easily. By doing so, we can automatically fill in related information in the COLLECTION table, such as the country of the band selected. This functionality enhances data management and user experience.

ARTIST_BAND table
We enter data about artists and bands into the ARTIST_BAND table. The structure of this table is as follows:
ID:Integer:
ARTIST_BAND:Text:60
COUNTRY:Text:20
INFO:Memo:
WEB:Text:200

COLLECTION table
We store information about our music collections in the COLLECTION table. The structure of this table is as follows:
ID:Integer:
TITLE:Text:200
ARTIST:Text:60 (we will link this field with ARTIST_BAND(table)/ARTIST_BAND(field))
COUNTRY:Text:20 (the field will display country value based on the ARTIST selection)
GENRE:Text:40
LABEL:Text:80
MEDIATYPE:Text:60
CATALOGNO:Text:40
DESCRIPTION:Memo:
NOTES:Memo:
PICTURE:Text:200

How can I link a combo box (or list box) to display values from another table?

• Start the Designer and select the COLLECTION table.
• Open the Layout page (1) and activate Design Mode (2).
• Click on the ARTIST box (3) to select it, then click on Modify Control Attributes (4) to display the Set Control Attributes options.
• Click on Set List SQL (5) to establish a link with the field from another table.
• Select the appropriate table and field (6), then click Update List SQL (7).
• To save your definitions, click OK (8), Save (8a), and Save Layout (8b).

Now, when you open the COLLECTION and click on the ARTIST list box, it will display the values entered in the ARTIST_BAND table.

How do we define the relationship between two tables where data from one can be used to populate the other table?

Above, we established a link between the two tables. Now, we want to populate data in the destination table using values from the destination table.

• Open the Layout page (1) and activate Design Mode (2).
• Click on the COUNTRY box (3) to select it, then click on Modify Control Attributes (4) to display the Set Control Attributes options.
• Enter the following query in the SQL Query box (5). Click on the line and input the statement.
  SELECT COUNTRY FROM ARTIST_BAND WHERE ARTIST_BAND='%1'
• Next, click on the line below and enter ARTST for the MasterField1 attribute (6).
• To save your changes, click Save 7, and Save Layout (7a).

Now, when in Organizer Advantage, open the COLLECTION table and select a value from the ARTIST list box, it will automatically assign the corresponding country value (COUNTRY).

Organizer Advantage, Simple Database Solutions for Windows PC

Organizer Advantage, Business Database Solutions for Windows PC

Organizer Advantage Free Service

Organizer Advantage Support