1 2 Previous Next 18 Replies Latest reply on Jan 30, 2013 1:47 PM by keywords

    Summary Field

    robby@brays.net

      What would cause a summery field to only total up some of the line items and not all?

        • 1. Re: Summary Field
          comment

          robby@brays.net wrote:

           

          What would cause a summery field to only total up some of the line items and not all? 

           

          More details would be useful. In general, a summary field summarizes records in the current found set (or the related set, if you are looking at a summary field in a related table).

          • 2. Re: Summary Field
            robby@brays.net

            Thanks

              Here is what I have right now.

              My set up is  orders - orders lineitems- orders- order lineitem- orderlineitemproduct.

             

            My orders layout has a portal that displays and creates new lineitems  (products) that are associated to that order.

              Each product in the products table has all the parts needed to assemble it. Ex. Part A needs 4- of these items, 10 of these items, and 3 of these items.

            I take the quantity number that is put in the lineitem, and multiply it by each of the component part numbers to get a total number of each part required to assemble the product.

              Thus is much like an invoice template where an invoice layout houses a portal of related lineitems. Now at the end a sum or total of line items is calculated.

            What I am doing is making the work order, like an invoice, and housing the related lineitems in a portal, like invoice line items, to show all the various products on the order. The order layout does have an order ID number and is the parent to the lineitems.

              Now I have a report built that groups all the orders or order # s by the start date on each order. Now I have the report show all the components needed for each order in the report.

            For instance 1/23/2013 would show order # 233 job name start date x amount of part A x amount of part B ect. Then the next order number would fall under it with the part counts for it.

            My report is currently doing this perfectly. What I CAN'T seem to get to work, is getting the sum field to add up all the part a's part B 's ect and show me at the BREAK line a total count of all the parts.

            Hopefully this makes a little more sense.

            • 3. Re: Summary Field
              comment

              robby@brays.net wrote:

               

                My set up is  orders - orders lineitems- orders- order lineitem- orderlineitemproduct.

               

               

              I am afraid that makes no sense to me. I would have expected:

               

              Orders -< LineItems >- Products

               

              or, if you also track the components required to assemble a product:

               

              Orders -< LineItems >- Products -< Quantities >- Components

               

               

               

              robby@brays.net wrote:

               

              Each product in the products table has all the parts needed to assemble it. Ex. Part A needs 4- of these items, 10 of these items, and 3 of these items.

              I take the quantity number that is put in the lineitem, and multiply it by each of the component part numbers to get a total number of each part required to assemble the product.

               

              I don't think it can work the way you describe, unless all products are made by assembling different quantities of the same one component. A true bill-of-materials solution is much more difficult to construct.

              • 4. Re: Summary Field
                robby@brays.net

                Whoops. I typed that wrong. It is order . ..  Lineitems.  . . . Products

                • 5. Re: Summary Field
                  robby@brays.net

                  Each product ID has

                  Product ID

                  Product name

                  Height

                  Width

                  Channel size, which can only be 3 different sizes and each product can only have 1

                  Channel # which is 2,3, or 4 of the above size

                  Picket size which can only 3 different sizes and each product only gets 1 size.

                  Picket quantity each product gets either 14 or 15 of the above pickets

                   

                  So lets say line item 1 in the the order form portal is product A and that lineitem calls for a total quantity of 10 units of that product.

                  I then have a total channels needed field, total picket needed field, which is a multiplication of the quantity number imputed in the lineitem field by channel # ( 2, 3, 4 ) for a total channel count.

                  Then lineitem quantity multiplied by the picket number selected, either 14 or 15, for a total picket count.

                  Now I know how many channels are needed and pickets are needed to build 10 units of that product

                    Hopefully this helps to see what I am trying to do.

                  This is all working perfectly too. I am trying to assemble a report to total all the parts of several orders in a given day.

                  • 6. Re: Summary Field
                    christinesephton

                    Why not create a field in the orders table and use the sum function instead?

                     

                    This should help resolve your issue.

                    • 7. Re: Summary Field
                      comment

                      robby@brays.net wrote:

                       

                      Each product ID has

                      ...

                      Channel size, which can only be 3 different sizes and each product can only have 1

                      Channel # which is 2,3, or 4 of the above size

                      Picket size which can only 3 different sizes and each product only gets 1 size.

                      Picket quantity each product gets either 14 or 15 of the above pickets

                       

                      Do I understand correctly that all your products are made out of channels and pickets only? If so, you could add two summary fields to the LineItems table - one to total  the channels (i.e. the calculation field that multiplies the LineItem's quantity by the product's number of channels) and the other to total the pickets. This way you can quickly produce a report of the components needed to produce any found set of Lineitems.

                      • 8. Re: Summary Field
                        robby@brays.net

                        Yes. That is exactly what I have. Each panel is either built from 2-4 pcs of channel, which could be 1 of three different sizes, and pickets, which could be 1 of 3 different sizes.

                          Right now I have a total field in my line items table that multiplies the quantity of panels by each of the channel and picket stored value to give me a total  and it works perfectly. What I can't get it to do now is sum each component # for each found set, which is the date because I want the report to total all the pickets and channels needed for all the work orders with same date to build. This way I can print a daily material list or weekly for that matter.

                        • 9. Re: Summary Field
                          comment

                          robby@brays.net wrote:


                          What I can't get it to do now is sum each component # for each found set

                           

                          Why not? As i said, use summary fields to total the calculation fields.

                          • 10. Re: Summary Field
                            robby@brays.net

                            Not sure, but it will not sum correctly. The number it generates is not correct. It's like it sums 1 or 2 of the found set and then ignores 2 or 3 others.

                            • 11. Re: Summary Field
                              christinesephton

                              By chance do you have running totals checked in the Summary Definition?

                              • 12. Re: Summary Field
                                robby@brays.net

                                No it is not.

                                Thnx.

                                • 13. Re: Summary Field
                                  robby@brays.net

                                  It's really got me stumped because it should work.

                                  • 14. Re: Summary Field
                                    comment

                                    Does this (attached) work for you?

                                    1 2 Previous Next