4 Replies Latest reply on Sep 16, 2011 12:26 PM by tomyum

    Exclude product group from report summary



      Exclude product group from report summary



      I am using a modified version of the "Invoices" starter solution.
      I have created a report (based on Invoices) that gives me a summary of a selection of invoices within a given date range (and a couple of parameters excluding some of the invoices within the date range).  So far so good.
      But now I need to exclude one of the product groups from this report (IE create a report of the given invoices EXCLUDING all occurrences of the product group 15).

      I considered doing it the easy way and just getting a summary of the product group 15 for the given date range and subtracting this from the total in the report, but found that this would not always end up correct due to there being several variables in the invoice selection process (so sometimes this summary contained items that were not in the report initially and then the totals ended up wrong).

      Then I tried to make a new field in the Invoices table, planning to grab all the instances of product id 15 in Line Items and storing their value (price) in this field - and then just do a Sum of this field in the report and subtract it from the total.
      Anyhow, I can't get this to work...can anyone give me a hint of how you would choose to do this?
      It would be nice if I have the possibility to show the data from the excluded product group 15 on the report, so I can verify the numbers each time I do the report...

        • 1. Re: Exclude product group from report summary

          In your find, that pulls up records for a specified date range, you can include criteria that excludes products from this group.

          Create an additional find request. In a find script, you can enter find mode, specify your date range and use New Record/Request to create the new request. In a manual find, you'd select New Request from the Requests menu. Then specify your criteria identifying this group (put 15 in the group ID field?), but choose the omit option for this request. In manual find, you click the omit button. In a script, strangely enough, you use the omit record setp. Now perform your find.

          • 2. Re: Exclude product group from report summary

            If I want to just remove it from the report altogether, yes...but if I want to see a summary of group 15 in the report, and visually see the calculation at the end of the report...

            • 3. Re: Exclude product group from report summary

              Then set up a relationship that matches to this group and use it in your calculation. This may take some creativity to set up so that your specified date range also limits the data for this group as well but it can be done. (You can set up what I call a const (constant) field, constGroup15 as a calculation that returns only the value (15?) that identifies products in this group and use it as part of your relationship. There are also options that use a filtered portal (requires filemaker 11) and a summary field that can produce the same results.

              • 4. Re: Exclude product group from report summary

                Ok, thanks...I will give it a try :)