8 Replies Latest reply on Jun 9, 2017 7:09 AM by philmodjunk

    Invoice Report

    sam0723

      Hi, i am new to filemaker. Now, i am working on the invoice system. My original idea is to create a invoice and invoice line item table(portal) to allow generation of the invoice

       

      For example,

      Record 1

      Invoice number

      name

       

      Portal

      catagory    wording     amount

       

      so the invoice report will be based on the invoice line item and the summary field could calculate the total of all items with same invoice number.

      i have made a various version for several languages that means i create 6 tables and corresponding line item tables to store the data separately.

       

      Now, my boss would like to have a summary report of the clients(based on the invoice table). so my question is do i need to rewrite the whole app.

       

      my first thought is to combine all the tables into 1 so it is much easy to handle the summary report.

      welcome to any approach. Thank you. i am using filemaker 15.

       

      The summary report is like

      date          invoice number      total of each invoice    settlement

      ......

       

                                                  subtotal

        • 1. Re: Invoice Report
          erolst

          sam0723 wrote:

          i have made a various version for several languages that means i create 6 tables and corresponding line item tables to store the data separately.

          ...

          my first thought is to combine all the tables into 1 so it is much easy to handle the summary report.

          welcome to any approach. Thank you. i am using filemaker 15.

          Definitely use a single table!

           

          If you create a Language table, you can give each customer and/or invoice a foreign language key, then reference the appropriate vocabulary from a third table, where you can store words or entire phrases. With the correct setup, you can also easily have multi-language Product/Services names.

           

          Ideally, you also only need a single layout for invoices and reports.

           

          Try to think of the UI elements that you need in your layouts as another kind of data.

          • 2. Re: Invoice Report
            sam0723

            thank you, i am thinking of how to summarise the amount of each items of an invoice. My idea is creating a calculation field to calculate all items of an invoice. A summary field to calculate total amount of invoice with same client.

            When i create a summary layout which is based on invoice line item table, the summary field works perfectly, but when i change the layout into basing on invoice table. The summary field could not work.

             

            if i would like to create a report like

            date          invoice number      total of each invoice    settlement

            ......

             

                                                          subtotal

             

            That means i could not use summary field? i should use SQL to get the result? Also, how could i calculate the subtotal of all invoices of same clients. Thank you

            • 3. Re: Invoice Report
              siplus

              summaries and reports are to be created in the line items table, not in the invoices table.

              • 4. Re: Invoice Report
                sam0723

                Thank you, is there any ways to create a reports based on invoices table.

                • 5. Re: Invoice Report
                  siplus

                  Basically the idea is to summarize data in the table where the data is.

                   

                  So if you want to see how many items of each type you sold, you'll do that in the lineitems table.

                   

                  If you want to know things related to invoices, like breakdown of invoices per customer state, then you'll do it in the invoices table.

                  • 6. Re: Invoice Report
                    philmodjunk

                    If you want a report with one row for each invoice with total(s) from that invoice in the row, you can get that on a layout based on either table. In either case, your summary fields would be defined in the line items table.

                    • 7. Re: Invoice Report
                      sam0723

                      i set it in this way, but the calculation result of the summary field is not correct.

                      • 8. Re: Invoice Report
                        philmodjunk

                        To repeat, define the summary field as a field in the line items table.

                         

                        If you have more than one occurrence of line items in your relationship graph, you must also select the correct occurrence when adding this line items field to your invoices layout.

                        1 of 1 people found this helpful