7 Replies Latest reply on May 8, 2013 7:11 AM by philmodjunk

    Moving Summary Data from one table to another

    ClaireCollin

      Title

      Moving Summary Data from one table to another

      Post

           I have a Summary Field as a running total in one table.  I have placed this field also on a second layout which has data from a second table. I want the running total from the first table to appear in the layout for the second table.  So far it is only giving me discrete values for records that have a value but it is not giving me a running total of those values. Is there a way to do this?

           I have tried using look-ups and calculations to pull the information from the first table into the second table but they always leave blank fields on the records that have no values rather than giving me the running total number that appears in the Summary Field of the first table.

           I hope this makes sense.

        • 1. Re: Moving Summary Data from one table to another
          philmodjunk

               What is the relationship between the two tables?

               You may need to define a second summary field with all the same settings EXCEPT that running total is not selected. The total returned will then be computed from the related set of records so this may or may not produce the total you need unless the correct relationship is used to reference the summary field.

          • 2. Re: Moving Summary Data from one table to another
            ClaireCollin

                 If I remove the running total from the summary field then I cannot separate by the Name of the product.  It just gives me a total of everything in the database.  I really need the running total so that it is cumulative over the year for each product.  This works on the table containing the Summary Field but if I add that field to another layout the running total is no longer there.

                 If I change the relationships the few Summary Field values that do appear in the second layout disappear.

            • 3. Re: Moving Summary Data from one table to another
              philmodjunk

                   This is why I asked for a description of your current relationship.

                   To get the needed totals you may need to change that relationship or even to define a new relationship in addition to your existing relationship.

                   There are also several other approaches, on that uses filtered portals and another that uses ExecuteSQL()

                   But I can't say much about these approaches until I understand the relationship that needs to exist here.

              • 4. Re: Moving Summary Data from one table to another
                ClaireCollin

                If I change the relationships the few Summary Field values that do appear in the second layout disappear.

                     I have tried various relationships and none of them give me what I am looking for.  The current relationships that I have works for all of my other fields that are not summary and bring in at least some values from the Summary Field.

                     New::Week#<>Production::Week_Year
                     New::Name<>Production::Name

                     I have never used ExecuteSQL() or Portals before so I am not sure how that works.

                • 5. Re: Moving Summary Data from one table to another
                  philmodjunk

                       You need a relationship that matches a given record in one table to just the records for which you want to compute a total in the other table.

                       In which table, new or production, have you defined the summary field?

                       Are any of these fields global or defined as unstored calculations? (These look familiar)

                       Please describe the exact total you want to see produced.

                       The total for a specific product over a specific time interval?

                       If so, describe the time interval involved.

                  • 6. Re: Moving Summary Data from one table to another
                    ClaireCollin

                         The Summary Field is in the Production table.  It is a running total.

                         My current relationship setup allows me to match to a specific record. If I change this relationship my other field become blank.

                         These fields are currently unstored calculations. Should I make them stored or use the auto-enter function?

                         I want to see the running total from the Production table appear on the New table for each Name depending on the first week entered.
                         If I have Name1 and the first week is Week12 in the New table I want to see the Summary Field from the Production table for that record, which should be the running total up to and including Week12. However, currently if there is no data in the field that is being summarized nothing will appear on the New table even though there is a value in the Summary Field on the Production table.

                    • 7. Re: Moving Summary Data from one table to another
                      philmodjunk
                           

                                My current relationship setup allows me to match to a specific record. If I change this relationship my other field become blank.

                           But you don't have to change that relationship. You can add a new and separate relationship that matches in the manner needed to get your summary totals.

                           I believe that we discussed the design of your system in another thread. All for fields cannot be unstored, but if I remember correctly, the match fields in production are unstored calculation fields and I told you in that other thread that this won't work for calculations defined in New that reference data in Production.