8 Replies Latest reply on Feb 17, 2016 1:02 PM by rmittelman

    Multiple Tables on 1 Layout?

    rmittelman

      I have a database I'm trying to update from Microsoft Access.  It is for a Student financial aid application.  There is a table for students, and a related table for Student Profiles.  A student profile is information regarding the course the student is taking, the fees, expenses, etc.  The tables are linked by student ID. The layout pictured below is based on the Students table, and there is a self-join table to Students to power the student portal in upper left.  When I click an item in the portal, it does a GTRR and displays the student info on this layout.  This is working just fine.  My aim is to also have a profiles portal (see upper middle area).  If the student has more than 1 course of study, they will all show up in this portal.

       

      If the user clicks on a profile record, the selected profile information should show on the bottom area of the layout.  In the sample below, I only have the first few fields included.

       

      The problem is that using a self-join table and GTRR will not work because the layout is based on students, not student profiles.  It returns an error that the layout doesn't have fields for that table.  If I change the layout to be based on student profiles instead, the right-hand portal works but then the left-hand students portal returns that error.  So apparently you can have fields from another table, but can't use GTRR for this.

       

      So I'm trying to find a work-around.  I'm not averse to basing the layout on a different table, and using SQL for the portals.  I do need to be able to pick a student from the left portal, and show a list of their programs (profiles) on the right portal, then choose one of those to display the profile information on the layout itself.

       

      Can somebody give a suggestion on how to accomplish this?  Thanks...

       

      Students Layout.png

        • 1. Re: Multiple Tables on 1 Layout?
          macwombat

          Hi.  You can do it like this.

          1.  Create a global field in your student table to store the StudentProfileID.

          2.  Create a relationship to a new Table Occurrence of the StudentProfile table based on the global field = StudentProfileID.

          3.  Set a script trigger on the Student Profile portal to set the global field with the StudentProfileID of the portal row selected.

          4.  Create your portal at the bottom of the layout based on the new Table Occurrence.

           

          Sample file attached.  HTH.  Chris

          • 2. Re: Multiple Tables on 1 Layout?
            rmittelman

            Hi Chris,

             

            Thanks for the quick answer.  I'll need to check your sample app, but I'm thinking I didn't explain my challenge properly.  There are many students, each with their own unique ID.  Each student will have 1 or more profiles in the profiles table.  Note each profile is a course of study, so if the student finishes one and starts another, there are 2 profiles.  So I can't store a profile ID in the student table, because there may be multiples.  And I certainly can't make it global.  Each profile record will have its own unique ID, plus have the student ID as a foreign key.  What I'm trying to figure out is how I can maintain a student record and its fields on the same layout as the profile record and its fields.  I have to base the layout on either student table or profiles table, right?  If profiles table, then the profiles portal can be to a self-join profiles TO, and use GTRR to load the proper profile to the layout.  But, I still need to pick the student first, so I can't use GTRR.  I need some other method of assigning the proper "current" student when I choose him/her in that portal.  It's almost like I need a layout embedded in another layout in order to properly assign the current record in each table.

            • 3. Re: Multiple Tables on 1 Layout?
              macwombat

              Hi.  Have a look at the sample file, but I think you'll find that it does what you want.

              1.  You are not storing the profileID in the student record permanently.  The script trigger sets the global field for the purposes of displaying the correct information in the bottom portal.  Click on a different course of study and it's data will be displayed in the bottom portal.

              2.  I've attached an updated sample file which runs a script when you click a new student to clear the global so that it doesn't display the previously selected student's course in the bottom portal.

              3.  Stick with your layout based on student and use 2 different portals - one to display all the course for the student, and the other portal to display the currently selected course.

              Chris

              • 4. Re: Multiple Tables on 1 Layout?
                rmittelman

                Thanks Chris,

                 

                It took me a bit to figure this out.  I've been using OnObjectEnter portal trigger to run my scripts, which works whenever you click on another portal row.  You're using the portal fields grouped together and then using a button action instead of a trigger, right?  Is there a benefit to this method?

                 

                You're using the bottom portal to the student profiles 2 TO, because since the layout is based on students, you couldn't load the profile fields right on the same layout, right?

                 

                I think this may work, but:

                 

                Only the student name and address info needs to be edited for the students table, but there will be many fields that need to be edited for the profile.  There's only a few fields on the tab control in the picture above, but eventually there will be many more on the various tabs.  Using your technique, I think I need to reverse the orientation, basing the layout on student profiles and show the student info in a portal instead.  Would you agree?

                 

                Thanks again...

                • 5. Re: Multiple Tables on 1 Layout?
                  macwombat

                  Hi

                  It took me a bit to figure this out.  I've been using OnObjectEnter portal trigger to run my scripts, which works whenever you click on another portal row.  You're using the portal fields grouped together and then using a button action instead of a trigger, right?  Is there a benefit to this method?

                  They are different.  You need to choose what is the best for your needs.  Read the FileMaker Help files to see the differences.  I just used the button because it was quick and easy for the sample file.

                  You're using the bottom portal to the student profiles 2 TO, because since the layout is based on students, you couldn't load the profile fields right on the same layout, right?

                  Correct.  That seemed to be the functionality your original post was asking for.

                  Only the student name and address info needs to be edited for the students table, but there will be many fields that need to be edited for the profile.  There's only a few fields on the tab control in the picture above, but eventually there will be many more on the various tabs.  Using your technique, I think I need to reverse the orientation, basing the layout on student profiles and show the student info in a portal instead.  Would you agree?

                  If all the information on your tabs are going to be from the selected student profile then it probably makes more sense to do as you have suggested and base the layout on the student profile table.  Its all a learning process and often as you work through problems you determine that your operational needs require a different structure to where you started out.

                   

                  Enjoy!  Chris

                  • 6. Re: Multiple Tables on 1 Layout?
                    rmittelman

                    Thanks Chris, you've been so helpful.  I've had a few challenges on this project, because I'm still thinking like an Access developer, not a FileMaker developer.

                     

                    For example, my database relationships are setup as school being the parent, students being the children, and profiles being the grandchildren.  Logically this is correct, but if my layout needs to be centric to the profiles for reasons stated previously, this forces me to have multiple TO's just to list students on my layout portal.  I think if I just have a TO for students with a cartesian join between it and profiles, I can do my filtering for correct school in the portal.  I'm already keeping global fields for active school, student and profile.

                     

                    Regarding trigger vs. buttons, you're right, there are pros and cons either way.  Using a portal trigger makes it easier to select a row by script.  For example, if I choose a student, the script could select the first profile easily by just including a step to go to the first row.  The downside is when I need to iterate through the portal rows in order to bring the active one into focus, I need to surround the entire trigger script with an If test so I can set a variable to ignore the trigger.

                    • 7. Re: Multiple Tables on 1 Layout?
                      BruceRobertson

                      rmittelman wrote:

                       

                      For example, my database relationships are setup as school being the parent, students being the children, and profiles being the grandchildren.  Logically this is correct, but if my layout needs to be centric to the profiles for reasons stated previously, this forces me to have multiple TO's just to list students on my layout portal.  I think if I just have a TO for students with a cartesian join between it and profiles, I can do my filtering for correct school in the portal.  I'm already keeping global fields for active school, student and profile.

                      What's wrong with multiple TOs? Nothing.

                      Note that by designing for a cartesian plus filter calcs, you just want to be sure to tell your client not to be too successful.

                      Portal filter calculations are quite handy when the basic relationship provides a small related record count. But they are a severe performance problem for large related record sets. If your system never exceeds, say,  200 students, your plan is survivable.

                      • 8. Re: Multiple Tables on 1 Layout?
                        rmittelman

                        Thanks for the input, Bruce.  Given multiple schools, it is very likely the number of students will exceed 200.  Given that students can have multiple courses of study, it is almost certain the number of profile records will exceed that.  That's funny... tell them not to be too successful.  What is needed here is a better understanding of how FM relationships work, and can be levereged to make my application more efficient.

                         

                        The school is chosen on the first "main" form of the application, from a portal to the schools table.  A global variable is set to the School ID.  Originally, I based my Students and Profiles layout on the Students table, with the student selection portal on the top left based on a self-join Students TO.  The Profiles portal on top right was using the relationship between Students and Profiles.  I was using GTRR when choosing the student.  As you can see, there are student-related fields (name, address, etc.) on the layout, but there will be many more profile-related fields on the tab pages.  Making the layout student-table-based won't work for the profile records, because GTRR fails.  Using Chris' suggestion would work if I instead make the layout profile-table-based, and put the student demographic info in its own 1-record portal.  It also requires a cartesian relationship from profiles table to students TO so I can still get the portal to select the student.

                         

                        I guess I need to think through how to make my relationships work better, so I don't also need to filter my portals...