relationships, match fileds, and portals, oh my!

[magnuna]

hi all. i'm running fm pro6 on a pc with windows me. i'm also new to filmaker, so bear with me. and i'm using it to work with the data i've collected for my phd thesis, so please bear with the odd db and field names.

ok, right now i have 4 dbs: titles, monuments, family and officials. their fields are as follows:

titles - name, date, complete list, eps, hons, admin, mil, pr

monuments - name(ego), date, type, loc, desc, scenes, ind/scene, titles(of ego)/scene

family - name(ego), date, members

officials - name, date

i've entered all the data in all the fields for the titles and monuments dbs. i should also mention that a number of these officials share the same name and date range (not actual dates, but kings names). also, an official can have multiple monuments that are the same type (i.e. a tomb, 2 statues, etc.)

the first thing i'd like to do is make the members field in the familydb a lookup to the ind/scene field in the monumentdb, which i assume will require multiple match fields. however, because most officials have more than one monument, and each monument may have different family members mentioned/depicted on it, i need this lookup to pull the data from the ind/scene field for *all* monuments related to the name(ego) in question. is this possible with all monuments in one db regardless of type, or do i need a separate db for each type? and going back to the match fields required - name(ego) and date are obvious, would i need to include type as well? do i need to create auto-serial ID#s in each db instead, or join dbs? i'm trying not use a portal for this because i want to edit the way it looks.

the next thing is portals. i'd like one in the familydb that links to the monumentdb where i choose the monument by name, date and type and it calls up the ind/scene info (c ind/scene). the problem is that i'm only allowed to show info from 2 fields in the value list. is there a way to add a 3rd? or do i need to make 3 fields in the portal and make them relational? this also brings in the question of needing ID#s i think. (i also want to do this in the officialsdb, calling up the titles/scene for each monument in 1 portal, and the ind/scene in a 2nd portal).

and lastly, is there a way to use a button, either on the layout, or in a portal, that will show me a window into another database? what i would like to do is be looking at a record in the officialsdb and click on a button that connects to the title entry for that person in the titlesdb. but i'd like it as a small window pop-up, without actually jumping to the other databse itself. and could i do this for the monument records as well - where i can jump from the portal info in the officialsdb or familydb to the full info on a particular monument?

i hope this isn't too confusing. i can attach a file if that would help - let me know! and thanks a lot for any help/advice/comments.

[kenseye]

You may want to attack your request one at a time keeping your post short and more managable without writing another thesis. Which is the problems you want to address first?

[magnuna]

sorry about that.

i guess the first thing i list is the one to start with. creating a lookup from the familydb into the monuments db that takes into account the issue of multiple monuments.

thanks.

[Lee Smith]

Hi Ken,

It is always a fine line between supplying enough, and too much information. It does get confusing when several questions are asked at the same time, but if they are related, I think it is better to get them all out up front, rather than have them come out one at a time only to find out that we have wasted a bunch of time giving help and moving the poster down the wrong lane to solve their situation, when we finally see the "BIG" picture.

Hi magnuna,

I sure that hidden in there is all of the information that is needed for us to help you solve this. However, rather than sending any files, why not post your ERD (Entity Relationship Diagram) for your files.

Lee

[magnuna]

ok, here goes with the ERD. i hope the attachment comes through.

thanks.

ERD.pdf

[Ugo DI LUCA]

Agreed with all others that you should be more concise.

Thus, from what you said, you may start by searching for these "keywords" here on the Forum :

- Join File

- Many to Many relationships

- Filtering Portals

- Conditional Value Lists

- Go To Related record

[Lee Smith]

Hi magnuna,

I'm afraid that I have to give you an incomplete on this assignment.

Where are you fields? What are your Primary keys and Foreign Keys?

If you take the time to finish this, you will be surprised at how many of your relationship questioons will be answered.

Lee

[magnuna]

ok. in listing out the fields, etc. to send, I discovered that perhaps there's a problem in the primary key-foreign key area. so i guess that's the first issue. i think this question was actually in my first post - buried somewhere.

as you'll see in the attachment, name and date are the same in all dbs, so i made them primarys in 2 dbs, and foreign in 2 others. but perhaps due to the few unique names and dates (there's a *lot* of overlap in ancient egypt), i need to have primary and foriegn keys that are actual ID#s, auto-generated by fm?

thanks. i'll check out the recommended "key word" searches. and i hope i don't get another incomplete - it goes against by nature!

ERD.pdf