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

    Summarizing related records data

    hokiemike64

      Title

      Summarizing related records data

      Post

      Hello, 

      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
          philmodjunk

          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.