11 Replies Latest reply on Oct 19, 2011 3:39 PM by philmodjunk

    Relationships

    TonnyOn

      Title

      Relationships

      Post

      I have encounter a problem when I tried to link a field with other field in different table, it pops up a message saying "There cannot be more than one relationship path between two table in the graph. Another occurrence of one the tables must be added to the graph" and giving me an option to make a copy of the table with the same field. How do I fixed this problem without having to duplicate the same table, I just want to link them.

        • 1. Re: Relationships
          philmodjunk

          This option does not duplicate the table. It creates a new occurrence of the same table. Think of the boxes in Manage | Database | Relationships as labels that refer to your actual tables. Filemaker is just trying to see which of the two tables will need a new "label" so that you can get what you need without a "cycle" appearing in the relationship graph.

          Here's a tutorial on Table Occurrences you may find useful:  Tutorial: What are Table Occurrences?

          • 2. Re: Relationships
            TonnyOn

            The occurrence table makes a new occurrence of the same table but what I want is to link the field so whatever record I input to the field will add into the other field and display in the layout 

            A picture is display below to show what I want.

            • 3. Re: Relationships
              bumper

              What you are seeing is called a circular reference. FileMaker isn't able to decide which is what and if it attempted to it would end up in a continuous loop. To correct this select the Hardware List TO, then click on the two plus signs button. A new TO will be created called Hardware List 2 (note you can rename it anything you want except Hardware List) then create your relationship from Teacher Details to the new Hardware List 2.

              • 4. Re: Relationships
                mgores

                Another way of explaining it.  You can have 2 occurrences (Hardware list Student and Hardware list Teacher) but only 1 table (Hardware list).

                The table occurrence is just a reference for the relationship so that filemaker knows how to get to Hardware list from the Student or Teacher Details table (or from the Detail tables to the Hardware list).  Records created through the Student Details-Hardware List Student relationship and records created through the Teacher Details - Hardware List Teachers  both end up in the Hardware List table.

                • 5. Re: Relationships
                  philmodjunk

                  Here's the step by step.

                  Assuming you have these two relationships to different occurrences of HardwareList:

                  StudentDetails::ComputerNumber = HardwareList::ComputerNumber

                  TeacherDetails::ComputerNumber =HardwareList 2::ComputerNumber

                  On your StudentDetails layout, you place fields and portals from HardwarList. On your TeacherDetails, you place fields and portals from HardwareList 2.

                  And another option for you here is that the Student Details and Teacher Details tables look to be nearly identical in structure. You might consider using one table for both with an added field to identify each record as that of either a student or a teacher.

                  • 6. Re: Relationships
                    TonnyOn

                    So if I add a record on the teacher details, it will add on the hardware Lists 2 (table occurrence) and if add a record on the student details it will add on the hardware list 1 but hardware list 1 and 2 are the same and will have both the record from student and teachers

                    Is my explanation right?

                    Then how do I display these record from student and teachers on one field on the hardware list layout

                    • 7. Re: Relationships
                      philmodjunk

                      Your hardware list layout can specify either HardwareList or HardwareList 2 and you'll have identical results. Performing a find on this layout, sorting records, etc. affect all the records in the table, both those linked to teacher records and those linked to student records. If you need to include fileds from the teacher and student detail tables, then you will have a problem as these are two different detail tables. This isssue is one of the reasons why I suggested you use the same table for both teacher and student detail records.

                      If you keep them separate, you have several options. You can place a field from each table directly on top of each other. Since only one of the two tables will contain a related record, one of the two fields will be empty. As long as this is true and both fields are transparent, this works, but the layout can be difficult to maintain due to the stacked fields.

                      You can define a calculation field in the HardwareList table such as: Student Details::Name & Teacher Details::Name. This may require setting up third occurrence of Hardware list linked to two more occurrences of the detail tables so that you can specify one "context" in the context drop down that correctly links to both of these tables in order for this calculation to correctly evaluate.

                      You can put the fields from the detail tables in pairs on your layout, one located above the other (not stacked) and specify that the fields on the layout slide up, resize enclosing part. This produces an easier layout to work with than one with stacked fields, but only looks correct when you preview, print or save as PDF as fields do not slide in browse mode.

                      • 8. Re: Relationships
                        TonnyOn

                        Which method do you preferred

                        Could you show me each step on how to do the calculation of the field "Student Details::Name & Teacher Details::Name" with the third occurrence table

                        And the transparent field on which you explained about "stacked field"

                        • 9. Re: Relationships
                          philmodjunk

                          Neither of those were my preferred method. My preferred method would be to use a single table for both Student Details and Teacher Details.

                          The Calculation:

                          Define these relationships with spearate occurrences of each table:

                          Student DetailsCalc::ComputerNumber = Hardware ListCalc::ComputerNumber
                          Teacher DetailsCalc::ComputerNumber = Hardware ListCalc:ComputerNumber

                          Now this calculation could combine student and teacher names.

                          On the Fields tab, select Hardware List and add, cName, defined with this calculation:

                          In the "context" drop down at the top of the specify calculation dialog, select "Hardware ListCalc".

                          Student DetailsCalc::Name & Teacher DetailsCalc::Name

                          Stacked fields:

                          On your layout (based on Hardware ListCalc), place Teacher Details::Name directly on top of Student Details::Name. Make sure that Teacher Details::Name has a transparent file pattern so that it doesn't hide the field beneath it.

                          • 10. Re: Relationships
                            TonnyOn

                            "You can put the fields from the detail tables in pairs on your layout, one located above the other (not stacked) and specify that the fields on the layout slide up, resize enclosing part. This produces an easier layout to work with than one with stacked fields, but only looks correct when you preview, print or save as PDF as fields do not slide in browse mode."

                            How do I perform this method 

                            Also can you explain stacked fields I am not sure how to make a transparent file pattern

                            • 11. Re: Relationships
                              philmodjunk

                              Place your two fields like this on your layout:

                              [Student DetailsCalc::Name]
                              [Teacher DetailsCalc::Name]

                              In the Sliding and Visibility section of the Inspector's Position tab specify "Slide up" and also "Resize enclosing part" for both of these fields and all layout objects located next to or below them.

                              Key facts about sliding layout objects:

                              1. It's only visible in preview mode and when you print/save as PDF...
                              2. All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
                              3. Objects in headers and footers will not slide.
                              4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
                              5. Consistent side borders are difficult to achieve with sliding fields.