7 Replies Latest reply on Aug 22, 2014 6:55 AM by erolst

    Summary field of a related table?

      This is probably very easy but I got a bit lost on the road....

       

      I have a table of orders and a table of invoices.

      One order can have none, one or multiple invoices.

       

      I am creating a report layout based on the invoice table. I create different parts and sort the invoices per year and per month.

      So far no problem...

      But now I would like to see on the same layout how many orders I had in each month and each year.

       

      ...

       

      Thank you

        • 1. Re: Summary field of a related table?
          erolst

          You cannot create a summary field for a related field – but you can create a relationship, count the related records (conditionally), and summarize that field.

           

          See the attached file.

          • 2. Re: Summary field of a related table?
            Extensitech

            Since an order might not have any invoice at all, this could be challenging. Imagine the scenario where, in a given week, you had 100 orders but no invoices. There's no record on your invoices report for that week, so you have nowhere to show those 100 orders. Ideally, since the invoicing for the week has no direct relationship to the orders for the week, you'd have a separate report for orders.

             

            If, however, you want to make the assumption that every week or year that had orders also had at least one invoice: Create two relationships from invoices to orders, one where the year matches and one where the week matches. Create a calculated field in Invoices for each, counting the related records. Show those calculated fields on the subsummary parts of your invoices report for year and week, respectively.

             

            Hope this helps.

             

            Chris Cain

            FileMaker Certified Developer

            Extensitech, LLC

            813.426.3366

            ccain@extensitech.com

            www.extensitech.com

            1 of 1 people found this helpful
            • 3. Re: Summary field of a related table?

              Thank you for the attached file.

              Why this case?

               

              Case (

               

                GetNthRecord ( cMY ; Get ( RecordNumber ) - 1 ) ≠ cMY

               

                ;

               

                Count ( Orders::_id )

               

              )

              • 4. Re: Summary field of a related table?
                erolst

                xaviervb wrote:

                 

                Thank you for the attached file.

                Why this case?

                 

                Case (

                  GetNthRecord ( cMY ; Get ( RecordNumber ) - 1 ) ≠ cMY ;

                  Count ( Orders::_id )

                )

                Because otherwise each Invoices record would carry the count, and thus the summary field would return Count ( Orders::_id ) * Count of Invoices records in cMY group – but you want that result only once for each month.

                • 5. Re: Summary field of a related table?
                  erolst

                  Extensitech wrote:

                   

                  Since an order might not have any invoice at all, this could be challenging.

                   

                  Only if you assume that the OP is planning to mis-use his Invoice report to primarily display Orders records.

                   

                  If, OTOH, the task is as described, then you just work with those Orders that match the Invoices you have (or display); that there may be Orders which are not counted since they don't match any of the displayed Invoices must be understood.

                  • 6. Re: Summary field of a related table?

                    Yes, I want to mis-use this invoice report not only to display Invoice information but also to display Order information.

                     

                    Probably I would be better off to create 2 different reports, print them and then with scissors glue them on the same page...

                     

                    It's just a request of the client... He wants a table with the total of orders of that month and the total of invoices.

                    • 7. Re: Summary field of a related table?
                      erolst

                      xaviervb wrote:

                      Yes, I want to mis-use this invoice report not only to display Invoice information but also to display Order information.

                       

                      I think the optimal approach depends on what your client wants to see in this report, i.e. how detailed it has to be; or if it needs to be a traditional report at all, or just a list of dates and numbers.

                       

                      If you use e.g. ExecuteSQL() and/or virtual lists to create a dynamic report that shows the figures, and if need be, the individual records, you're independent of which related records exist in the respective other table for a given date range.