6 Replies Latest reply on Feb 3, 2012 8:24 AM by mgores

    Complicated related records situation

      Title

      Complicated related records situation

      Post

      I am in the process of designing a database that organizes people and assignments they may have on a project.

      The data on people is stored in an external fp7 file.
      Assignments are stored in a table that have a name, date, start and end time, and a description.
      There is a third table that is called Schedules that stores a person's ID and an Assignment's ID, sort of a link table if you will. This allows for any number of assignments to be added to a person's list of assignments and any number of people to be added to an assignment.

      There is a laout that accesses the "People" data that contains a portal of related "Schedule" records. Theoretically this should allow for new assignments to be added. I've used this method before in a similar fashion on enrolling students in to classes via a link table. Since the linking table only stores ID numbers, the portal actually only shows related fields directly from the "Assignments" table. I've already checked the box under the relationships between the link table and both assignments and people that new "schedule" records can be created through the relationships. However when I go to add a new assignment to a person, Filemaker gives me an error that the action is not allowed and that what I am trying to modify is not allowed to be modified.

      I have been able to go in directly to the link table, enter IDs for the person and the assignment. After doing this the assignments appear in the portal on the "People" layout. I'm wondering if I am missing some step I forgot about. I know that this method works because I've used it before. Any ideas?

      Please let me know if I need to provide any details, I know that this is very wordy. Appologies.

       

      God Bless,
      Chris

        • 1. Re: Complicated related records situation
          philmodjunk

          What you have is a classice many to many relationship and we call that "link" table a "join" table...

          People----<schedules>---Assignments ( ---< means one to many )

          When you are on a people layout with a portal to schedules, creating a new assignment record requires creating both a new record in schedules and a new record in assignments. This can't work via direct data entry in the portal even if you enable "allow creation..." in both relationships because you have to create the new record in Assignments first in order to get the serial ID to add to the new record in Schedules.

          I usually put a "new assignments" button just above the portal and set it to run this script:

          Set Variable [$PeopleID ; value: People::PeopleID ]
          Go to Layout [Assignments ]
          New record/request
          Set Variable [$AssignmentID ; value: Assignments::AssignmentID ]
          Go to Layout [Schedules]
          New Record/Request
          Set Field [Schedules::AsignmentID ; $assignmentID]
          Set Field [Schedules::PeopleID ; $PeopleId]
          Go to Layout [original layout]

          Examples of this script and other tricks for working with many to many relationships can be found here: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          This demo file also demonstrates methods for keeping the user from linking to the same related record twice for the same current record.

          • 2. Re: Complicated related records situation
            mgores

            You can also use another TO of assignments with a cartesian relationship to people to provide a list of assignments.  I usually do that, then make the assigment name a button that creates the record in the join table using that assignmentID and that peopleID.  If a new assignment is needed a separate button can pop up a new window in the assignment layout to allow the new assignment to be entered, then return the people layout so it can be selected from the list.

            • 3. Re: Complicated related records situation
              philmodjunk

              @Mark, when you get a chance, take a look at the demo file. It uses that method and adds a "check box" type appearance to it so that clicking an item in the portal shows selectes/deselects the item without needing a portal to the join table to show what is selected. Wink

              • 4. Re: Complicated related records situation

                @PhilModJunk What I don't understand is that I haven't had to do that in the past. I had another database of students and classes. The Enrollments join table stored an ID, Student ID and Class ID but I used no extra scripting. All I had to do was add a portal to the Student layout with Enrollments as the source for the portal and display a related field from the class. Similar for on the Classes layout. I can send you a blank version of the two databases if you'd like to see. Perhaps I'm missing something between the two.

                Thank you for your example solution. It has some great ideas. I just don't see the "Add" script as necessary since I've gotten it to work properly before without it.

                • 5. Re: Complicated related records situation
                   @PhilModJunk What I don't understand is that I haven't had to do that in the past. I had another database of students and classes. The Enrollments join table stored an ID, Student ID and Class ID but I used no extra scripting. All I had to do was add a portal to the Student layout with Enrollments as the source for the portal and display a related field from the class. Similar for on the Classes layout. I can send you a blank version of the two databases if you'd like to see. Perhaps I'm missing something between the two. Thank you for your example solution. It has some great ideas. I just don't see the "Add" script as necessary since I've gotten it to work properly before without it.
                  • 6. Re: Complicated related records situation
                    mgores

                    @ Phil- yes I see how that works.  There are times though where, for me at least, I like having the two portals and seeing the list of possible choices dimish and the selected choices grows.  Smile