8 Replies Latest reply on Jan 23, 2014 8:21 AM by philmodjunk

    summing a related field

    Striker3070

      Title

      summing a related field

      Post

           So far I used count of in found sets summary fields with no issues, these are doing simple counts.  Now I need to sum the miles.  However if I put a summary field in the Activities tbl, it only counts it once, this makes sense as it's only entered once.  If I place it in the  VolunteerActivities tbl, there is nothing to sum, no miles in that table. I don't seem to get an option to sum a related field.  So it seems summary fields can only sum data in it's own table.

           In this very simple relationship, how can I get a sum of the miles a Volunteer has travelled YTD or Total miles for an activity and use it in a sub summary part?

           Thanks

            

      relationships.png

        • 1. Re: summing a related field
          philmodjunk

               Yes a summary field can only sum data in the same field in which it is defined, but from a related table, you can reference that summary field and get a total from the related records in that table. Sum ( RelatedTable::Field ) can also be used.

               So either of those methods could be used from the context of Volunteers to get a total miles figure.

               But if you want a total in the join table, Volunteer Activities, the many to one relationship will require a different approach:

               Define a calculation field that copies the Miles from Activities. Use a summary field to compute the total of that calculation field.

          • 2. Re: summing a related field
            Striker3070

                 OK, I guess I am a little brain dead today, but after hours, I can't figure this out.  I am probably over complicating it, but are you saying that in the VolunteerActivities tbl, I can put a summary field the references the Summary field in the Activities tbl?  Not sure how to do this!

                 Any SUM field I put in VolunteerActivities that references the summary field in Activities only has the one value from the one side of the relationship.

                 I suppose I could try a calc field in VolunteerActivities like fkVolunteerID * Activities::Miles.  But will the resulting calc field work in a found set or sub summary part?

            • 3. Re: summing a related field
              philmodjunk
                   

                        I can put a summary field the references the Summary field in the Activities tbl?

                   No, I mentioned using a calculation field to pull the data into the join table so that a summary field can summarize the data from the context of the Join table.

                   

                        Any SUM field I put in VolunteerActivities that references the summary field in Activities only has the one value from the one side of the relationship.

                   Which is why I described using Sum in the Volunteer table, not Volunteer Activity.

                   Define a calculation field, cActivityMiles, in Volunteer Activities with this simple calculation:

                   Activities::Miles

                   Then a summary field defined in Volunteer Activities can summarize cActivityMiles.

              • 4. Re: summing a related field
                Striker3070

                     That could not have been easier!!

                     Thank you!

                • 5. Re: summing a related field
                  Striker3070

                       After building and importing several years of data.  I've been told they only have cumulative numbers from previous years, but not the actual data.  This presents an issue because at the end of the year we have a banquet for our volunteers and give small awards based not only on this years activities they attended, but also the cumulative total over the time period since they became a volunteer.

                       Where would you put a previous count of the number of activities a Volunteer has attended?  The only place I can think of where it may work is in the Volunteers table.  That way, only one entry for each volunteer needs to be made.  Just not sure how to include that total in my sub summary Grand Total parts

                  • 6. Re: summing a related field
                    philmodjunk

                         To compute a grand total, you can use a calculation field with an expression such as: SummaryFIeld + Volunteers::CumTotal

                         A sub summary based sub total that included data from volunteers would use the GetSummary function. But is that what you really meant to ask for?

                    • 7. Re: summing a related field
                      Striker3070

                           well, yes I was asking how to include that previous count in a sub-summary.  But I was also asking if there was a better way.  Always god to check my premise.

                      • 8. Re: summing a related field
                        philmodjunk

                             What is not clear is that your sub summaries compute a total for a portion of your data, the field in volunteer would be added to all such sub totals and that does not appear to produce a meaningful sub total to me as I would think that you'd need a different value from the Volunteer table for each sub summary group of records.

                             But I am picturing different totals for the same volunteer. If you have one sub summary group for each volunteer, then you can use GetSummary to get the total shown in your summary field and then you can add it to your total from the related volunteer record.

                             Volunteer::CumTotal + Get Summary ( SummaryFIeld ; VolunteerID ) //I am assuming that your sub summary fields specify volunteerID as the "when sorted by field"