2 Replies Latest reply on Aug 25, 2016 2:18 AM by alangodfrey

    How can I combine invoice line items into invoice total?

    JohnTennock

      I have imported some invoice data from an external source. In the source data there is one record for each invoice line item. Each line also includes the common header information like customer details. I need to display this in a portal for any selected customer as shown below. I am trying to figure out how to combine multiple line invoices (e.g #591, #677, #779) into a single line each with just the total value of that invoice per line. The Cumulative Invoice Total is a summary field, not part of the imported data.

      I have explored all the options I can find in the help file (like self-join relationships) but can't get the result I need.

       

      I would appreciate any suggestions as to how best to approach this.

        • 1. Re: How can I combine invoice line items into invoice total?
          Johan Hedman

          Two choices

           

          1.

               Create a List view with a Sub Summary Report and have the Customer field

               Sort your records on Customer field

           

          2.

               Create a new table for Customers and have a ID Unique number for each Customer. Then import Customer from your file and then match those Customer to your portal

          • 2. Re: How can I combine invoice line items into invoice total?
            alangodfrey

            Your question is slightly confusing.  Johan has described how to do what you ask for, which is how to see the total invoice value for a Customer.  But I am guessing that you are using the 'Find' function of Filemaker to just find one Customer's invoice lines, and then you want to see the totals of each invoice.

            If that is the case, do as Johan suggests, but create a sub-summary part on your layout and define it as 'When sorted by InvoiceNumber'. Create a Summary field = Total Of Invoice Line Value and pout it in the sub-summary part.  Sort the records by InvoiceNumber.

            Maybe even better, create 2 sub-summary parts, the upper one 'When sorted by InvoiceNumber', and the lower 'When sorted by CustomerID'.  Then sort by CustomerID, InvoiceNumber, and you will see the invoices grouped by Customer (and their total) and each Invoice for that customer totalled too.