8 Replies Latest reply on Nov 11, 2009 2:45 PM by Karnel

    Inventory Tracking report

    Karnel

      Title

      Inventory Tracking report

      Post

      I'm using FM Pro 10 Advanced on Win XP.  I have an inventory tracking table that tracks the distribution of several clothing items that come in various sizes. I need to show at any given time the total count of each item broken down with subtotals of each size. Here are the fields in my table:


      PersonID
      ItemID
      Item Description
      Size
      Date
      Amount Checked Out
      Amount Checked In
      Balance Item – Calculation = Amount Checked In – Amount Checked Out
      Running Balance – Summary =Total of Balance Item(running with restart), when sorted by Item Description

      I have created a sub-summary report sorted by Item Description and it gives me the total for each item just fine, but I’m having trouble figuring out how to get it to break down each item by size and give me a subtotal of that.  I was thinking I need another summary field for the size and I’ve tried several different things, but can’t get it to work right. 





        • 1. Re: Inventory Tracking report
          philmodjunk
            

          You're almost there.

           

          Create an additional sub summary part sorted by size.

          Add the summary fields you already have to this new part.

          Include your size field in your sort order when you sort your records.

           

          Neat trick now possible: You can now generate reports that break things down by size or not simply by controlling the sort order. If you include your size field in the sort, you'll see the size sub totals. If you don't they disappear.

          • 2. Re: Inventory Tracking report
            Karnel
              

            I'm still having problems getting it to break down like I'm needing.  This is what I'm wanting it to look like:

             

            Person           Out            In                  Size                Balance

             

            Tommy           2                               S                    11

            Sue               1                                S                    10

                                                                                 Total Small  10

            Ben                3                               M                    20

            Tammy            5                               M                    25

            Patty                             5                M                    20

                                                                                 Total Medium 20

            Lisa                 4                               L                     10

            Lance              5                               L                      15

                                                                                  Total Large 15

            TOTAL SHIRTS - 45

             

            Tommy           2                               S                    11

            Sue               1                                S                    10

                                                                                 Total Small  10

            Ben                3                               M                    20

            Tammy            5                               M                    25

            Patty                             5                M                    20

                                                                                 Total Medium 20

            Lisa                 4                               L                     10

            Lance              5                               L                      15

                                                                                  Total Large 15

            TOTAL Jackets - 45

             

             

            I added the second sub summary and sorted by Item then Size, but it doesn't breakout the sizes.  I'm sure it's something I'm doing wrong....just doesn't want to work right.

             

            • 3. Re: Inventory Tracking report
              philmodjunk
                

              What you describe should work.

               

              Check this part of your and make sure it's set up correctly.

               

              THe part where you have "Total Small 10" should be the new sub summary part. When you check its settings it should be set with "sorted by" size, print below. Your sort order should specify first the item field and then the size field second.

              • 4. Re: Inventory Tracking report
                Karnel
                  

                OK, I checked all of the things you listed and they were all good. Then I finally figured out 2 things that I fixed/added that made it all work.

                 

                1.  I realized that I had my size field setup to be a number field, therefore when I was running the sort it was actually not sorting.........don't ask me why, dummy me! 

                 

                2.  I started looking and the only thing left that was not right on the report were the size totals (the "Total Small 10" showed "Total Small -2).  I figured out I needed one more summary field in my db.  I added a Running Balance Size - Summary = Total of Balance Item (running with restart), when sorted by size.  I put that in the size sub summary part and it now gives me all the subtotals and total I need. 

                 

                Man, those sub-summaries can be sooo tricky.  I don't think I could have figured it out without your help.  Thanks PhilModJunk!!!!  You guys are the best!

                • 5. Re: Inventory Tracking report
                  philmodjunk
                    

                  I can explain the number field issue. All filemaker sorting is based on a field's index. An index is a datastructure (a b-tree I think I read in the case of filemaker) that lists one instance of every value in way that the values can be listed easily in order. Number fields index their numeric values and that ignores any text entered in those fields.

                   

                  I don't see any reason for selecting the running total option for any summary field placed in a sub summary part. It won't produce a wrong answer, as far as I know, but it may slow the rate at which your report updates.

                  • 6. Re: Inventory Tracking report
                    Karnel
                      

                    Your absolutely right, I didn't need the running total option to get it to work.  So, what would the running total option be used for?  Just curious....

                     

                    Thanks Again!!

                    • 7. Re: Inventory Tracking report
                      philmodjunk
                        

                      I use it in "ledger" type layouts where each record is one row in the body.

                       

                      Using your fields for an example:

                       

                      Person           Out            In                  Size                Balance

                      Initial Bal                          13                                         13

                      Tommy           2                                 S                         11

                      Sue               1                                 S                         10

                      Ben               3                                 M                          7

                      Tammy          5                                  M                         2

                       

                      The right hand "balance" column would be your running total field.

                      • 8. Re: Inventory Tracking report
                        Karnel
                           Awesome! Thanks!