Hello, Chris. Welcome!
You're on the right track. A join table is the "right" way to implement a many-to-many relationship. Here are some pointers:
1) At its most basic level, a join table will contain a field for the key of each table it's joining together. In your example, it would contain EmployeeID and ReviewID. Each record in the join table, therefore, is a unique combination of the review and an employee to which it relates. You shouldn't ever have a duplicate of the combination of the two (although obviously an employee can appear more than once, and a review can appear more than once - that's the point).
2) Many folks will also include a unique, separate key field for the join table itself. This can be useful if you want to identify a particular join record for scripting purposes, but it's not absolutely necessary. (The combination of the other keys is, as I said, unique.)
3) You can implement the join table through a portal on each of the parent tables. For example, if I'm looking at a review record, I can have a pull-down of available employee ID values (along with, say, a name) to insert into the join table record (enabling record creation on your Relationships Graph) so that you can just add employees to a review. Conversely, you can have a portal on your employees screen with a pull-down of available reviews. This gives you the ability to see all reviews related to a given employee, or all employees related to a given review, simply by looking at the appropriate record.
4) Another way to implement the join is to use a list selection view, where you click on the correct record and script the insertion of the correct key fields. This is a little more sophisticated, but takes control away from the user (to help prevent errors).
To your other question, should you want to associate a customer with a particular review, you can do so directly on the review record, I should think. A single review would not be associated with more than one customer, so that could simply be a data element of the review record in a one-to-many relationship, and you can pass the information through to the join table if you wanted to via the related field.
Hope that makes sense. Feel free to ask further questions.
Thanks for the clear response. Let me see if I understand you correctly, you suggest putting a portal on the Reviews table (as well as the Employees table), that is directly linked to the join table? I've played with numerous portals and scenarios, but I'm not sure if I tried that one yet -- I think my main attempt has been to put, say, a Reviews table portal on the Employees Table...
So, when I'm logging a new review, I can create lookups to add new employees, and these would go into each line of the portal (which is linked to the join table)? Then this review SHOULD be linked to each of the employees added?
Yes. The join table sits between ("joins") the two parent tables. This is what converts the many-to-many relationship into (essentially) a pair of one-to-many relationships.
Now, to facilitate a more friendly user experience, you can add fields from the parent tables to the portals. For example, you can put the employee name in the portal on the reviews layout so the user can see which employees have been added. Just set the fields so they cannot be entered in Browse mode (to prevent people from editing the employees table from there) and it works a treat.
Cool. Thank you. I'll see if I can get this thing working.
Yeah, it doesn't take long to go off the rails here. For what it's worth, I did great with MS Access, but since we switched to Macs, well, not so much...
1. Should I put pop-up employee fields in the join table? As of right now, when I create a portal on the Reviews table using the Join table, the only thing I can put on there is the Personnel ID Match Field.
2. I accidentally created a portal on the Reviews table using the Personnel (Employees) table as it's "foundation." When I saved, the portal was just a blank box. That's been a recurring problem. Why is that -- is it because I'm using the wrong table for the portal?
1) The only fields you absolutely need in your join table are the two foreign keys. You're right; that means all you can put in there is the foreign key value. But that's okay, because you can reference fields in either parent table from there.
2) A portal will not have any objects in it if you don't tell FileMaker to put them there during the creation of the portal. That's also okay; you can always add them later.
See if the attached file (VERY quick and dirty) doesn't help.
Mike, you rock. Thank you so much. I'm going to analyze this to death until I figure out what I'm doing wrong.
One last quick question -- you say I have to tell Filemaker to put objects into the portal. Maybe something changed in the version I'm using (FMP 13), because in the original version of this, I never had empty portals. Maybe I've just forgotten that simple step, but it's driving me up a wall. Is it a box you have to check when you create it?
Thanks again. This helped immensely to spotlight where the real error was -- my employees didn't have any FRAKKING ID'S! Somehow I neglected to add those when I imported the employee list. Information in the portals wasn't "sticking," (among other problems) because there was no ID.
Thanks so much!.
When you create the portal, you'll get a dialog that shows you the fields in the table occurrence to which the portal points by default. You can select fields from that. If you don't, you'll get an empty portal.
And no, nothing changed between versions.