Many to Many relationship problem

[stephiesmith1]

I still don't fully understand the relationship setups and a couple of things are giving me fits! I'm creating a performance assessment database. Aside from self assessments, each employee may have assessments from their Team Lead(s), Peer(s) and Supervisor. For the supervisor relationship, I used a self-join to the Employees table (straight out of Filemaker 7 Special Edition:-). That works fine; I can show the employee's supervisor on his record and I can also show all of the people the employee supervises via a portal.

The problem is with the Team Leads(TL) and Peers (many to many). The supervisor looks at each of his personnel and decides if peer inputs and team lead inputs are called for. If so, he enters the peers and TLs for that employee. Currently, I have a Peers table set up that is another occurrence of the Employees table and I have a table called Peer Input where the field EmployeeID matches the EmployeeID in the Employees table and the PeerID matches the EmployeeID in the Peers table. That works for selecting the Peers for that employee and also creates the Peer Input form which will be used by the Peer. The PROBLEM: It is the PEER who will be coming into the database to fill out the Peer Input form and I can't figure out how to get those records to show up automatically for the Peer after the supervisor selects the Peer to do an assessment. Ideally, the Peer will come into the database and have a list of people who require his Peer assessment and when he clicks on a record in the portal, he will go to the Peer Input form that's been set up for that Employee.

I've attached a screenshot of the employee layout which may help you to see what I'm trying to do. If I got this to work correctly, then on my record under "Peers to Review" at the bottom, the name "Jamie Oldham" would show up since her supervisor has designated me as a Peer to review her, as you can see at the top right of her own record.

I could tell you all the things I've tried, but that's pointless because if I did one thing wrong, like put the wrong field in the portal, it wouldn't have worked, so I don't know if I've been close or way off. Does anyone know how I can do this? I have a similar thing to do with Team Leads.

[CyborgSam]

Stephie->

Welcome to the forums!

For many-to-many relationships, the usual practice is to make another table (not just a table occurence) which holds as a minimum the keys to each of the other tables. In your case the "other tables" are the same table. So this new table would associate a person to a peer. When viewing a person's record, you would have a portal with all the related peers. I've attached a quick & dirty demo of this in action. Check out the Relationship Graph & layouts to see how it works.

Stephie.fp7.zip

[stephiesmith1]

Thanks, Cyborg Sam. Your db straightened out a problem I didn't realize I had...my join table, which is a Peer Input form, had previously been working correctly, i.e., when on the Employee layout and I added a peer for that particular employee, a Peer Input form was created (Peer Input FOR that Employee and FROM a selected Peer) but somehow, while switching everything around a zillion times, I changed something and those records weren't being created correctly. Your database solved that problem for me, and now I can add a peer for an employee from either the Employee table or the join table which is great.

I still have the other thing that I haven't figured out, which is the same relationship backwards, I guess. In your database you have a Person table, which I equate to my Employee table. Then you have the Peers table which I equate to my Peers table. And we both have the join set up the same way (now:-).

What I also need to be able to do is allow the PEER to come into the database and see a portal list that shows all of the employees that he owes reviews for. Using your db for the example, in the Person table, record 5 is for Doug Out and he has two peers selected--Pete Moss and Jim Nayseeum, who will give reviews on him. So when Pete and Jim come into the database, they should be able to see a list of people they OWE reviews on and Doug Out's name should be on it. Does that make sense?

Thanks again for helping with the first half.

[stephiesmith1]

I have 2 questions. (1) In the sample database from CyborgSam, neither relationship is set to allow creation of records. Sam, was that your intention and whether yea or nay, records are being created in the join table. Why is that if the relationship is set not to do it? (2) I noticed that if I use a drop-down list for your value list in the portal, nothing shows up, but if I use the pop-up menu, the name shows up (you have the value list set for the ID number and the corresponding name as the 2nd field, but to show only the name). Why does the value list show up with a pop-up menu and not a drop-down list?

I see now that if I make a new table called Peers, I can show that list of employees who the Peer needs to review through a portal of records from the join table. But since I want an employee to come in and see if he owes peer review(s) and/or team lead review(s) and/or supervisor review(s), I'm not sure how to work that on one layout. I'm thinking now that I should have made the Performance Assessment Form (not mentioned previously except as a self-assessment) a Star Join table, having it not only relate to the Employee table and the Performance Measures table, but also the Peer Input Form, especially since the Team Leads and the Supervisor will put their review on that same form after the Employee completes his self-assessment.

This is mind-boggling...

[CyborgSam]

In the sample database from CyborgSam, neither relationship is set to allow creation of records. Sam, was that your intention

No, that was a very quick & dirty demo just to show the join. I didn't set it up to properly allow entries through portals.

I noticed that if I use a drop-down list for your value list in the portal, nothing shows up, but if I use the pop-up menu, the name shows up (you have the value list set for the ID number and the corresponding name as the 2nd field, but to show only the name). Why does the value list show up with a pop-up menu and not a drop-down list?

The field in the Portal is a calculation that concatenates the first & last name. IMO, I generally don't like the concept of data entry in portals, so I tend to treat portals as plain lists where a line can be clicked on to bring up all that record's info.

One thing that may be better to do before continuing: create an Entity-Relationship Diagram (ERD). This will outline exactly what pieces of info you have (entities) and how the relate to each other. Once you understand how the process you're trying to put in the database fits together, it will be a lot easier to visualize how to implement the ERD in FileMaker using tables and the Relationship Graph. Yea, it can be a PITA, but if it gets you to the point of truly understanding your process, it's more than worth the effort.

[stephiesmith1]

Thanks for your help, Cyborg Sam. I think I've got it figured out now (see manytomany.jpg), though I won't say it's perfect as I will probably find some problems as I go forward. Since I need to be able to see the Employee/Supervisor, Employee/Peer, and Employee/Team Lead related records from both sides on the Employee layout, I ended up adding a 2nd occurrence of the same tables for each pair. So for the Peer Input, I have the Employee table joined to the Peer table via a PeerInput form/table. For the second view of the relationship, I have a PeerInput2 form and a Peer2 table with a different relationship from the first set. All the portals have a pop-up menu using the value list of EmployeeNumbers with a second field "Name" and set only to show the name as you did in your example (thanks for that...it makes it easy to make a match record) since the PeerID, SupvID and TeamLeadID are all actually that person's EmployeeNumber. The portals at the bottom are from the 2nd set of tables (Peer2, etc) and view only as the info comes in from other records; the pop-up menu fields use the Employee:EmployeeNumber field and are formatted as mentioned above. The portals to the right, i.e., the ones that are used for inputting information for that particular employee,show records from the first relationship and are also formatted the same way as the other portals but use the field PeerID, TeamLeadID and SupvID as opposed to EmployeeNumber. Sounds confusing, doesn't it but so far everything seems to work.

Stephie