1 Reply Latest reply on Aug 23, 2012 10:27 PM by philmodjunk

    Summarizing related records data



      Summarizing related records data



      I have a client that has an assortment table and also has a related items table. I have a few fields in the item table that calculate a number based on whether the item is New, Revised or Repeated.


      What I am trying to do is show in a report from the assortment table data a summary (total) of all of the related items. Since the total of the items is always 1 (if the parameter evaluated is true), I just want to aggregate these items as total count columns in my report.


      To illustrate...


      The assortment table has several headers...

      ID Name DesignsNew DesignsRevised, DesignsRepeated


      The Item Table has 

      ITem ID   AsstID_fk (foreign key) IsNew IsRevised IsRepeat.


      IsNew, IsRevise, IsRepeat are 1 if the item type is New, Revise or Repeat respectively.


      What I want my report to look like (and also export to excel as a table) is


      AsstID AstName DesignsNew DesignsRevised DesignsRepeated Where


      DesignsNew, Revised and Repeated are summarizations (total) of the IsRevise, isrepeat and isnew fields from the related item table.


      Any thoughts or cheezy examples that can get me going?


      Thanks much.


        • 1. Re: Summarizing related records data

          There are two ways to get the same totals. Each approach has its advantages.

          Define  summary fields in Items that compute the totals of each of the three fields. When you place the summary field from this table on your assortment layout, it will total the data from the related records only.

          Define three calculation fields in your assortments table. Use Sum ( RelatedITems::IsNew ) type syntax to compute totals of your sets of related records.