4 Replies Latest reply on Feb 20, 2011 9:00 AM by PecCars

    How can I transfer summary data from one table to the other ?

    PecCars

      Title

      How can I transfer summary data from one table to the other ?

      Post

      Consider layout LA, using table TA with fields DateAsFirstOfTheMonth and DateOfOccurrence. I have created in layout LA a summary of count by DateAsFirstOfTheMonth.  Consider table TS that collects statistics and is "unrelated" to table TA. Table TS contains the fields TS-DateAsFirstOfTheMonth and CountOfOccurrence. How can I populate table TS with the summary data from table TA ?   I had thought of using getsummary in the field definition of CountOfOccurrence, it just doesn't work.  Am I doing something wrong ? Is there any other way of populating TS from TA ?

      BTW, populating the data should take place in real time.

        • 1. Re: How can I transfer summary data from one table to the other ?
          philmodjunk

          First, make the tables related:

          TA::DateAsFirstOfTheMonth = TS::DateAsFirstofTheMonth

          Then you can either define a field in TS as Count ( TA::DateasFirstofTheMonth )

          Or you can just pull the summary field from TA onto this TS based layout.

          Either way, you get the count of TA records on your TS based layout.

          It's also possible to set up a summary report based on a TA layout that lists the count for each month, but leaves out the individual record entries. That approach can eliminate the need for the TS table--at least for the purposes of this report--though you may have other reasons for including this part of the table.

          The only difference between the two approaches is what you will see if there are no records in TA for a given month. With the TS layout and the count function, you'd see a zero. With the related summary field or a summary report based on the TA layout the entry is completely missing.

          • 2. Re: How can I transfer summary data from one table to the other ?
            PecCars

            Phil,

            your approach one makes more sense, except that TA:DateAsFirstOfTheMonth is a calculation field and apparently is not stored in the first place and because of that cannot be referenced by TS in order to count. TA:DateAsFirstOfTheMonth is actually calculated from TA:DateOfOccurrence. Is there any way that I can make TA:DateAsFirstOfTheMonth a "storable" field so that I can use it for the count in TS ?

            Your approach two is not so interesting, as TS is a basis statistics table (and not a report). Several layouts will be created upon TS being populated as a table and not as a layout.

            • 3. Re: How can I transfer summary data from one table to the other ?
              philmodjunk

              I see no reason in what you've posted so far that would prevent you from defining TA::DateAsFirstOfTheMonth as a stored calculation. Is the date it references stored in a related table? If so, you may need to use a field with a looked up value auto-enter field option to pull the date into the TA table for use in this calculation. Since DateofOccurrence is not a date that should be subject to frequent changes, this shouldn't be a major issue if you have to go that route. You can use a script trigger on the original field to automatically update this looked up value field any time the original value is updated just to keep the values synched.

              Several layouts will be created upon TS being populated as a table and not as a layout.

              You may find that you can do this with option 2. For example, if you set up a summary report with a sub summary part for the sub totals but delete the body part, you can get a "table like" view of the data with one row of data for each month.

              • 4. Re: How can I transfer summary data from one table to the other ?
                PecCars

                A bit tricky but it works now. First: create a self-join on DateAsFirstOfTheMonth. Second: Create a relationship between TA and TS based on DateAsFirstOfTheMonth. Third: you need to ensure that the table are sorted appropriately: you can do this in the relationship dialog.