4 Replies Latest reply on Nov 1, 2013 8:13 AM by philmodjunk

    Best way to create this layout

    c.wagner1

      Title

      Best way to create this layout

      Post

           I have a fairly standard invoicing solution.  3 basic tables; customers, invoices,  and line items.  I would like to create a layout (not a report)  that lists all the customers and the grand total of all their invoices.  In the footer of the layout I would like to see the grand total of all the customers  invoices.

           I have come up with several ways of doing this with summary fields and portals.  The problem is with 20k customer records and 80k invoices in our database the performance of my answer has a lot to be desired.  I have a feeling this can be done using multiple table occurrences and complex relationships which should drastically improve the performance but I can not quite get it to work.  Any ideas?

           Thanks

           Chris

            

           ps FM 12

        • 1. Re: Best way to create this layout
          philmodjunk

               Which methods have you tried to use?

               I can think of three approaches and none require complex relationships or added table occurrences:

               A summary report with the body layout part replaced by a sub summary layout part and a summary field can be based on Invoices.

               A calculation field can be defined in Customers to use Sum ( ) to compute the invoice total and a summary field can compute the grand total.

               A script could be set up that updates a number field in Customers each time an invoice is "finalized". That number field then shows the customer total and a summary field references it to compute the grand total. This last option greatly reduces the number crunching needed for your report but is less flexible and has to be managed very carefully to avoid errors in your number field totals.

          • 2. Re: Best way to create this layout
            c.wagner1

                 I am currently using a summary field based on the invoices in a sub summary layout part and the performance sucks.  The solution runs on a very beefy server.  (i7 mac mini on an ssd with 16gb ram and server 12)

                 When loading the layout from a computer on the local network it takes 60 - 90 seconds for the layout to finish summarizing the invoice total field

                  

                  

            • 3. Re: Best way to create this layout
              tomasd

                   Filemaker can't use your hardware up to its full potential. Many records combined with summary fields or complex calculation can be very slow. Please send Filemaker feature request to speed up summary fields and calculations on the server, like I did.

                   Meantime, you can index as many fields as possible - Copy calculations to indexed fields in the server script.

                    

              • 4. Re: Best way to create this layout
                philmodjunk

                     The third option in my previous post can produce the report that you want very quickly.