8 Replies Latest reply on Jun 21, 2012 2:45 AM by thirdsun

    Showing a summary field from a related table.

    gobbles

      I think I am just doing something silly, but I can't for the life o me figure out what I am doing wrong.

       

      My databse has Two tables in a One to Many relationship. Client to Appointments

       

      I have a report that shows a series of subsummary fields from the Appointments table. At the bottom of the page is a trailing Grand summary with some totals.

       

      I have a total calculation from the Clients Table I would like to also appear on the same page. It continues to stubbornley show "0" when I generate the report. Is there something about trailing grand summaries that mean I can't have this total in there? How would I show summary fields from different tables ont he same page?

        • 1. Re: Showing a summary field from a related table.
          Malcolm

          I have a report that shows a series of subsummary fields from the Appointments table. At the bottom of the page is a trailing Grand summary with some totals.

           

          I have a total calculation from the Clients Table I would like to also appear on the same page. It continues to stubbornley show "0" when I generate the report. Is there something about trailing grand summaries that mean I can't have this total in there? How would I show summary fields from different tables ont he same page?

           

          For summary fields the primary parameter is the found set. For a summary field placed onto a related table occurrence layout the primary parameter is not the current found set. Its primary parameter is the related record set determined by the relationship.

           

          You may put summary fields from different tables onto the same page but you unlikely to get the results you expect. A summary field from a different table only knows one found set, that is, the group of records related to the current record. This probably doesn't reflect the sub-summary grouping within the current table.

           

          In a leading subsummary part the found set will reflect the relational conditions from the first record in the set. In a trailing subsummary part the found set will reflect the relational conditions from the last record in the set.  These could be very different found sets.

           

          A summary field in a trailing grand summary part will only reflect the relational conditions from the last record in the set. Similarly, a leading grand summary will use the first record in the set to establish the related record set.

           

           

          Malcolm

          1 of 1 people found this helpful
          • 2. Re: Showing a summary field from a related table.
            gobbles

            That is helpful Malcolm, however, how do I go about getting that total onto the same layout as that from the related table?

             

            Do I have to do something cheesy with global fields?

             

            I should point out, all my totals are based of the same found set.

            • 3. Re: Showing a summary field from a related table.
              gobbles

              This is probably not the most elegant solution but, in case it is of use to others:

               

               

              I had a script go to a Client layout and search by the same parameter then created a variable that I passed to a Global Field that I then put in the grand summary.

               

              I made sure I cleared the value in the Global field as a cleanup.

               

              There surely must be a cleaner way to do this.

              • 4. Re: Showing a summary field from a related table.
                comment

                gobbles wrote:

                 

                I have a total calculation from the Clients Table I would like to also appear on the same page.

                 

                More details would be useful, IMHO.

                • 5. Re: Showing a summary field from a related table.
                  gobbles

                  That sentence was a little vague sorry.

                   

                  I have a summary field that holds a count of how many Clients have have made more than one appointment. I would like it to appear on the same Layout as a some summary field from my Appointment table that Counts the total number of appointments.

                   

                  I did manage to find a way to do it with a variable and a global field but hopefully there is a more elegant solution?

                   

                  thanks

                  • 6. Re: Showing a summary field from a related table.
                    comment

                    gobbles wrote:

                     

                    I have a summary field that holds a count of how many Clients have have made more than one appointment. I would like it to appear on the same Layout as a some summary field from my Appointment table that Counts the total number of appointments.

                     

                    I am afraid it's still as vague as before. Perhaps an example using actual numbers, e.g. Adam has two appointments, Betty has one and Cecil has three and we want the report to look like this: ...

                    • 7. Re: Showing a summary field from a related table.
                      Vaughan

                      Instead of summary fields, use calculation fields through relationships to count the appointments related to each client. These calculation fields can then be pipelined around the solution.

                      • 8. Re: Showing a summary field from a related table.
                        thirdsun

                        As mentioned by Malcolm you always have to remember that the relation decides which related records to summarize. In fact this can be very flexible and I prefer summary fields to calculations. That way you have one field (the summary field) that can aggregate data in very flexible and dynamic ways that you define simply by adding new relations. Calculations on the other hand are less flexible and often need to be defined several times depending on the context of desired result.

                         

                        Let's focus on your example of CLIENTS and APPOINTMENTS. I don't know what data you want to summarize, but let's assume have the following fields:

                         

                        CLIENT

                        -----------

                        id_client

                        name

                        ....

                         

                        APPOINTMENT

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

                        id_appointment

                        id_client

                        date

                        start_time

                        end_time

                        duration // = end - start

                        sum_duration // sum of duration and the field we need to get all kinds of totaled durations

                         

                        If I understand you correctly you want to show sums in the Client layout - for the sake of this example let's still assume it's the total duration you're interested in.

                        For the total duration of all appointments of a single client you need the following relation:

                         

                        CLIENT::id_client ----- = ----- client_to_APPOINTMENT___by_id_client::id_client        //just my naming scheme

                         

                        Now if you display the field "client_to_APPOINTMENT___by_id_client::sum_duration" on the client layout you will see the total duration of all appointments of the currently selected client.

                         

                         

                        Let's assume you want the total of all appointments by all clients regardless of which client you currently show:

                        CLIENT::id_client ----- X ----- client_to_APPOINTMENT___X::id_client        //now every APPOINTMENT is matched

                         

                        client_to_APPOINTMENT___X::sum_duration will show the desired value.

                         

                         

                        Furthermore you could add global fields like date_range_from and date_range_to to the client table and setup a relation to APPOINTMENTS in order to fine-tune the selection of records you want to sum up.

                        Additionally I use 2 fields that are always set to 1 and 0 (or true and false) in all of my tables in order to match related records based on boolean fields like "active?", "completed?", "paid?" and so on - the advantage is that you can re-use those 1 and 0 fields - it's better than creating text fields, e.g. one with the constant value "active", just to match records from related tables. Sooner than later your table would be full of global helper fields that don't represent any real data.