You might want to read this article: Tutorial: What are Table Occurrences?
Where FileMaker confuses things is that numerous dialogs and drop downs refer to "tables" when they are really listing Table Occurrences. Table Occurences are the "Boxes" in Manage | Database | Relationships while Data Source tables are listed in Manage | Database | Tables. Any given Data Source table can have many different Table Occurrence boxes in the Relationships graph.
When you select a "table" for a layout or select a "table" from which to add fields to your layout, you are really selecting a table occurence as the relationships that link to that table occurrence are what determines exactly what data gets displayed in your layout. Thus your original table occurrence, "Personnel" is unrelated, but the new table occurrence should show as related if you have linked it in your relationship graph.
Hmm I'm still a bit confused.
For instance if I make new occurences of the "Personnel Data" table and the "Rehearsal Report" table and link those occurences to the "Injuries" table, and then go to my layout that is made from the original table occurence of "Personal Data" and I try to add a portal that relates records from the "Injuries" table, I cannot choose the "Injuries" table from the drop down list because it is grayed out and listed under "unrelated tables". But if I try to hook up the "Injuries" table to the first occurence of the "Personnel Data" AND the first occurence of the "Rehearsal Report" table, I get a message that says "There cannot be more than one relational path between 2 tables in a graph. Another table occurrence must be added to the graph."
I'm gonna try to attach few screen shots.
Thank you for your help so far!
Think about what you just said: "if I make new occurences of the "Personnel Data" table and the "Rehearsal Report" table and link those occurences to the "Injuries" table..."
From what you've described, the Personnel Data table occurrence your Layout refers to has not been linked to Injuries, the new table occurrence for Personnel Data was linked to it. In order for FileMaker to see Injuries as a related table occurrence for your portal, you'll have to link it to the original Personnel Data table occurrence or you'll have to bring up layout setup... for your layout and select the new occurrence of Personnel Data in the Show Records from box.
Yes, I think I understand that the original occurrence of "Personnel Data" (the one that is used for my layout) needs to be linked to my "Injuries" table. I've done that now. The real problem comes when I ALSO try to link my "Injuries" table to the original occurrence of the "Rehearsal Reports" table. Then I get that error message saying I need to create a new occurrence of that table. When I do that it seems not to work. Here's a screen shot of how I currently have the tables related.
You have two options, create a second TO of Injuries or a Second TO of Rehearsal Reports. Which is correct depends on your layout and/or script design (if you are referring to related data via a script). And you might even need both.
If you want to include data from Rehearsal Reports in the Injuries portal, you'd link Injuries to a new TO or Rehearsal Reports.
If You want to place a portal to Injuries on a Rehearsal Reports layout, you'd create a new TO of Injuries. (You can use the button with two green plus signs in the bottom left corner of your relationship graph to make a new TO of any existing TO.)
Calculation fields depend on the specified "context" you select in the specify calculation dialog.
If that still doesn't help you out, describe what you are trying to do in more detail so that we can help you figure out how to set up your relationships to support it.
I'm very close to getting this to work! So far everything works as it should except for the "Reh_num" field which holds a number which specifies what rehearsal number it is.
"Reh_Num" is a field in the "Rehearsal Reports" table. The "Rehearsal Reports" table is connected to the second table occurrence of "Injuries" called "Injuries 2" by the "date" and "Reh_Num" fields. However, I need the "Reh_Num" field to show up on the "Personnel Data" layout through a portal with shows records from the original "Injuries" table occurrence. The "Personnel Data" table is linked to the "Injuries" table by the fields "Full_Name".
I need the portal on the layout that is based off the "Personnel Data", that shows records from the "Injuries" table to show the "Reh_Num" field from the "Rehearsal Reports" table. I believe I need to link the field "Reh_Num" from the "Injuries" table either to the "Reh_Num" field in the "Injuries 2" TO or to the "Rehearsal Reports" TO. However in either case, when I do this, I get that error again saying I need to add a table occurrence.
Any thoughts? I'm attaching another screen shot so you can see how the tables are related.
Since what you need is the Rehearsal Number, not the Rehearsal Record, all you need is to include the Injuries::Reh_Number field in your portal to Injuries. (Injuries::Reh_Number will have the same value as the matching Rehearsals::Rehearsal Number.)
Keep in mind that Injuries and Injuries 2 both refer to the same table of records so data entered into a Portal to Injuries 2 will be visible in a Portal to Injuries and vice versa.
If you find you need other data only found in Rehearsals, you'd need to add an additional TO of Rehearsals to link to Injuries so that you can refer to fields from this new TO.
Solved! Thank you!