Display M-to-M - Related Occurances

[H.P.]

Hi all,

I am new user of filemaker, using filemaker 7.0. I am trying to design a small database that manage projects for a company.

I have problem displaying field with specific criteria. My database contains the following entities:

Project {projectID, projectName}

Project Role {roleID, roleName}

Staff {staffID, staffName}

And a joint entity:

Project Staff {projectID, roleID, staffID, assignmentID}

There are several roles such as Production Coordinator, Graphic Designer, and Manager

I would like to display on separate areas on the form staff names related to different roles, such as : Production Coordinator and Graphic Designer. I do not want a portal list that display all roles and staff related to the specific project. I want to display staff names for certain roles related to the project only.

Example:

~~~~~~ Production Coordinator:

John Wendy

Peter Winny

Other form information.....

~~~~~~ Managers:

John Wendy

Sophie

I am clueless on how to solve this problem.... spent 2 days and still haven't got a clue yet. Could someone pls post a hint or solution to this problem?

Thx a lot,

H.P.

FileMaker Version: 7

Platform: Windows 2000

[Reed]

Without knowing much about your specific problem, I can point you to a more general example about a many-to-many relationship I posted a couple of days ago.

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=100278&page=0&view=collapsed&sb=5&o=all&fpart=1&vc=1

[H.P.]

Thank you for your help. My problem is a little different from the example you provided in the other thread.

I attached the original example you have posted with some modifications. Please take a look at layout for table A, I think it better explains my question.

Basically I want to add another table: table C, and the joint table would be tableABC. The condition for the table is dependent on the relationship between the 3 table.

It's really easy to solve this problem using query in relational DB such as access/mysql/oracle. It's so hard to do it in FM though The learning curve is quite high for switching to filemaker :

Please help with you can.

Thx,

HP.

FileMaker Version: 6

Platform: Windows 2000

M2M.zip

[Ender]

It's difficult to get one's head around "TableA", "TableB", "TableC". You should name your tables and fields so they actually mean something. Much easier to understand the relationships that way.

[H.P.]

Thx for pointing that out. Here's the detail on the tables:

Table A = Person

Table B = Car

Table C = Dealer

Table ABC = joint table

A person has many car. Each car is sold by a dealer. There are multiple dealers: dealer 1, dealer 2, dealer 3, etc...

I want to display in the Person layout different portals: Portal 1: All cars sold to this person by Dealer 1. Portal 2: All cars sold to this person by Dealer 3.

Thx,

H.P.

[Reed]

I don't have time right now to modify the example. (Got to go to work) But you could filter the portal by dealerID. make the relationship from table a to the join a two key relationship.

[H.P.]

I'm sorry I don't really understand what you mean.

The tables aleady joint by a joint table (relating 3 tabless). How do I join A to a 2-key relationship?

If I filter by tables, it would still list all dealers related to that person, isn't it?. I want to display cars sold by one or more specific dealers instead of listing all cars sold by all dealers for that person (eg. 2 portals: portal 1 to display cars sold by dealer 4 for that person and portal 2 to display cars sold by dealer 7 for that person)

[Reed]

I'm at work now, so I have attached a modified file... All I did was create a global field in Table A and modify its relationship to include a constraint between the global and TableC_ID. Now all portals that use that relationship will be filtered by what you enter into that global. try putting different dealerID numbers into the global and see what happens.

If you want to have another layout that is set up in a completely different relational structure, just create addtional table occurrences in the define database/relationships dialog and group them separately.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

M2M-1.fp7.zip

[H.P.]

Thanks for helping : Seems like it's impossible to help more than one filter on the same layer. The approach you showed in the example will allow only one filter at a time. Is it possible to have more than 1 filter in the same layout or it is an impossible task with filemaker?

Although your example didn't exactly solved my problem, it still helped me a lot in this project. It's a great relationship example for beginners in Filemaker like me.

H.P.

[Reed]

actually, you could link several occurrence of the join table to a single occurrence of table A, each with a different relationship. Then link the joins to whatever other tables you want. Kind of like a hub and spokes.

[H.P.]

yeapi... I got it :

Each filter would require a separate global key to appear in the same layout.

How naive I was, keep trying to make occurances of Tables A or C instead of the second occurance of the joint tables

Thanks a lot for your help and patience. I included the example in here anyway in case someone else is in the same situation as I was.

H.P.

FileMaker Version: 7

Platform: Windows 2000

M2M.zip