comboBox filter from another combobox?

[J__]

Hi,

I think I need a relation here, so I posted here, if not, please point me to the right forum.

I'm trying to get better at FM and I came up with this little fun project for keeping movies that me and my mom own in a fm database.

Here's what I am trying to do:

I have a 2 Dropdown listboxes/Combo Boxes. The first dropdown contains movie ratings. Say R, PG, PG-13 and G. The second drop down contains a list of movies.

The user picks from the first drop down a rating, say 'G'. In the next drop down, it's contents should contain only G rated movies. If the user picks PG rated movies, then only PG should be displayed.

The user (actually one of my family members who borrows my movies) can then pick the movie they want and the rating drop down selection and the movie drop down selection should be saved in the user table for that use.

So, I'm basically trying to filter the second list based on the first list... and then save it.

again if this is not the right forum my apologies. Just say where I should Post this.

Also, thanks for any suggestions or help in advance,

Sincerely,

J__

[QuinTech]

Hi, J___. You're in the right forum for this question. We normally only get grouchy about posts in the wrong section when it's blatantly ridiculous, like most of the posts in the "About FM Forums" forum.

You are approaching this in the right way. One thing to be sure of is that your selection is from a field (best if it's global) that is different from the field where the movie's actual rating is stored. That is, you'll need at least four fields--

MovieName (Text)

MovieRating (Text)

RatingSelection (Global, Text)

MovieSelection (Global, Text)

When you enter movies, fill in the first two fields. When your family wants to select a movie, she should use the second two fields. To implement:

Create a relationship from RatingSelection to MovieRating. Now create a value list that gets its values from a field, through a relationship, from the field Movies 2::MovieName. Attach that value list to the MovieSelection field.

Now, whenever the user enters an R into RatingSelection (also a good candidate for using a value list from a field), the value list on MovieSelection will show only R-rated movies.

See the attached for an example of how this works.

HTH,

Jerry

Movies.zip

[J__]

Hi Jerry,

Thanks for the sample, I really appreciate it. A few questions:

1. Is the second table in relations a self-join on the first?

It's the same table right? There is no second table.

2. Here though the list of movies is being displayed from the actual movie table. It looks like if I add another record, all the records contain the same data.

to avoid this behavior is the idea to separate the value list into a separate table

So you use the global fields to select from the value lists, which are reading from a relation and you say 'only show related' records.... hmm cool.

I have to play around with this a little.

thanks,

sincerely,

J__

[QuinTech]

1. Is the second table in relations a self-join on the first?

It's the same table right? There is no second table.

That is correct, it is just a second table occurrence. This structure works fine for the limited functionality of the program you have now, but your needs might soon outgrow this. Imagine, for example, you owned a chain of movie theaters. First, i wish you would lower your prices. Second, you would probably want different tables for Movies, Theatres, Screens, etc. In a scenario like this, the "Chooser" fields might sit in a different table altogether. But for example purposes, this is fine.

2. Here though the list of movies is being displayed from the actual movie table. It looks like if I add another record, all the records contain the same data.

to avoid this behavior is the idea to separate the value list into a separate table

I'm sorry, i may have misunderstood your original post. How exactly do you want to determine the contents of the value list? If the example i sent does not show only those movies where RatingSelection matches MovieRating, i may have miscoded.

Keep in mind that RatingSelection and MovieSelection are globals and thus, yes, the value is the same for all records. But you would not enter data for movies in these fields, they are only used for selection purposes. (You may have missed the fact that the two layouts are labeled "Enter Data Here" and "Select Movies Here.") If you add data to MovieName and MovieRating on the layout "Enter Data Here," you will see that that data is not added to all records.

J

[J__]

Hi,

First let me say "thanks" for your responses.

I was after the separate table approach of having a table with MyMovies

and then a table which is Users and CheckedOutMovies.

So basically my User (one of my family members [10+]) goes to a layout and clicks the 'Check out movie' button or something like that. a 'CheckedOutMovie' record is created (with their user name - from the user table in it). They pick a rating and then pick a movie from the drop down list of movies. I only let them check out one at a time (have to keep track of my flicks) and then at some point, when i get into reporting, I'll print out who has my movies checked out!

Regarding your response to my second quote:

I did follow that you had one view for selection and one for adding movies to the selection list.

my goal though is not only to allow a selection of the movie, but to store that in a table that they have selected the movie. Do I need to write a script. One of the things I struggle with is that there's no events to notify you of things.. like if you an 'OnComboBoxClicked' event occurred, then I could put that value into the checkedOutMovies table's movieName field.

if you have tips on this or a reference you would like to point me to, I would appreciate it.

again,

thanks for your help,

Sincerely

J__