8 Replies Latest reply on May 29, 2014 4:46 PM by SamRembert

    How can I create this layout?



      How can I create this layout?


           I touched on this a few months ago in another post, and the results got me somewhere, but I've kind of hit a wall.

           I need to take a list of data and break it down, using sub-summaries.  Easy.  The hard part comes when I'm trying to line everything up.  I don't necessarily want each sub summary to take up another line of paper.  Rather, I'd like the first body line to appear in the same row as the sub-summary.

           When doing simple one-field summaries, this is pretty easy using some conditional formatting.  But I'm having trouble figuring out how to do it when I have multiple summary fields calculating within one line, but for different summaries.  Not sure this is even possible in Filemaker.  I used to do it using the "1-pixel Sub-Summary" trick, but that was clunky.

           In this basic example, I'll use the five fields:

      •           Unit
      •           Shelf
      •           Type
      •           Quantity
      •           Total Quantity

           I can't seem to enter stuff in the text box of this forum to make everything line up, so I'll do it in an image.

           So the first part of the image is a simple list of items, broken down by the "Storage Unit" and the "Shelf" they sit on.

           When all is said and done, I'd want the list to total the number of each item on each shelf, as well as the total number of items per shelf.  This is a purely hypothetical example, but it's a simplification of what I actually want to accomplish but can't figure out.  It's having two separately calculated results on one line which is baffling me.  Well, that and getting multiple sub-summaries to go into the same line, which I believe isn't possible.

           An even bigger part is figuring out the dividing lines between sections, since conditional formatting can't be used for this.  Can anyone figure this one out?



        • 1. Re: How can I create this layout?

               In older versions we used to adjust the height of the field to extend at least one pixel across the body to sub summary border and then the field would display when the sub summary part displays and not in the subsequent records grouped beneath it. But this no longer works in newer versions of FileMaker.

               What you can do is leave these fields out of the sub summary part (only the horizontal lines shown in your example would be inside a sub summary layout part) but use conditional formatting to hide the field when the preceding record is part of the same sub summary group.

               The conditional format expression for your Unit field, as an example, might look like this:

               Let ( R = Get ( RecordNumber ) ; If ( R > 1 ; YourTable::Unit = GetNthRecord ( YourTable::Unit ; R - 1 ) ) )

               In Versions of FileMaker older than version 13, you can then specify a white text color or a very large font size to make the contents of the field not visible. In FileMaker 13, you can use this expression inside the new "Hide Object When" setting in the Inspector.

          • 2. Re: How can I create this layout?

                 Phil...thanks for your reply.  That "Hide Object When" setting in 13 sounds like it could be useful.  Might have to look at upgrading.

                 I was able to get close with the conditional formatting getting the Unit and Shelf sections to appear as I wanted.  So that worked.

                 However, I don't think I can do what is shown solely with Conditional Formatting.  Where I'm running into issues is the one field calculating the total number of items per shelf (under "Total") and the separately calculated number of each item (under "Quan") starting on the same line, with a second (or more) lines below that for other items.  For example, On Unit A, Shelf 1, there are 5 total items, 3 hearts and 2 moons.  I haven't figured out how to get the 5 and the 3 on one line, as they need to be calculated as a part of another sub-summary.

                 Maybe I'm missing something simple?  

            • 3. Re: How can I create this layout?

                   Make Quan a calculation field that uses the GetSummary function to compute the same subtotal that you'd get by putting the summary field into the sub summary layout part. This function uses two fields as parameters. The first parameter is your summary field, the second field, the break field, is the same field you'd specify as the "sorted by" field for you sub summary layout part.

              • 4. Re: How can I create this layout?

                     Hmmm...let me look into this GetSummary.  Maybe that will do the trick!

                • 5. Re: How can I create this layout?

                       Phil!  You're the winner.  I had never used GetSummary before, but I think it might be my new favorite calculation term, because that's a thing now.  

                       Based on a quick and dirty test with some basic data, it seemed to do what I want.  Now I need to scale it to the much larger database and see if everything works out for me.

                       Now the only trick is getting the dividing lines to work out.



                  • 6. Re: How can I create this layout?

                         Ironically the dividing lines are the only thing to put in your sub summary layout parts -- which can be sized to be nearly the same height as the width of the line.

                    • 7. Re: How can I create this layout?

                           Yep.  I did that, but it still leads to a little bit of line messiness.  I'm going to experiment today with the "hide object when" setting in FM 13.  It can be applied to lines as well, with the same conditional calculations used on hiding fields.  So I have a feeling some really clean layouts can be made this way.

                           Thanks for all your help!

                      • 8. Re: How can I create this layout?

                             Between that GetSummary Field, and my purchase of FM Pro 13 today to get me the "Hide Object When" option, I have made the layouts of my dreams today.  This is what I've wanted for 10 years.  Sweet!