14 Replies Latest reply on Jun 2, 2010 11:14 AM by Pwrjnky

    Printing Lists without duplicate information

    Pwrjnky

      Title

      Printing Lists without duplicate information

      Post

      I have a large collection of sheet music from movie and broadway productions. I want to print out a list using only two fields: (1) the song title, (2) the production it was featured in. The list will be sorted by the production name. In some cases I have several songs from one production. In these cases I don't want the production name to be repeated for each song. How do I do this?

       

      I want it to look like this:

       

      Production                         Song

      Anything Goes                   All Through the Night

                                                   Anything Goes

                                                   Blow Gabriel Blow

      Around the World              If You Smile at Me

                                                   Look What I Found

       

      Not like this:

       

      Production                        Song

      Anything Goes                  All Through the Night

      Anything Goes                  Anything Goes

      Anything Goes                  Blow Gabriel Blow

      Around the World             If You Smile at Me

      Around the World             Look What I Found

       

       

      FMP11

      Mac OS 10.6

      Little experience with databases

       

        • 1. Re: Printing Lists without duplicate information
          philmodjunk

          There's a "cute trick" in filemaker that will allow you to do this.

           

          1. Add a sub-summary layout part, specifying the Production Name as its "when sorted by" field. Choose the "above" option so that the sub-summary part is placed above the body.
          2. You can change the size of this sub-summary part so that it is just one or two pixels tall to keep it from being easily noticeable.
          3. Place both the Production and Song Title fields in the body side by side. Make sure that the vertical text alignment in each is set to "bottom".
          4. Now click the Production field to select it and drag one of the top selection handles upwards to make the field just tall enough so that a pixel or two crosses the line into the sub-summary part.
          5. Now sort your records by Production name.
          6. In Filemaker 10, 11, you see the result you want.
          7. In older versions, you'll have to enter preview mode or print the report to see the effect you want.

           

          How it works: Any object that crosses the line into a sub-summary part or a header will normally be displayed as though it is wholly enclosed with in that layout part.

          • 2. Re: Printing Lists without duplicate information
            Pwrjnky

            You, my friend, are a life saver. I thought sure I was going to have to do some kind of relational table voodoo that would take me many hours to learn. Your solution works beautifully. I was even able to add a horizontal line separating each production name subset by sneaking it into the sub-summary part.

             

            For future reference, you might add to your instructions a note to uncheck "object grids" under the "Arrange" menu to allow fine adjustments of the part size and text block size. "Zoom in" under the "view" menu is also helpful for making those fine adjustments.

             

            Thank you for offering your time and expertise. 

             

            Tom

            • 3. Re: Printing Lists without duplicate information
              philmodjunk

              Both good suggestions and Here's one better:

               

              To make fine adjustments to a layout part, click the part label and use the Inspector (FMP 11) or Object Info (Older versions) to specify the height. You can adjust the height of a layout part one pixel at a time this way if necessary.

              • 5. Re: Printing Lists without duplicate information
                Pwrjnky

                Phil,

                 

                When you helped me solve this problem I was using a trial version of fmp11 and I was able to see the desired result in browse mode. Since then I've purchased a full version. Unfortunately, I can now only see the desired result in the preview mode. Any idea why?

                 

                Tom

                • 6. Re: Printing Lists without duplicate information
                  philmodjunk

                  It shouldn't make any difference. This trick worked way back in the filemaker 3 and 4 era but you had to enter preview mode or print to see it. WIth filemaker 10 and 11, you can see summary reports in browse mode.

                   

                  The only thing that comes to mind is to check your sort order and make sure it's the right order for the sub-summary part you set up.

                  • 7. Re: Printing Lists without duplicate information
                    Pwrjnky

                    Phil,

                     

                    I've played around with the layout a bit. I enlarged the sub-summary part to about field height. I incrementally moved the upper boundary of the production name field up into the sub-summary part to see if there was some point when the field would show up the way it's supposed to in Browse mode. When I move the handles up far enough that the actual text name of the field begins to extend into the sub-summary part, then that upper portion of the field text begins to show up in browse mode. The only way to get the full text of that field to show in browse mode is to stretch the handles up far enough in layout mode to move the entire field name up into the sub-summary part.

                     

                    By the way, I've noticed that the extension on the file name is .fp7. Is that the way it should be?

                     

                    Tom 

                     

                     

                    • 8. Re: Printing Lists without duplicate information
                      philmodjunk

                      The suffix, .fp7 has stayed the same since filemaker 7. That's because the file format has not changed. All updates/new/features released since then have not required a change to the file format.

                       

                      I did some quick testing. I think Filemaker Inc. fixed a Filemaker 10 bug that I reported and this changes the behavior a bit. When I preview the layout, the field displays the way I want it. When I enter browse mode, this field disappears. It appears that text in the field is clipped by the border boundary. Thus in filemaker 11, this trick becomes a preview/print only trick.

                       

                      For browse mode, it may be possible to set up a conditional format on the field that changes the text color to match the layout color in all cases but the first record in each group.

                      • 9. Re: Printing Lists without duplicate information
                        Pwrjnky

                        I had suspected that a "fix" might be the problem. The conditional text color idea is intriguing. I'll experiment. If you come up with something, let me know. Worst comes to worst I'll use two layouts, one for viewing and making changes to on-screen, in which I'll move the production name field up into the sub-summary part, and another for printing, in which I will set it up as you suggested in order to minimize the length of the printed list, since I have a couple thousand records, most of them with unique production names.

                         

                        Again, thanks for your help.

                         

                        Tom

                        • 10. Re: Printing Lists without duplicate information
                          philmodjunk

                          The trick with the conditional formatting, is that you somehow have to identify the first record of each sub group. Here's one method: http://www.4shared.com/file/1RZG5vDA/HiddenSubHead.html

                          It uses a self-join with a sorted relationship so that you can compare the name from the first record in a category with the name of the current record in your conditional format expression.

                          • 11. Re: Printing Lists without duplicate information
                            Pwrjnky

                            I can't believe it, but I was actually able to apply this solution. I don't really know anything about relational tables, but, by examining your file, doing a little research to realize what a second occurrence of a table was, then basically copying all your work, but substituting in my field and table names into the formulas...I got the results I want. I don't think I'll have the slightest idea how I did it tomorrow, but if I was able to figure it out by examining your file, I should certainly be able to do it again (if I have to) by examining my own. Talk about a crash course in database management!

                             

                            You da man!

                             

                            Thanks,

                            Tom

                            • 12. Re: Printing Lists without duplicate information
                              Pwrjnky

                              Phil, I'm having a problem with the conditional formatting that you showed me how to do. Whenever  a new record is added that alphabetically precedes the first record within a category, it pushes the record with the visible (black) category field down one place when it sorts the list. That's normal, but in your file posted at http://www.4shared.com/file/1RZG5vDA/HiddenSubHead .html the new first record will have the visible category field (black) and the category field of the previous first record will turn invisible (white). The top record is always the one with the visible category field, which is the desired result. For some reason mine isn't working that way, even though, as far as I can tell, it's set up identically to yours. Any idea why?

                               

                               

                              • 13. Re: Printing Lists without duplicate information
                                philmodjunk

                                Did you specify a sort order in the relationship? Double click the line between table occurrence boxes in the relationship graph and click the sort checkbox. You can refer to my demo to see how it should be set up.

                                • 14. Re: Printing Lists without duplicate information
                                  Pwrjnky

                                  I just spent all of this morning fooling around with the relationships graph on your file. Finally I noticed that when I clicked on the little equal sign between the tables the pencil icon at the bottom of the window became active, and if I clicked on that icon a window came up that had the sort box checked for the duplicate table. I went back to my file and found that the same box was not checked. Aha! Of course then I came back to the forum to see if you had responded yet to my question (and let you know not to bother if you hadn't) and found that you had, in fact, answered two hours before I laboriously and profanely figured it out for myself. So now, I'm about to complain about this site not having an email notification system and then look up to see that I have failed to check yet another little box: "Email me when someone replies" Too many little boxes for my pretty little head!

                                   

                                  However, once again, I greatly appreciate your quick, and correct, response to my question.

                                   

                                  Thanks,

                                  Tom