Base: Repeating Drop Down Subform Menu: Need Help w/Master and Slave Linking

English
#1

Hello, may I please request some assistance with a repeating drop down menu in a subform?

I have a bit of experience with Access and Filemaker Pro, but I’m new to Base. It’s a bit confusing.

I’m creating a movie collection database. But I’m going to need three subforms: One for Genre, one for Actors, and one for Location.

(Location = where the movie is located on my shelves.)

I’d like to deal just with Genre right now, because once I learn how to set it up as a repeating drop down menu, I should be able to do the same with the other two subforms.

So, I’ve created a many-to-many relationship database. (Many movies have more than one Genre, which is why I made it M-to-M.)

The Movie table contains the fields MoviePK and Title. (I prefer to name my primary key and foreign key fields with PK and FK. It helps me keep track of what’s what, because I’m not really an experienced database designer.)

Is it possible for me to ask you to take a quick look at the database’s relationships, and see if I linked everything up properly? I think I did, but with Base, I’m not 100% sure yet.

https://tinyurl.com/2w7tkmbt

Moving on to the subform:

Creating the subform is a bit confusing to me. I’m not sure which table to base the subform on. With FileMaker, I can select fields in multiple tables. I can create repeating drop down menus quickly and easily. Adjusting to Base is a bit more work.

Do I use the Join table or the Genre table to create the subform? I experimented with the Genre table, but the master and slave fields have me a bit confused. I selected MoviePK for the master field and GenrePK for the slave. I am sure that’s wrong.

If anyone is willing to give me a nudge in the right direction, and help me select the tables, fields, and linkage, that would be great.

I have read documentation and watched a few videos, but the only one I found on repeating drop down menus is considerably old and has no narration.

I’d be very, sincerely grateful for any assistance. Thank you very much. J. Danniel

#2

ask119354.odb (14.1 KB)

The table relations and keys are all right.
This is a many-to-many relation between movies and genres where each movie may belong to zero, one or many genres and each genre may belong to zero, one or many movies.
Based on this foundation, I added 2 queries to list movies and genres with names in ascending order followed by the primary key. “lb_Movie” and “lb_Genres” are supposed to be used with list boxes.
There is only one way to build a form/subform combination for a many-to-many relation.
Master form: one list (“Movies” in this case)
Slave form: the linking table (“Join” in this case) linked to the master form by the common ID (“MoviePK” —> “MovieFK”).
The slave form needs to be a table grid with a column having a listbox showing the items of the other list (“lb_Genre”).
Now you can navigate to a movie and assign genres to it from a column of listboxes in the subform.

Homework: Make another form where you can assign movies to genres.

#3

Thank you so much for your reply and assistance.

I’m very very close, but I see one thing that I need to fix. Would you be willing to help me fix this one issue? (I mean, if it’s only one issue.)

I got the drop-down menu for genre working, but only to a point. When I select the first genre for a film, it works. However, when I try to add a second genre to the film, the first one disappears.

How close am I? Almost there?

Would you be willing to take a look at my revised database and see what needs to be tweaked? Hopefully it’s just something minor that I haven’t figured out yet. I’d be very grateful.

Thank you again.

https://tinyurl.com/33uhdmca

#4
  1. The listbox query includes only the name field. Append the primary key to that query as a second field which is (unfortunately and confusingly) in the listbox properties Bound Field = 1.
  2. I would rebuild the join table. It can take duplicate combinations of movie-IDs and genre-IDs because of the auto-ID.
    Tools>SQL…
CREATE TABLE "Join_New"(
    "MovieFK" INTEGER,
    "GenreFK" INTEGER,
    PRIMARY KEY ("MovieFK","GenreFK"),
    FOREIGN KEY ("MovieFK") REFERENCES "Movie" ("MoviePK"),
    FOREIGN KEY ("GenreFK") REFERENCES "Genre" ("GenrePK")
)

menu:View>Refresh Tables.
Have a look at the relations window.

#5

Okay, now we are getting into deeper water that is a bit over my head.
Please bear with me, as I’m not 100% sure how to approach this.

Could you please elaborate just a bit more on this:

“Append the primary key to that query as a second field which is (unfortunately and confusingly) in the listbox properties Bound Field = 1.”

I’m not sure what, exactly, this means.

And I’m not sure what rebuilding the join table does. What, exactly, does that SQL do?

From the looks of it, it seems it’s turning foreign keys into primary keys. If that’s true, it’s going to confuse the heck out of me. It’s going to take me some work to grasp that concept.

Please bear with me. I was really hoping a minor tweak would fix the drop down menu issue.

#6

SELECT "Genre", "GenrePK" FROM "Genre" ORDER BY "Genre" ASC
gives this:

Genre GenrePK
Action/Adventure 3
Comedy 0
Drama 1
Gangster/Crime 4
Mystery/Suspense 5
Science Fiction 2

First column is the visible one, second column has the values that are written to GenreFK.

Just run it and see.

#7

I think it works! Wow. Thank you. Big, tremendous help. Thank you again.

I think the breakthrough for me was when I figured out what you meant by appending the primary key.

I refreshed the tables, and the original Join table still appears, along with Join_New.

So, there are now two join tables in the relationships area. Is that the way it’s supposed to be?

Is the original Join table supposed to remain?

genre_final.odb (13.8 KB)

#8

Have a look at the key icons indicating the primary keys. Your join table has a separate auto-ID as PK. My join table has a combined PK of both columns.
With your table, any movie can be Comedy, Comedy and Comedy.
With my table, duplicate combinations of MovieFK and GenreFK are not accepted. Both columns are foreign keys and together they are one primary key which needs to store unique values. Each pair of genre and movie is a unique pair, otherwise it could not a primary key.
If you want to test my solution:
Copy the form and bind “Join_New” to the subform.

#9

With your table, any movie can be Comedy, Comedy and Comedy.
With my table, duplicate combinations of MovieFK and GenreFK are not accepted.

I didn’t think about that. Good point.

I’m still fuzzy on the concept of combined primary keys, and foreign keys being primary keys. Please give me time to let that sink in. I’m still not sure about the Join table.

#10

Just copy the icon of the form document and paste. You will be prompted for a name for the new form.
Having 2 copies of the same form document, open one copy for editing and change the subform binding from table “Join” to “Join_New”. Play with both forms. Try to enter duplicate genres and see what happens. Then think again.

#11

Interesting. If I enter COMEDY in the first row, fine.
If I enter COMEDY again in the second row, I get a warning and it won’t let it remain.

#12

Jaws is the movie with ID 0.
You select a record with MoviePK 0 in the main form.
The subform shows all records with the same movieFK 0 AND any new subform record inherits this movieFK from the parent form’s MoviePK.
You add a new record movie #0 preselected in the main form and genre #0 entered by selecting the ID of genre “Comedy” from the list box.
Now your Join table has a record storing the information that movie #0 belongs to genre #0

MovieFK GenreFK
0 0

MovieFK points to one distinct movie in the movies table with primary key 0. GenreFK points to one distinct movie in the genre table with primary key 0.
At the same time, the two columns are the combined primary key of that table. A primary key identifies each row of a table as distinct from other rows in the same table. For Base, the primary key makes it possible to “put a finger” on one distinct record for editing.
A second record with the same primary key (0,0) is a violation of the primary key as the error message states when you try to do so.

If you think that “Jaws” is not a good comedy, you delete that record from the “Join” table.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.