3 Replies Latest reply on Aug 15, 2011 12:00 PM by philmodjunk

    Using portal to create new records via join table



      Using portal to create new records via join table


      Hello everyone,


      I am working on a database tracking college athletes (using FMP 11).  Since each athlete can have many sports and each sport will have many athletes, I created a join table called "activity" to link my "athlete" table to the "sport" table. 

      "athlete" contains their basic demographic info and is related to "activity", which has the fields of: activityID, athleteID, sport_name, and academic_year.  Activity is then linked to "sport" whose only field is sport name.

      First question: Am I correct in the design of the relationships here?

      My issue comes on my data entry layout (on the activity tab) that I have designed for the athletes. 

      Using a portal to the activity table, I would like each athlete to select which sport(s) they participate in, thinking that there will be multi-sport athletes. When I test this portal, it looks like I can create multiple records through the portal, as I can choose a sport in multiple lines in browse mode.

      Just to double check, I created a layout using all the fields from the "activity" table to make sure that multiple records were being created. However, only the first sport entered through the portal shows up as a created record.  In other words, if I enter 3 separate sports in the portal, my test layout will say only 1 record is created.

      I also tried using two separate portals, this time basing the "sport_name" field off of the "sport" table and leaving "academic_year" with the "activity" table.  Not sure which one is correct, I thought this would fix it, but it didn't work out either.

      Where am I going wrong here?  I have "allow creation of records through this relationship" turned on in all the appropriate places.  Maybe I am missing something simple, but I would love a pointer if you can help!  Screenshots of the portal and then the list showing creation of only 1 record.

      Thank you!


        • 1. Re: Using portal to create new records via join table

          Not sure exactly where you've gone wrong here, so let's map out the typical way to set this up and you can compare your system to that to spot any differences.

          You should have these relationships and please note the change here for linking to records in the sport table:


          Athlete::AthleteID = Activity::AthleteID (enable "allow creation of records via this relationship for Activity)
          Sport::SportID = Activity::SportID

          With this set up you can place a portal to Activity on your Athlete layout.

          Inside this portal, you can place the Activity::SportID field and format it with a value list that displays SportID from Sport as field 1 and Sport_name as field 2. When you select a value from this value list on the bottom blank row of the portal, a new record is created in activity linked to Athelete by AthleteID. The SportID you select then links it to a record in the sport table. You can place any fields from Sport in this same portal to display additional information about the selected sport.

          • 2. Re: Using portal to create new records via join table

            Thank you PhilModJunk,

            I reformatted the portal and took a second look at my test view and I think the problem may have been there.

            I formatted the value list to use sportID and sport_name.  When a sport is selected, only the sportID shows up in the portal.  Is there a way to have the sport name show instead?  I have checked the "show second field only" box so that when selecting a sport, the athletes only see the sport names, but it still displays the ID after they have made their selection.

            Thanks again for you help!


            • 3. Re: Using portal to create new records via join table

              With the "show second fieild only" option, you can use the Pop Up menu option and then the name will show instead of the number after you exit the field. You can keep the drop down list, but put the name field from the related field next to it to display the name when you exit. You can resize the ID field to be just wide enough to display the arrow and then put it next to the name field to look like one field or you can even place the name field (with non transparent fill color) on top of the drop down field, but with behavior options selected in the inspector to deny access to this field while in browse mode. When you click on this field, the drop down list behind it will deploy.