11 Replies Latest reply on Aug 27, 2013 10:03 AM by AnnieHsieh

    How to combine multiple records with same criteria into one letter

    AnnieHsieh

      Title

      How to combine multiple records with same criteria into one letter

      Post

           I'm using Filemaker Pro 12. I've been trying to put multiple records that has the same criteria together but couldn't figure out how.

           This is what I have                                                          This is what I want to do

           Teacher A       Teacher A                          -->                   Teacher A       

            Studnet X       Studnet Y                                                  Student X       

                                                                                                        Student Y      

                                                                                                                                              

            

        • 1. Re: How to combine multiple records with same criteria into one letter
          philmodjunk

               Can you explain that in more detail?

               Does your solution currently have just one table or do you have a table for students and a table for teachers?

          • 2. Re: How to combine multiple records with same criteria into one letter
            AnnieHsieh

                 I have them on one table, separate fields. So I have one "Teacher" field and one "Student" filed. What I am trying to do is to put them on a letter that I am working on. I want the student names to be listed below the particular teacher.

                 So like

                 Teacher A       Teacher B

                 Student X        Student P

                 Student Y        Student Q

                 Student Z        Student R

                  

                 But right now I don't know how to carry that out

                 I have one teacher per student on my letters now. So if I were to send the letters right now a teacher might receive 10 letters with 10 of her student on each of them.

                  

                 Thanks so much for helping, hope that explained my situation better

            • 3. Re: How to combine multiple records with same criteria into one letter
              philmodjunk

                   normally, in database documentation a record represnts a row and the fields are arranged in columns. I think your example shows6 records with 2 fields like this:

                   Student X    Teacher A
                   Student Y    Teacher A
                   Student z     Teacher A
                   Student P    Teacher B
                   Teacher Q   Teacher B
                   Teacher R   Teacher B

                   and if you want to see a report like this:

                   Teacher A
                      Student X
                      Student Y
                      Student Z

                   Teacher B
                       Student P
                       Student Q
                       Student R

                   Then create a layout to be viewed in List View.

                   Use the part setup dialog to add a sub summary layout part "when sorted by" your Teacher field.
                   Select the "print above" option when asked and place the Teacher field inside the Sub Summary part. put the student field inside the body layout part. (You can resize the body to be just slightly taller than the student field.)

                   Return to browse mode and sort your records by the Teacher field and you'll get the above result. If the records are not sorted by the teacher field, the sub summary part will not be visible.

                   Note: this structure assumes that students only have one teacher as is typical of lower grades here in the US. If you have a school system for older children where one student has more than one teacher, a different table structure is advisable.

              • 4. Re: How to combine multiple records with same criteria into one letter
                AnnieHsieh

                     Hi, 

                     Thank you so much for helping!

                     So I went over the steps and got the table sorted by "teacher" already, but I still don't know how to carry it out in the letter. How do I do that to make the student X Y Z s' name be printed below teacher A in a letter?

                • 5. Re: How to combine multiple records with same criteria into one letter
                  philmodjunk

                       My example shows it printed below the teacher's name.

                       But what do you mean by "letter"? Can you describe that in more detail and will this be a letter that is printed and mailed out or will it be emailed?

                  • 6. Re: How to combine multiple records with same criteria into one letter
                    AnnieHsieh

                         It's a letter that will be mailed to teachers. When I am looking at the letters there are still 5 letters ( multiple ) for each teacher. But what I'm trying to do is to put those 5 students with the same teacher into 1 letter 

                         So it will be like

                         Dear teacher A,

                         Your following students have been selected....

                         Student X

                         Student Y

                         Student Z

                          

                         Right now I have

                         Dear <<Teacher FirstName>>,

                         Your following students have been selected....

                         <<studentfirst>> <<studentlast>>

                          

                         And this only shows one student in one letter

                    • 7. Re: How to combine multiple records with same criteria into one letter
                      philmodjunk

                           There is more than one approach to what you want.

                           You might put:

                           Dear <<Teacher FirstName>>,

                           Your following students have been selected....

                           in the header layout part. and put:

                           <<studentfirst>> <<studentlast>>

                           in the body layout part and resize the body to be just tall enough to contain these two fields.

                           Any additional text after the list can be put in a trailing sub summary layout part.

                           A more flexible option would be to add another table with one record for each teacher that links to your existing table. Then you can set up a calculation field in that  table that uses either the List function or Execute SQL to produce a list of the students so that you can use a single merge field in your layout for listing the students. That takes more work to set up but gives you more flexible options for how you construct your letter.

                           You may also find this demo file on setting up a "Message Template" to be a useful enhancement over the basic Merge field based form letter layout: https://www.dropbox.com/s/6xw8buafjuohncu/MessageTemplateBuilder.fp7 You can open this file from FileMaker 12's File menu to get a copy converted into the .fmp12 format.

                      • 8. Re: How to combine multiple records with same criteria into one letter
                        philmodjunk

                             Oh yes, and the first method I described should be viewed in List view, not Form View in order to see all of the listed students in one letter. And you would first perform a Find for just one teacher.

                             Another variation would be to sort the records by teacher and use a pair of sub summary layout parts for the text above and below the list of students. This would allow you to print out all your letters in a single print job. (Specify a page break after every occurrence for the lower sub summary layout part.

                        • 9. Re: How to combine multiple records with same criteria into one letter
                          AnnieHsieh

                               I have tried your first method and it worked!! But when I click preview, the letter is separed into two pages. Does that have something to do with the pair of sub summary parts you just mentioned?

                               The pages is separated after the <<studentfirst>> <studentlast>> and the second page has all the texts besides <<studentfirst>> <<studentlast>>

                          • 10. Re: How to combine multiple records with same criteria into one letter
                            philmodjunk

                                 Did you select a page break option for any of these layout parts in part setup?

                                 It's also possible that your report layout, as designed won't fully fit on one page and so the last layout part appears on the next page.

                            • 11. Re: How to combine multiple records with same criteria into one letter
                              AnnieHsieh

                                   Yes it worked now thank you so much !!