11 Replies Latest reply on Mar 31, 2014 6:59 AM by PatrickLollis

    Nested Loops Question

    PatrickLollis

      Title

      Nested Loops Question

      Post

           I am a music teacher with 500 students, and I'm creating a database to track 262 music standards. It's coming along well, and I need help with a nested loop situation. If something about the structure needs to change to make this process easier, I'm open to suggestions.

           I've attached a picture of the tables and relationships.

           Each student has a profile in Student Data. Each music standard has a profile in Music Standards. Each note in Music Notes is related to both a student and a standard.

           I wish to print a report for each student every 9 weeks that lists each standard and either the date of mastery, or the highest score that has been achieved so far (if not yet mastered).

           I created the Music Achievement Report table to hold one record per student per standard plus the mastery_date or max_score info. What I need to do now is populate these fields, and I think it's going to require a nested loop, something like this:

           Loop 1: Get the list of student IDs from Student Data - Start with the first student ID

                Loop 2: Get the list of standards from Music Standards.-Start with the first standard

                (thiscould just be math instead of a search)

                     Loop 3: Find all records in Music Notes with the current student and standard.

                          If mastery = "Yes", write the date to the related mastery_date field in Music Achievement Report.

                     Next record

                     If none of the mastery fields were "Yes," write max (score) from the set into max_score in Music Achievement Report

                Next Standard

           Next Student

           I'm not sure if this is doable, and if it's not, what to do instead. Thanks ahead of time for thinking about this and any responses. :-)

           Patrick R. Lollis, Music Teacher, Grapevine, TX

            

      lollis_db_relationships.png

        • 1. Re: Nested Loops Question
          philmodjunk

               I don't think you need to use this method to get your report. I see two possible approaches that use the Student Data, Music Notes and Music Standards table occurrences.

               Which is best depends on your answer to this question: Do you want to list all Music Standards (or All music standards appropriate to that student) even if there is no Music Notes record linking it to the student? (This may be a moot question if you always have at least one note question linking the student to all relevant records in Music standards).

               If you only want records for which you have recorded at least one record in Notes or you always have at least one record in Notes for every standard at the time that you need this report, you can set up a report layout based on the Music Notes table occurrence.

               If you want a report for a single student, but which lists all relevant standards whether or not there has been a note record recorded, you can set up such a report with filtered portals to Notes using a layout based on the Music Standards layout.

               Either way, there is no need for a looping script to generate more records in a new table just for reporting purposes.

          • 2. Re: Nested Loops Question
            PatrickLollis

                 PhilModJunk thanks so much for the quick and insightful reply. :-)

                 "If you want a report for a single student, but which lists all relevant standards whether or not there has been a note record recorded, you can set up such a report with filtered portals to Notes using a layout based on the Music Standards layout."

                 This is the one I want to do, but I've never used a portal. Here is what the current report (from Excel) look like.

                 I created print categories, a print order field, and some header records to recreate this report in a three column FP report with a header for each category. I just can't figure out how to populate the date/max score field. What would the portal solution look like? Would it give me that single data point for each student that will fit on a 3-4 page report? Could I script such a solution to loop through all the students?

            • 3. Re: Nested Loops Question
              philmodjunk

                   Creating  databases in FileMaker without using portals is like rowing a boat with only one oar. You can get there, but not easily. Unless it's a demo file of extremely simple design and narrow purpose, I can't think of a single database that I've created that did not have at least one (and usually many) portals.

                   So I strongly recommend that you learn how to use them as they are a key layout design tool for working with multiple related records. You might start by looking up "Portal" in FileMaker Help or any training materials to which you have access.

                   What your screen shot shows is a typical result from using a spreadsheet. It's possible to replicate it in FileMaker, but it will take a lot of work that could be avoided if you are willing to produce the same data in a different format. Even if you ultimately go with the format shown, the following info remains a good starting point.

                   From your relationship graph, it would appear that you have one record for each music standard. (and this is what you should have.) I do not know if all records for standards apply to all students of if you have different standards records for different groups of students such as students with different ages or grade levels. If only some apply to a given student, the first step is to perform a find or use Go to Related Records to pull up the correct sub set of records in Music Standards on a layout based on that table occurrence.

                   But each record in Music Standards might link to more than one record in Music Notes. A simple portal to Music Notes should list all Music Notes for that Music Standard for all students for which you've made such a record in Music Notes. I suggest that you create a blank layout based on Music standards and try creating such a portal. If you can get that to work and see all Music Notes records for that student, you are part way to where we want to go.

                   From there, we can set up a portal filter to limit the records shown to just one Music Notes record. This would be the music notes record for a selected student and would be either the most recent notes record for that standard or the notes record with the best score recorded or the "mastery" record for that student for that standard.

                   The next step would be to take that portal that you created as a test and do the following:

                   Enter layout mode.

                   Select the portal (not a field in the portal) by clicking the lower part of the portal and then select Portal Setup... from the Format Menu. (You can also just double click the lower part of the portal.) This re-opens the portal set up dialog that you saw when you first added a portal to your layout. Select the check box for a portal filter and enter this expression:

                   Music Notes::student_id = 5 // use a number for which you know that a record exists in Student Data and that has at least one Music Notes record for the current standard.

                   Return to Browse Mode and you should now see only Music Notes records for that one student that are linked to the current Music Standards record. If you click to a different record, you should see different records in the portal for the now current standards record.

                   Let me know if you can get this to work. The next step is to reduce this down to either a "Mastery" entry or a max score. And to start the ball rolling on that, what do you enter in Music Notes to indicate Mastery of a given standard?

              • 4. Re: Nested Loops Question
                PatrickLollis

                     Thanks to your help, this is going great!

                     I created the report based on the Music Standards table. I was able to us a custom sort field, print columns, and conditional formatting to get this report massaged up to a very close cousin to my original document. I'm VERY please with that.

                     Now, on to the portal. I decided to tackle mastery date first. For starters, I created a button on the individual student form layout. The button runs a script that starts with Set Variable [$$CurrentStudentID; Value:Student Data::student_id], then opens the Music Achievement Report layout. I created a portal to the Music Notes table. I filtered the records by Music Notes::student_id = $$CurrentStudentID and Music Notes::mastery = "Yes". In theory, there should never be more than one record to record mastery of the same skill, but just in case, I sorted the records by date. The only field I put in the portal was the date field, and BAM...perfection (see screen shot).

                     However...when I navigate back to the Student Data List, choose a different student, and run the report again, it still shows the results from the first student. I wondered if I was resetting the variable correctly, so I put in a custom dialogue box to check, and the variable is changing perfectly. For some reason, the data in the portals sticks with the first student and won't change unless I quit the application and relaunch the database.

                     I'm sure I'm missing something really small, but I can't figure out what it might be.

                     One other annoying detail...I've added a portal to the Student Data table in the header and added the first_space_last field to put the student name at the top of the report. It displays perfectly in browse mode, but disappears when I go to preview mode. Like the other data on the report, it also sticks with the first student's name.

                     Thanks again for your help. I appreciate it so very much. :-)

                • 5. Re: Nested Loops Question
                  philmodjunk

                       You've made great progress with portals!

                       Refresh Window [Flush Cached Join Results] Will force a filtered portal to update. But I probably would not use a filtered portal for this as you can set up a relationship to a difference Table Occurrence of Music Notes and use a global field defined in Standards for student ID and a Calculation field defined in standards that always returns the text "yes" as a pair of match field to match to StudentID and Mastery in this new occurrence of the Notes field. Then you avoid the update issue with a filtered portal not updating.

                       A script on the students layout can set this global field to the studentID of the current student record and change to the report layout to show the data for that student.

                  • 6. Re: Nested Loops Question
                    PatrickLollis

                         I think part of my brain just shut down reading that...LOL. I'll read it SLOWLY and search some terms and make it happen though.

                         Thanks again! I'll post when I get it to work or run out of ideas. :-)

                    • 7. Re: Nested Loops Question
                      PatrickLollis

                           OK...let me see if I understand.

                           I duplicated the Music Notes table.

                           I created a global field in the Music Standards table called global_id.

                           I created a yes_match calculated field in Music Standards that equals "Yes".

                           I joined the Music Standards table to Music Notes 2 with both of these fields.

                           Now, when I change the value of the global_id field, it AUTOMATICALLY filters the records through this relationship to fields for that student where mastery = "yes".

                           Now, I need to alter the script for the button on the Student layout to copy the current student_id, open the Music Standards table, and paste the student_id into the global_id field of the first record. Then, I can simply open a portal and display the (latest) date. BRILLIANT!

                           For the max score, I'm guessing that I can repeat this procedure with Music Notes 3, global_id, and no_match, then sort the records by score descending in the portal.

                           Question: For space reasons, I need to put these two fields one on top of the other. The max score portal will have a value even if the standard has been mastered, but the mastery portal will be blank if it hasn't been finished yet. Will it work to put the mastery portal on top of the max score portal conditionally formatted to have a white background for "yes" and a transparent background for "no"?

                           Wow, I hope this works!  :-)

                            

                      • 8. Re: Nested Loops Question
                        philmodjunk

                             I duplicated the Music Notes table.

                             Well you duplicated the Music Notes table occurrence. And this is correct, but the names used can be important when discussing FileMaker's relationship graph.

                             Will it work to put the mastery portal on top of the max score portal conditionally formatted to have a white background for "yes" and a transparent background for "no"?

                             That's an interesting question. I think that will work in FileMaker 13, but I wouldn't change fill colors, I'd keep both fields transparent but use the conditional formatting to set the text sizes very large (120+ font size) when I want the contents of that field to be invisible. Even better, use the new "hide when" property to hide the entire portal if you are using FileMaker 13.

                        • 9. Re: Nested Loops Question
                          PatrickLollis

                               I created a portal to Music Notes 2, and when I pulled up a report, there was a date beside every standard.

                               On a hunch, I filtered the portal to Music Notes 2::music_standard_id = Music Standards::music_standard_id, and that fixed the problem. The mastery piece is working perfectly and instantly now across all the students I've tested.

                               My understanding of a portal was that it would only pull up related records, so it seems like this filter would be unnecessary. Am I misunderstanding?

                               I'll try the max score box next.

                               Also, any idea why the portal to the student's name shows up in the browser view, but not in preview? I really need the child's name at the top of their report.

                          • 10. Re: Nested Loops Question
                            PatrickLollis

                                 Yahoo! Not only did the score portal work, but since each portal is sorted by "Yes" and "No" in the mastery field, there is no possibility of an overlap. I just put one field directly on top of the other, and it works exactly as planned.

                                 If I can get the kids' names on top of this report, I'll be a very happy camper indeed. :-)

                                 Compared to all the learning I did on this today, writing a script to loop through a group of students, saving and emailing their reports as we go, will be a snap!

                            • 11. Re: Nested Loops Question
                              PatrickLollis

                                   Everything is working correctly and looped to work for all students. Thanks so much for all your help. :-)