9 Replies Latest reply on Dec 22, 2010 7:21 PM by manager12

    Table Occurrences?

    manager12

      Title

      Table Occurrences?

      Post

      I have a table called "Personnel Data" that holds peoples names, contact info, etc. I have another field called "Rehearsal Reports" which holds information about rehearsals of a play. I have a table in the middle which relates these tables, called "Late/Absent". The "Late/Absent" table uses the field "full_name" as a key field to relate it to the "Personnel Data" table. The "Late/Absent" table also uses its "late_date" field and its "rehersal_number" field as key fields to relate to the "Rehearsal Report" table which has fields of the same name. These relationships all work as planned and I have some portals set up in different layouts displaying and editing the data for different reasons.

      Now comes the problem. I've added an "Injuries" table which needs to connect to both the "Personnel Data" table (key field should be "full_name") as well as the "Rehearsal Reports" table (key fields should also be "date" and "rehearsal_number"). I'm able to connect two fields from the "Injuries" table to the "Rehearsal Reports" table but when I go to connect one field of the "Injuries" table to the "Personnel Data" table I get a message that "There cannot be more than one relational path between 2 tables in a graph. Another table occurrence must be added to the graph."

      Ok, so I added another table occurrence of the "Personnel Data" table and tried to connect the "Injuries" table to "Personnel Data" Table Occurrence I just created. Now I think it is saying that the "Personnel Data" table is an unrelated table to the "Injuries" table.

      Does a new occurrence of an already exisiting table automatically relate to the original table? Or do I need to relate them somehow? Is there another way I should be going about relating the "Injuries" table to the "Personnel Data" and "Rehearsal Report" tables rather then making new table occurrences?

        • 1. Re: Table Occurrences?
          philmodjunk

          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.

          • 2. Re: Table Occurrences?
            manager12

            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!

            • 3. Re: Table Occurrences?
              philmodjunk

              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.

              • 4. Re: Table Occurrences?
                manager12

                Portal SetupRelationship Graph

                • 5. Re: Table Occurrences?
                  manager12

                  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.

                  I guess I'm not sure at this point how to make "Injuries" relate to "Rehearsal Reports".

                  • 6. Re: Table Occurrences?
                    philmodjunk

                    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.

                    • 7. Re: Table Occurrences?
                      manager12

                      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.

                      • 8. Re: Table Occurrences?
                        philmodjunk

                        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.

                        • 9. Re: Table Occurrences?
                          manager12

                          Solved! Thank you!