4 Replies Latest reply on Jan 13, 2010 8:57 AM by philmodjunk

    How to get totals of number fields grouped by

    benbogaert

      Title

      How to get totals of number fields grouped by

      Post

      Hi all,

       

      I'm rather new to Filemaker and working with FM 10. I have experience in PL/SQL and Java programming.

      I'm working on a invoice system in FM 10 but I'm stuck on a particular thing what is not so hard in SQL.

       

      Here's the situation:

      I have, let's say, 10 invoices with articles that may or may not be on several of these invoices. Now I want to generate a report of the first 5 invoices that presents me the total of each article, so if one article is on invoice 1 and 3 I only want to see the article once but the total amount of this article must be the sum of invoice 1 and 3 of this article.

      In SQL this is relatively easy to do with a select sum(amount of the article) grouped by the unique article identifier.

       

      Is there a way to establish this in FM 10? I believe there will be, but I'm not getting there...
      Or is there a way to execute an SQL query in FM 10 and to generate a report out of the results of the query?

       

      thanks in advance.

        • 1. Re: How to get totals of number fields grouped by
          ninja
            

          Howdy benbogaert,

           

          I'm no pro at subsummary reports, but that is what you're after.

          You seem to be looking for a subsummary report, sorted by articles, on a found set of invoices.

           

          This post is simply to give you the key words to search this forum and FMP help.

          it will be another more versed in setting up these reports who can answer the detail questions you run into...

           

          hope this helps

          • 2. Re: How to get totals of number fields grouped by
            philmodjunk
              

            Check out Summary Reports and Sub-Summary parts.

             

            You can create a layout based on your invoices line items (you do have your invoiced items in a portal of related line item records don't you?).

             

            You can create a list view report and add a sub-summary "when sorted by" your item ID field. (The sorting does your grouping BTW.)

            Place all your line item fields you want for your report in this part--include a summary field to total up your quantities.

             

            Delete the body part from your layout.

             

            Now if you perform a find for the invoices you want and then sort the resulting found set as required for you sub-summary part, you'll get your report.

            • 3. Re: How to get totals of number fields grouped by
              benbogaert
                

              First of all thanks for the quick response.

               

              Now I have been able to set up a report with the total amounts grouped by article but I'm still struggeling with the sub-set of invoices I only want to show.

              What do you mean with perform a find?

               

              thanks

              Ben

              • 4. Re: How to get totals of number fields grouped by
                philmodjunk
                  

                You can perform a find by hand or in a script to restrict a report to data from a specific group of records.

                 

                Either way, you enter find mode, enter criteria (like a date or range of invoice numbers), find the records and then sort them (so that the sub-summary parts appear).

                 

                There's a lot of different ways to do this depending on your specific needs.