7 Replies Latest reply on Feb 5, 2014 11:59 AM by philmodjunk

    Counting item categories

    JoRo

      Title

      Counting item categories

      Post

           I'm having difficulty creating a report that will allow me to show total sales for specific categories of our products. Regular summary counts aren't working.

           I'll try to explain the scenario. We sell training manuals. We have 115 individual items or SKUs. We have 5 manuals, but each manual is available in multiple languages and multiple formats (print, CD, download, copies). I have set up a series of conditional item categories and subcategories to allow for easy data entry.

           Now I need a quarterly report that shows (on one page) how many total manuals were sold, how many were sold in each format, in each language, and in each topic. Using a summary field type, I have been able to use "the count summary" to count item category fields. This essentially counts the lines that have a particular category code. This works great for lines where only one item is purchased. However, the count summary ignores the quantity field. So if someone purchases more than one of a particular item, it is still only counted once. I have not been able to figure out a way to fix this problem. It seems to me each category match field in a line (or record) needs to be multiplied by the qty and then counted/summarized, but I cannot figure out how to do it. 

           Any ideas?

           The image I included is the report I've created. It shows 29 total products sold but only seven if you look at it by title. That is because we had an order that had 10 of one title and 11 of another title. The number of resources distributed at the top is a simple summary: total of quantity field. The individual line items below are all summary: count of category item ID fields.

      Screen_shot_2014-02-05_at_12.01.41_PM.png

        • 1. Re: Counting item categories
          philmodjunk

               Use the "Total Of" version of a summary field to summarize the Quantity field instead of using the Count Of option with your summary field.

          • 2. Re: Counting item categories
            JoRo

                 I tried that earlier, but since I'm summarizing the category and subcategory ID fields (which are numbers), using "total of" really throws off the totals because the ID numbers are all 3-digit numbers. Is there a way to get a "total of" summary from the quantity field that filters or recognizes the specific category and subcategory IDs I need?

                  

            • 3. Re: Counting item categories
              philmodjunk

                   The fact that the ID numbers are 3 digit numbers has nothing to do with this. You need to summarize the Quantity field, not the ID field.

              • 4. Re: Counting item categories
                JoRo

                     The "total of" summary field works great for my overall total because i just need number of units. But I can't figure out how to make it just total the lines with a category ID of say "100". If I could get the equation or calculation to do that, I could duplicate and edit the calculation so that it would do it for all the my categories, sub-categories, and sub-sub-categories.

                • 5. Re: Counting item categories
                  philmodjunk

                       But no formula or calculation may be needed. That's why FileMaker has Sub Summary layout parts. You sort your records to group them by category and then place the summary field into a sub summary layout part that specifies the category field as its "when sorted by" field.

                       But if you need to use the same record in more than one summary calculation due to needing to group your results in different overlapping categories, you may need to use either summary fields in filtered portals or ExecuteSQL to compute the needed totals.

                  • 6. Re: Counting item categories
                    JoRo

                         Okay, I'll work on the filtered portals idea. I'm not sure how to use ExecuteSQL. I've tried the sub summary layout parts, but as you guessed, I'm trying to sort the same records in multiple ways, so it doesn't work. Hopefully, I'll be able to figure something out.

                         Thanks for your help.

                    • 7. Re: Counting item categories
                      philmodjunk

                           What puzzles me is that you "count of" summary fields should show the same limitations as the Total of summary fields.