6 Replies Latest reply on Apr 17, 2015 11:20 PM by jatowle

    Sum of parts

    jatowle

      hi all,

      i have a bill of material that has thousands of parts on many sub assemblies and assemblies. I would like to get a summary of the common parts across the different assemblies much like a pivot table in Excel is ther anyway of doing this in file maker?

       

      thanks

      jeff

        • 1. Re: Sum of parts
          mikebeargie

          Do you have a visual example of what you are trying to achieve? That might make it easier to understand.

           

          There are numerous ways to "pivot" a table view, like virtual list, or using a product like CCPivot, but it sounds like you're just looking for a trailing grand summary.

          • 2. Re: Sum of parts
            jatowle

            Hi mike here is a image of one of my layouts as you can see i have a mark number w1015 used across many different Asssy Numbers. what i would like to ultimitly do is see the total quantity fro each diffrent mark that is listed on this BOM and then also see the total number of the same Mark that are on Purchase orders and total number of same mark that have been received and what is also in stock for the same part. if i coud do all this in a deffernt layout that would be good see second image as a sample

             

            bom.PNG

            Capture.PNG

            • 3. Re: Sum of parts
              mikebeargie

              This can be done a number of different ways in filemaker.

               

              For example, from w1019 in an "Items" table, you could calculate the quantity received in inventory as such:

              ExecuteSQL("SELECT SUM(quantity) FROM inventory WHERE itemID = ? and flag_received = \"1\"" ; "" ; "" ; Items::ID )

              ExecuteSQL is a great technique because it doesn't require layouts or relationships in order to function.

               

              Another method would be to subsummarize on the existing layout you screenshot above, by AssyNumber, and drop a summary field in the subsummary part that will show the summary for each AssyNumber grouping. Here's a quick video to demonstrate that:
              FileMaker Pro 12 Subsummary Reports Part 1 - YouTube

              This would probably be the easiest for you to understand and use for your immediate need.

               

              Yet a third option would be to create a virtual list report that assembles your "Qty Reqd", "Qty on PO", "Qty Rec", "Qty In Stock", and displays that "virtual" info on a special report layout/table. This technique is a little complex, but gives you total control, and is a great thing to use wherever you don't want permanent layouts or aggregate fields everywhere.

              • 4. Re: Sum of parts

                YOu can create a summary line for anything and delete the body line and all those items will provide their totals, just as they would if you listed all of the items in the body. You can use multiple summary rows and different sorts which will only show the lines that have been sorted. This gets interesting....

                • 5. Re: Sum of parts
                  jatowle

                  Thanks mike,

                  i will try these suggestions and let you how it works out for me

                   

                  jeff

                  • 6. Re: Sum of parts
                    jatowle

                    OH and thanks jack I will try your suggestion as well

                     

                    jeff