4 Replies Latest reply on Feb 27, 2013 1:22 PM by EP

    Subtracting record count in reports

    EP

      Title

      Subtracting record count in reports

      Post

           I have a script that creates a report.  This reports displays an order date and a "type" field listing type of fruit ordered.  I have a sum field that displays the amount of records found (amount of orders) within a date range.  I want to subtract the amount of orders that contain "apples" in the "type" field.  Is this possible, perhaps via a calc field?

           Thanks

        • 1. Re: Subtracting record count in reports
          philmodjunk

               "Sum" field or summary field?

               Is there only one type of fruit ordered in each order record or do you have a related table that lists each fruit sold for a given order?

          • 2. Re: Subtracting record count in reports
            EP

                 Summary = count of ( _fkClientID). 1 type of fruit per order

            • 3. Re: Subtracting record count in reports
              philmodjunk
                   

                        that displays the amount of records found (amount of orders) within a date range.

                   One simple way to do that is to omit the "apple" orders from your found set. That can be done with the same find that used the specified date range to find your records.

                   There are quite a few other options that don't require omitting them from the found set but all are more complex than that first suggestion.

                   A calculation field, for example, can use If or Case to return a value for all records that are not "apple" orders. Then a summary field that computes the total of this field will omit the apple records from the count.

                   If you are using FileMaker 12, an executeSQL function in a calculation field can compute the needed "found set - count of apples".

              • 4. Re: Subtracting record count in reports
                EP

                     Thanks Phil, this is how I currently have it set up.  I want apples to display in my report, but not in the summary.  I would like to have a summary that shows the count of all records NOT containing apples and then a summary field showing the count of orders containing apples.  (I sort my report by "Type" so I want Apples to appear with a sub-summary).