1 2 Previous Next 18 Replies Latest reply on Aug 6, 2015 9:58 PM by ericruff

    Report listing child records from just one parent record

    ericruff

      I feel so dumb asking this.  How do I limit a report to showing the child records from just one parent record?  I attach my sample database from a school holding just two classes, Algebra I and History II .  Each class has several students.  The Class_subject table has 4 fields, a primary key field, the name of the class (Algebra I), the teacher's name, and the name of the textbook being used.  The Students table has just 4 fields as well, the foreign key field, the student's first name, the student's last name, and the student's birthday.

      The students appear (and the records are created) in a portal on the layout for the class in which they are enrolled.  The relationship between the two tables allows the Students foreign key field to be automatically created when a new record in the portal is created.  No problem.

      Now I want to generate a roll call report listing all the students for a particular class.  I created a report layout and it lists all the students and gathers them by class and shows all the students.  But this is a report for the whole school and both classes are listed. The thing is, I just want to create a report showing the students in one of the classes, say Algebra I.  I go to that record in the Class_subject table and run a script "Select just this record" which creates a found set of just one record, Algebra I.  Yet when I run the Roll Call report script, it show the students from not just Algebra but from the other class, History II, as well.  How can I run a report that limits the results to the child records of just one parent record?  I realize the portal itself could be used to make a report, but for other reasons, I want to do this through a report layout.

      What am I missing? The answer seems to be staring me in the face, but I am drawing a blank.

        • 1. Re: Report listing child records from just one parent record
          jbrown

          Good afternoon.

          I used to work in a school and designed their system. These kind of tasks are quite common.

           

          PROBLEM:

          Your script that is supposed to show the roll call simply goes to the roll call report without doing any finds. It just goes to that layout and shows all the records, in this case. You have to go to that layout and FIND the students that belong to this class. To do that, there are two ways:  Go To Related  Record or Grab ID of the class and Find.  Both do the same thing.

           

          Use the Go To Related Record script step which finds the related records in the other table provided there is a relationship from this class table to the students (there is : the portal shows that). the Go to Related Record step (GTRR) will show only those students. Be sure to walk through the options: select the RELATED table in the drop down of the script step options.  Use the Roll Call layout. And Match current record only.

          Screen Shot 2015-08-02 at 5.29.04 PM.png

          However . . .  to really make the data structure 'correct', i recommend the following.

           

          The first thing you probably want to do is to create a join table, an enrollment table between classes and students. Ideally you don't want the students TO to be in the portal because one student can be in many classes. This Enrollment would contain the ID of the class and the ID of the student, thus joining the two tables together by the relationship. You'd maybe want to include a school year in this table as well.

           

          The next thing to do is to create a new layout based on the enrollment table. It would have on it the class name, from the classes table, and the student name from the students table.

           

          TO print a report of Algebra, you start from that class table and that record. I'd then use the script step GO to Related Records, going to that enrollment-TO-based layout and find the students there.  Or simply grab the ID of the class in a variable, navigate to this layout, do a find for this ID in the ID_Class field.

           

          There's some other fine-tuning to do, such as error trapping and such, but this should get you started.

           

          For a school database, I'd really recommend  the idea of a join table, the enrollment table. This has the advantage of storing every students' entire class list AND that for many years.

          • 2. Re: Report listing child records from just one parent record
            erolst

            Well, if you simply switch the layout, you get whatever found set was active the last time you visited that layout.

             

            Simplest remedy would be use Go to Related Record/Request:

             

            //Enter Browse Mode

            Go to Related Record [ From table: “Students”; Using layout: “Roll call report” (Students) ] [ Show only related records ]

            If [ not Get ( LastError ) // or the more explicit error number for no related records ]

              Sort Records [ Keep records in sorted order; Specified Sort Order: Students::_fK_Class; ascending ] [ Restore; No dialog ]

            End If

             

            You are aware that your current data model allows a student only to participate in a single class? You're missing a join table between Class_subject and Student.

             

            PS: To isolate a record (though that is not really of relevance here), instead of

             

            Show All Records

            Omit Record

            Show Omitted Only

             

            use

             

            Find Matching Records [ Replace; primaryKeyField ]

            • 3. Re: Report listing child records from just one parent record
              ericruff

              Jeremy, thank you so much! Perfect answer. I really appreciate it.

              • 4. Re: Report listing child records from just one parent record
                keywords

                A separate issue with your file is that you don't currently have a mechanism for handling a student who is enrolled in more than one class—a standard many-to-many issue. On your demo, if say Billy Simpson wishes to enrol in BOTH Algebra and History, you will end up with duplicate student files. Between your Classes and Students tables you need a join table, Enrolments, with records that just carry a studentID and classID.

                • 5. Re: Report listing child records from just one parent record
                  jbrown

                  You're welcome.

                  I'd strongly consider that enrollment table. Feel free to reach out if you need any additional help.

                   

                  And . . .

                  Don't forget to mark an answer as correct or helpful.☺ It helps those who answer out with a little point system.


                  Thanks

                  • 6. Re: Report listing child records from just one parent record
                    ericruff

                    To all:

                    I apologize. I read you loud and clear on the many-to-many relationship that is missing for Students who have many Classes.  I apologize, but I was just trying to come up with a sample solution that would have a simple parent/child relationship so that I could talk about reporting child records limited a single parent record and... a class-student situation was the first example that popped into my addled mind.  My actual solution is not related to schools and classes at all.  I should have chosen a different sample model that did not hint at a many-to-many relationship issue.

                     

                    Thank you all for your guidance.  And thank you erolst, for reminding me to trap for the child table with zero records.

                     

                    Eric

                    • 7. Re: Report listing child records from just one parent record
                      erolst

                      ericruff wrote:

                      I should have chosen a different sample model that did not hint at a many-to-many relationship issue.

                      Why not simply use the real entities and names?

                       

                      This way you neither run the risk of choosing an inappropriate model, nor do you have to perform any mental gymnastics to transpose suitable answers to your situation (and recognise them in the first place) – and, last but certainly not least, you save your prospective helpers from giving unnecessary advice to remedy a model perceived as flawed.

                       

                      Next time …

                      • 8. Re: Report listing child records from just one parent record
                        ericruff

                        Again, I apologize.  The real situation is that I am designing a filemaker solution to mimic a Court approved form that has space for only limited repetitions of the same data, for example, room for two client's prior addresses, but in fact there may be many prior address and the form instructs us to attach a Continuation Sheet if there are more than 2 prior addresses.  There are many places on the form like this.  My approach was to make an additional table for fields where there might be more answers than the form provided for, such as a table for all prior addresses (thereby preserving the possibility of unlimited number of prior addresses) and then, just before printing, assign the first to records from that table to fill in the two "hard-wired" places on approved form, and then locate all the additional prior addresses (those with record numbers >2) on the continuation sheet in the form of a Report.  My problem was that my client table was picking up related records for prior address from not just this client, but all clients in the database because I had forgotten the GTRR step.  I didn't explain my real world situation because it seemed too cumbersome; my actual solution has over 100 fields so far, most of it unrelated to my issue, and I thought a tiny example db would explain my point.  Unfortunately, my example was poorly chosen causing others to give unnecessary advice and for that I am sorry.

                        • 9. Re: Report listing child records from just one parent record
                          jbrown

                          Hi.

                          Don't worry about it. Your example, even if it was fake, provided you the answer. And if we gave irrelevant advice for this problem, it is relevant for some other problem you may encounter. So it was a win-win: you got your answer and maybe have more information on correct data structure for future issues. I had no issue with writing up a description to, as it turns out, a fake problem.

                           

                          By the way, the Student / enrollment / classes is just one specific example to a larger 'join' concept, if you're not already familiar with that.

                           

                          And your real issue is a common issue with databases: developers should almost always put related information that could be more than one into a child table. Anytime you start labeling fields as "Phone1", "Phone2", etc. its time to create a related table.

                           

                          Good luck. And We'll be around for more help if needed.

                          • 10. Re: Report listing child records from just one parent record
                            keywords

                            Interesting to read your longer description of the real setup you are grappling with, and it suggests to me another issue. If your form displays two prior addresses—presumably penultimate and antepenultimate—how do you deal with a subsequent change of address? I assume what was previously given as current address becomes penultimate, what was previously penultimate becomes antepenultimate, what was previously antepenultimate drops off the form. To avoid having to manually process that you should consider keeping ALL addresses in the same table, including from–to dates to give an order, and connecting these to the form itself via an order match. As far as the from itself goes, you could then display them via on portal that just displays the current address and another that displays latest two priors. Of course you may already be doing that for all I know.

                            • 11. Re: Report listing child records from just one parent record
                              ericruff

                              Hmm.  I like your ideas maybe better than mine.   I am dealing with a government form that has to be printed and signed by my client.  There will be a lot of data entry and correction of data, but once the data is in its final form, the document will be printed out, signed by the client, and submitted for government approval. That is, it is not a form that will likely be updated and re-signed at a later date.

                              As far as an example field is concerned, the instructions I am operating under don't necessarily require me to list all the client's prior addresses in any particular order, but let's just pretend that is a requirement. All the prior addresses are in a related table "Prior Addresses".  For each prior address I can have a date field "date_moved_into_this_address" and I can sort on that field. The government form itself has room for just 2 prior addresses and the instructions mandate that if I have more than 2, the additional addresses should be listed on a separate continuation page.  I am not supposed to change the form in any way, even if I think my change would be an improvement.  So, I have the main form layout, which is just the government's .pdf form on the layout, and I am laying my fields on top of that.  I also have another layout I designed which is the continuation sheet available for "spillover" addresses (priors address records  #3,4,5...nnn.

                               

                              Until I read your post,  my plan had been to place on the .pdf form some "hard wired" address fields for related records 1 and 2, with fields like Prior_address_1_street, Prior_address_1_city, Prior_address_2_street, Prior_address_2_city.  My plan was that when I was satisfied all the data was in clean final form, I was going to have a script populate those hardwired fields with data from the first 2 records of the table "Prior Addresses".  You suggest putting a portal there with room for just the first two records.  I think that's a great suggestion, and will eliminate a script to populate them, and eliminate a bunch of hard-wired fields.

                               

                              As to the continuation page, I was going to do a Report layout, but another portal may be the answer again.  The trick is to limit the records on the continuation page to records 3 and above, yet not to have a limit on how many spillover records can be listed.  My Report (not portal) technique for that works, but I am hoping you have something slicker.  In the main client file I have a number global "Max_records_on_form_prior_addresses" that is the maximum number of records that can appear in the main form, in this case that global is set at 2. Next I added a calculation field to the "Prior Addresses" table called "Record_number" which is calculated to identify what record number this record is, the calculation being Get ( RecordNumber ).


                              Then in the relationships graph I made another occurrence (is that the right term or maybe instance") of the table "Prior Addresses" which is then named "Prior Addresses 2".  I then made a relationship between the Client_file table and the Prior Addresses 2 table with two match conditions.  First, the normal match which is the primary key to foreign key. The second relationship match, or condition, is that the field Record_number > Max_records_on_form.  This limits the records on the continuation page to records 3, 4,and higher, and it seems to work.  I imagine anyone reading my method is throwing up at this point.  You said I could use on the Continuation page "portal that just displays the current address and another that displays latest two priors." I suppose I could again use a portal and simply specify the initial row at 3, and that should show only the spillover records.  I guess the drawback to the portal approach is that the spillover portal requires you specify how many total records display in the portal, like 10. I was hoping to avoid having hard limits like that.  It seems like the minute you say "I'll probably never have a  client with more than prior addresses, sure enough two weeks later you have someone from the military that has 12 prior addresses. An so forth will all kinds of fields, not just addresses.

                               

                              Do you have a suggestion on how to handle spillover records without an upper numerical limit?


                              But in general I have the feeling I am taking to long cut to grandma's house...

                              • 12. Re: Report listing child records from just one parent record
                                keywords

                                I'm glad my suggestion has got you thinking. Portals are not really intended for printing but rather display on screen, where you can set the portal to display as few records as you wish, but with all relevant records accessible by scrolling. My suggestion of using portals was not for your continuation report but on the form itself, because there you have a number limit. A current address portal need only display ONE record, and a prior address portal is, by order of the Govt, limited to TWO records. The first one is easy if the addresses are sorted in order from current > oldest, as current will be the first record. The second portal will require either a filtered TO or a portal filter to start at record 2. So my assumption is that current address is record 1, penultimate is record 2, antepenultimate is record 3.

                                For your continuation report I would not recommend portal, for the very reason you mention—the number of records is indeterminate. For that report a list report based on the address table would definitely be best. And as you suggest, you would need a TO that starts at record 4.

                                I hope that makes sense.

                                • 13. Re: Report listing child records from just one parent record
                                  erolst

                                  keywords wrote:

                                  For your continuation report I would not recommend portal, for the very reason you mention—the number of records is indeterminate. For that report a list report based on the address table would definitely be best. And as you suggest, you would need a TO that starts at record 4.

                                  I hope that makes sense.

                                   

                                  It does – except that there is no TO that “starts at record 4”; preparing that list would be a script task.

                                   

                                  And yes, it cannot be emphasized enough that portals are a great UI device, but not really suited to presenting printable lists of indeterminate length (though that could be scripted, too).

                                  • 14. Re: Report listing child records from just one parent record
                                    erolst

                                    ericruff wrote:

                                    Do you have a suggestion on how to handle spillover records without an upper numerical limit?

                                     

                                    I said this could be done using a script – here it is:

                                     

                                    Set Variable [ $SPILLOVERFROM; Value:3 ]

                                    # [ keep #s from this value upward; avoids having a “magic number” suddenly appear ]

                                     

                                    Go to Layout [ “Addresses” (Addresses) ]

                                    Sort Records [ no dialog; Person::id; Addresses::address# ]

                                    # [ second sort field is whatever you use to rank addresses as #1, #2 etc.)

                                    Go to Record/Request [ First ]


                                    Loop
                                      Set Variable
                                    [ $sizeGroup; Value:GetSummary ( FastSummary::cCountAll ; Person::id ) // first sort field ]

                                       # [ count of addresses for this person ]

                                      Set Variable [ $omitCount; Value:Min ( $sizeGroup ; $SPILLOVERFROM - 1 ) ]

                                      Set Variable [ $nextGroupAt; Value:Get ( RecordNumber ) + $sizeGroup - $omitCount ]

                                      Set Variable [ $isLastGroup; Value:$nextGroupAt > Get ( FoundCount ) ]

                                      Omit Multiple Records [ no dialog; $omitCount ]

                                      Exit Loop If [ $isLastGroup ]
                                     
                                    Go to Record/Request [ no dialog; $nextGroupAt ]

                                    End Loop


                                    Go to Record/Request
                                    [ First ]

                                     

                                     

                                     

                                    gives you a found set of people with addresses ranked $SPILLOVERFROM and higher.

                                    1 2 Previous Next