1 Reply Latest reply on May 26, 2016 7:54 AM by erolst

    Use "GetSummary" with field in different table

    wladdy

      A database records the lengths of various book chapters, and the time it takes for a reader to read the chapters.

       

      Table "Chapters" has three fields:

      pk_ch_ID (primary key)

      WordCount (integers)

      WC_sum (summary field with the running total of WordCount)

       

      Table "Sessions" has five fields:

      pk_sess_ID (primary key)

      fk_ch_ID (foreign key)

      minutes (integers)

      minutes_sum (summary field with the running total of minutes)

      WPM (calculating words-per-minute: WordCount/minutes)

       

      "Chapters" and "Sessions" are related through a one-to-many relationship between "pk_ch_ID" and "fk_ch_ID", which are also the fields sorting their respective tables in the relationship.

       

      I want in "Sessions" a calculated field called "average_wpm" with an expression calculating "chapters::WC_sum" divided by "minutes_sum".

      For example:

       

      FieldRecord 1Record 2
      pk_ch_ID12
      WordCount10001200
      WC_sum10002200
      pk_sess_ID12
      fk_ch_ID12
      minutes1015
      minutes_sum1025
      WPM10080
      average_WPM10088

       

      However, before even getting into the division, I cannot find a way to properly use the "GetSummary" function in order to get "chapters::WC_sum" into a layout based on "Sessions".

       

      I tried to use "fk_ch_ID" both as the break field in GetSummary (chapters::WC_sum;fk_ch_ID) and the field sorting the layout. To my surprise, this does not work and "average_WPM" remains blank.

      I tried many other combinations, but none works as I want.

       

      Obviously, I am missing basic paradigms on the meaning of sorting (for instance, I don't get the need of a sort order for the relationship when the layout has its own), the working of summary fields and the proper use of a break field. I feel that my database is not properly designed, but I don't understand why.

       

      I would appreciate immensely links to ressources that could enlighten me in these areas, as well as a practical solution for my problem at hand.

       

      Thanks in advance! W.

        • 1. Re: Use "GetSummary" with field in different table
          erolst

          wladdy wrote:

           

          Obviously, I am missing basic paradigms on the meaning of sorting (for instance, I don't get the need of a sort order for the relationship when the layout has its own)

          Layout sorting - sort the records in the layout table

          Relationship sorting: sort related records

           

          wladdy wrote:

          working of summary fields

          Apply a statistical function to a field across all records of a found set (or a related set)

           

          wladdy wrote:

          the proper use of a break field.

          You need a break field to define a group; e.g. all records for 2015, with summarized values per month only makes sense if you sort the records by month, so FM knows where to start/restart the calculation.

           

          And that is why the break field of a sub-summary part must appear in the sort order to get accurate results (and make that part appear in the first place).

           

          wladdy wrote:

           

          However, before even getting into the division, I cannot find a way to properly use the "GetSummary" function in order to get "chapters::WC_sum" into a layout based on "Sessions".

           

          I tried to use "fk_ch_ID" both as the break field in GetSummary (chapters::WC_sum;fk_ch_ID) and the field sorting the layout. To my surprise, this does not work and "average_WPM" remains blank.

          I tried many other combinations, but none works as I want.

           

          You are using the wrong method here. GetSummary() is used to get the value of a summary field per group, where group is (as described above) defined by the sort order.

           

          In your case, you could just create a calculation field to "pull in" the chapter word count (simply a reference to the related field), so each session knows the word count of its chapter.

           

          Now you can treat that calc field like a native field, e.g. create a summary field that totals it – and then use that summary field in your calculations – e.g.

           

          GetSummary ( sSumOfWords ; fk_chapterID ) / GetSummary ( sSumOfMinutes ; fk_chapterID )

           

          which you can put into a sub-summary part with fk_chapterID as the break field.