10 Replies Latest reply on Jan 23, 2012 11:25 AM by ahcho

    Complex Sums

    ahcho

      Title

      Complex Sums

      Post

      Well, maybe not that complex.

      I would like to sum the values in one table in another. For example.

      ----------------

      Purchase Order Table

      PO#

      Total

      ----------------

      Purchase Order Items

      PO#

      Item Cost

      ----------------

      There are many entries in the "Purchase Order Items" that are related to one PO#. I would like to sum all [Item Cost] related to one PO# in the "Purchase Order Table".

      Thanks,

      Aaron

        • 1. Re: Complex Sums
          philmodjunk

          Pick one:

          1) Define a calclution field in Purchase Order Table as:

          Sum ( PurchaseOrderItems::Item cost )

          2) Define a summary field that computes the total of Item Cost in the Purchase Order Items table. Put this summary field on your Purchase Orders Table based layout.

          If this is to total the items shown in a portal on the same layout where data entry can change the total thus computed, use the first option as it will update more smoothly. In that situation, the second option requires a refresh window script to update the total shown in the summary field.

          • 2. Re: Complex Sums
            ahcho

            Thanks for the quick reply.

            How do I incorperate an If statement into the Sum (PurchaseORderItems::Item cost). I would like to be able to sum only the relevant costs (via the PO#).

             

            Aaron

            • 3. Re: Complex Sums
              philmodjunk

              Please explain what you mean by "relevant costs".

              The sum function adds up the total of all related records so it will total all the values in Item Cost for a given PO. If you need to be more selective, you must either define and use a relationship that excludes the "nonrelevant" records or you add a calculation field with an If function to the line items table that returns the value of Item Cost for the "relevant" costs and is blank for those that aren't.

              • 4. Re: Complex Sums
                ahcho

                So in my purchase order table, I have the following data:

                PO#          Unit Cost

                PO14         $15

                PO14         $20

                PO14         $25

                PO99         $99

                PO99         $99

                From the Purchase order table, if I have PO14 open, I want the total field to go through the above table and sum up all the costs pertaining to PO14 and not PO99. 

                So if I was to use the sum function, it would just sum all the values up so I need to konw how to create the calculation that will filter out the non-related records.

                • 5. Re: Complex Sums
                  philmodjunk

                  If this is a calculation field defined in the purchase order table, it will sum up just the item costs for that one purchase order. It will do exactly what you have requested here. The relationship is what limits the records summed to just those linked to a given purchase order.

                  • 6. Re: Complex Sums
                    ahcho

                    Sorry for bringing up this older post. I've figured out why the sum isn't working. I've attached a database to this post called Test_Sums which contains two tables as listed above)

                    So the sum does not work if I have a Portal (with the option to create new records in the Materials table). If I switch off that option, than the sums work. Is there a work around for this problem?

                    The ultimate goal for me is to allow a user to enter in a purchase order with various items and have the total automatically update.

                    Thanks,

                    Aaron

                    • 7. Re: Complex Sums
                      philmodjunk

                      Unfortunately, you can only upload graphic files to the forum, so we cannot see your uploaded file.

                      The "allow creation..." option you describe should have no effect on whether sum does or does not work, so this statement is a tad confusing here...

                      • 8. Re: Complex Sums
                        ahcho

                        You are correct that the "allow creation" option has no effect (I must have confused myself). After starting fresh again, after summing the total for the first time, any subsequent additions to the Purchase Order Items table will not result in an update in the total field found on Purchase Order table. Is there a way to have the total field update constantly?

                        • 9. Re: Complex Sums
                          philmodjunk

                          The sum function will do that. Are you using a field of type number with an auto-enter calculation for this? That would explain the lack of updating here. If so, make it a field of type calculation and it should update correctly.

                          • 10. Re: Complex Sums
                            ahcho

                            Awesome. That worked.

                            Thanks for the help!