Drop down menus with relationships

[k57skye]

Full newbie here, so not sure if this is in the right place or if its been covered a million times. Dont really understand the gargon so its hard to search efficiently.

I am trying to set up a simple datebase to record who has pictures in a number of set magazines. I have 5 fields. Magazines, issue, person, page number and size of picture. I managed to set this up with drop down menus very easily.

What I want to do is somehow link the magazines and issues. For example, 1 magazine has 10 issues a year, whilst another has 6. Each issue is named different (eg. spring or Jan/feb etc). I wanted to set up the layout so when you select a particular magazine, it will then only offer the correct issues in the issue menu.

Any help with this would be greatly appreciated.

[PamRotella]

I'm not 100% clear on what you're asking, but I think what you mean is, you want the drop-down list for one item to actually change according to something that has been selected in another field (probably from another drop-down). If so, this is done by a combination of what's usually an additional relationship, and the value list setting "Include only related values starting from...".

I could give you a fictional sample, but why don't you take this info and try it out on your own first? You'll probably figure it out without additional help.

But if you need more help, could you post some specifics to help us understand exactly what you want?

[k57skye]

Thanks for trying to help, but I still cant really work it out. I am sure its simple, but when I start getting into it I just dont really understand the jargon.

As I tried to explain before I am trying to Catologue which people appear in which magazine. My 5 factors are: Magazine, issue, person, page number, size of pic.

If I have 3 magazines each with different number of issues. eg:

Boards: Jan/feb, march, April, May, June, Autumn

Wind: Spring, Summer, Autumn, Winter

Surf: Spring, Mar/april, May/June, Summer special, September.

So now in my database I want it so when I select (with a drop down) Boards in the first (magazine) field, it then only comes up with the 6 possible options in the second (issue) field.

Does that make more sense? And thanks for the help.

[bcooney]

I would design it with the following tables:

Magazines

Issues (stores _kF_MagID)

Pictures (store _kF_IssueID) and page#

People

Magazine have many issues. Issues have many pictures. Ah! Do pictures have many people?

If so, we need another table PicPeo that sits between Pictures and People and stores the combos (_kF_PicID and _kF_PeoID).

After you confirm this sounds about right, then you can build the proper interface, with conditional value lists.

[k57skye]

I dont think it needs to be so complicated.

The magazines are a set number.... There are about 15 of them. The issues are a set number... between 4 and 14.

The people are a set number... between 4 and 10

Picture size is one of 5 different sizes... 1/4 page, 1/2 page, etc

The page number is the only thing that is different.

Do I still need all the different relationships??

[comment]

It's actually simpler with separate tables - you only need to enter the magazine details once, instead of ... say 10 issues x 4 pictures, that's 40 times.

Anyway, what you're looking for is a conditional value list. You can find many threads discussing this topic, e.g.

http://www.fmforums.com/forum/showtopic.php?tid/174320/

And yes, you will need to establish relationships to make it work - whether between separate tables or between a single table and itself.

Edited August 2, 2008 by Guest

[k57skye]

Thanks again for your help. I have somehow managed to do it, although I cant get it to look nice!!

I have attached the file if anyone is interested.

Basically I can look through each magazine, issue by issue, and when I see a picture of one of the people (sailors) I log it in the database: Magazine, issue, person, page number and size of picture.

Once inputted I then then call up a list of all the pictures by sailor, to let them know what pictures they have and where they are. Or I can call up the lists per magazine and issue to see all the pics of the all sailors.

Again.. not pretty but I think effective. Thanks a lot for everyones help. In the end the link to the other thread was the key and I managed to modify that.

TestMCC.fp7.zip

[comment]

I think I would do it this way.

Pictures.fp7.zip

[k57skye]

Wow... thanks a lot... that looks perfect. I now need to spend a while going through it all to try to understand it all.

Thanks again.

John

[Agent Ska]

I'm trying to go one step further, meaning I am relating to one more table than the example shown.

I am sure I am missing something basic. Can someone explain what is incorrect with this setup?

I can specify a job-ID, and get the first pull down menu to work fine. It is the second level that I am having difficulty getting to work.

relationalDatabaseTest.fp7.zip

[bruceR]

Like this.

cascadeValueList.fp7.zip

Edited September 11, 2008 by Guest

[Agent Ska]

sweet. Thanks!