3 Replies Latest reply on Jul 8, 2013 6:34 AM by philmodjunk

    Combining same products by product number and adding their quantities in a Portal

    okrampane

      Title

      Combining same products by product number and adding their quantities in a Portal

      Post

           Hi,

           I am trying to make something that will help me make invoices automatically based on selected delivery notes.

           I have a range of 50+ products, which i deliver to one company each day. Each day i write a delivery note by hand, where i put on the product ID, product name and delivered quantity. I have now successfully put all my products in filemaker and am now able to write these notes in filemaker.

           Here comes the tricky part. Every month i have to make an invoice in which i include all of the product delivered in the last month. I have made a checkbox where i mark from which delivery notes i want the data to be collected and shown on my invoice. But as you can see, when i delivered the same product more than once in the past month, filemaker shows more than one record of it. I want it to combine same products in one row and just add together their total quantity.

            

           so if i have

           Product A   100 pieces
           Product B     50 pieces
           Product A      20 pieces

           i want filemaker to show

           Product A   120 pieces
           Product B   50 pieces

            

           I really hope you can help, i have been trying to resolve this problem for a week now. I have searched google a million times but no success.

           Thanks :)

      Screen_Shot_2013-07-07_at_17.08.14.png

        • 1. Re: Combining same products by product number and adding their quantities in a Portal
          philmodjunk

               Assuming that

               Product A   100 pieces
               Product B     50 pieces
               Product A      20 pieces

               are 3 records in your portal's related table,

               You can set up a summary report where each product is represented by a single line with a sub total for that product.

               Create a new layout based on the portal's table.

               Remove the body layout part from your layout.

               Add a sub summary part when sorted by your Product Field. Put the product fields and a summary field that computes your totals pieces in this sub summary layout part.

               Perform a find or use Go To Related Records to pull up the set of records you want for your report.

               SORT them by the same field as the "sorted by" field you specified for the sub summary part.

               Note: if you do not sort your records by this field, the layout will appear blank.

          • 2. Re: Combining same products by product number and adding their quantities in a Portal
            okrampane

                 ufffff, that sounds complicated xD

                 So this new layout based on portals table that you are talking about means i have to create a new database table? What about the sub-summary part? Is this yet another new database table? 

                 Tell me if i get this right. In the sub summary table there should be listed all the products just once and a calculation should be made to sum up the quantities of the same product from summary table?

                 Also, performing a searh by using find or Go to related records only filters all the records in a table until you do another search. I want to have my invoices archived, i want every invoice to be a separate record with a portal that shows the kinds of products i delviered and their quantities. 

            • 3. Re: Combining same products by product number and adding their quantities in a Portal
              philmodjunk

                   You do not have to create any new table if your structure is as I have described it. You will need a new layout based on your existing table that your portal references.

                   A sub summary part is a layout part created in part set up.

                   

                        i want every invoice to be a separate record with a portal that shows the kinds of products i delviered and their quantities.

                   And that is exactly the structure that you need to produce such a summary report. All Finds are temporary, but can be repeated at any time that they are needed. They have nothing to do with whether or not your data has been "archived" or not.

                   Please see this tutorial link for more details on this method. Creating Filemaker Pro summary reports--Tutorial