Here's the relationships window:
Your relationship shown lacks "crows feet". Either WorkList::IDKey uniquely identifies each record in Work List or Contributors::IDKey uniquely identifies each contributor. But both are not possible for the relationship shown as what is shown is a one to one relationship.
It looks to me like you need a join table since a given contributor can work on any number of "Works" and any given Work can have multiple contributors.
That would suggest relationships similar to this:
Works::__pkWorkID = Contributor_Work::_fkWorkID
Contributors::__pkContributorID = Contributor_Work::_fkContributorID
The hours worked by any given contributor would be recorded (or computed from a fourth related table) in Contributor_Work.
I did a bit of reading yesterday and made your suggested join table work. My problem now is that, by moving some of the criteria to the join table (percentages, for example) the portal I've created shows fields from Contributor_Work, but some of those fields, such as the contributor name, must come from the Contributors table.
This is a problem because when I tell the join table to automatically create records via relationship, (I specify this in the relationship between Works and Contributor_Work), I can create records based on non-essential criteria such as percentages, but I cannot choose a Name from from the Contributors table because that is not the relationship I've enabled to create records in. But that's backwards.
There is this paradox: I want to create records, through a portal in my parent table (Works), that live in the join table, but the main criterion I want to use to create these records is in the wrong table. If I tell the Contributors table to automatically create records, I will get endless copies of my contributors, which is wrong—they should have one record associated with them.
How do I get around this problem? At the moment, I am forced to create a layout that looks at fields in the join table, create records in there, then watch them show up in the parent table's portal. This is inelegant, to say the least.
You can select contributors from a dropdown list in your join table.
I made you a little example file:
Note that in this file I'm using a trick of two fields on top of each other to select a Contributor. I also created a second layout that uses a merge field to show the name. It's a slightly different technique.
It is however so that you cannot create contributors from the Works layout using this method. Because the contributors table is one step to far away. Your Works table is related to your Join table. You can create records there. But from there you can't create new contributors.
You can do this however if you use a sneaky trick. Namely, if you relate a second Table Occurrence (I don't know if you have learned about Table Occurrences yet or not) of your contributors table to your Works table.
For instance by creating a "Temporary Contributor" field in your works table where you can either select an existing or create a new contributor. And then you use a button that runs a script that puts that temp info in your join table.
An example of this can be seen here:
Here's a demo file on Many to Many Relationships that you may find helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
It demo several different approaches and tricks for working with a many to many relationship.
And here's a file that demonstrates a an approach for selecting records by name that allows you to use auto-complete and yet still link records by ID number:
FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
Thanks to you both. I'll respond ASAP.
Thanks for that. I like being able to select the contributors from a list and creating their records in the join table via the main "work" table, which is pretty much what I was trying to acheive.
Also, it's not a problem to have to create contributors in their own table, but you nailed the data entry that I need.
Phil—those are fantastic. Will pour over them this evening.
Okay, I'm back and have implemented everything and it is working brilliantly. Thanks to both of you for your help and handy files. I have one question, as I've run into a reporting problem: I ended up making two of these many-to-many relationships (using a join table, of course) because I need to make a print layout that shows, in my case:
1. a subsummary page that shows each work, then each contributor underneath — this is fine, no problems.
2. I also need to show a second set of related contributors (this time "Publishers"). It is set up exactly the same way as "contributors"
I can display related records in my subsummary if the layout is set to show records from "Contributor_Work". The problem, however, is that I cannot display related records from "Publisher_Work" (my new join table for showing publisher info) at the same time. It seems I can only show related records correctly from one table at a time, and I understand why.
Any ideas how to combat this? Perhaps there's a work-around.
It would take a bit of creativity, but both Contributor_Work and Publisher_Work could be two table occurrences with the same data source table. This would require an additional field used to distinguish between the two and you may want to use filtered portals or more complex relationships for portals to this join table and to automate data entry into this added field, but such could be made to work and then you could have both publishers and Contributors as part of the same summary report.
The alternative is to use a portal to one of the two join tables--which can work, but also has some limitations that can make it hard to get the report to function the way you need it to.
Good to know it's possible. I could, if need be, put contributors and publishers in the same table and filter the results onto the page, perhaps? It's fine if they share records in the same table, but they need to be displayed in two different positions on the page and if filtering portals helps here, then maybe that's a way to go. I shall investigate.
Using sorting, you can group the two types of records and with a sub summary layout part, you can set up sub headings for each such group.
You mean that if the field for the Contributor name and Publisher name are on different subsummary parts, all related records for each will display?
You would have a field that identifies to which group each record belongs. Say it's a text field with either the text "Contributor" or "Publisher".
If you added a sub summary part "when sorted by" this field, your records would sort into a group of contributor records and a group of publisher records. The sub summary part then serves as a "sub header" where you can put this one field if you wish so that your report looks like this: