1 Reply Latest reply on Mar 31, 2011 3:39 PM by philmodjunk

    Confused about Summary Fields and Reporting



      Confused about Summary Fields and Reporting


      This is a very basic question, but for some reason I just cannot figure this out.  Any help would be very much appreciated!

      I have a Doctors Table with fields: DoctorID, DoctorName.  I also have a Procedures table with fields: DoctorID, ProcedureName, ProcedureDate.  They are linked using the "DoctorID" field.  Each procedure that a doctor does is a record in the Procedures table. 

      I want to create a report for a user entered set of dates that summarizes procedure count by doctor.  For example, if the user enters starting date "1/1/2011" and ending date "1/31/2011", then I want a report that looks like this:

      StartingDate: 1/1/2011

      EndingDate: 1/31/2011

      DoctorName ProcedureName Count
      John Smith ProcedureA 20
      John Smith ProcedureB 31
      John Smith ProcedureC 15
      Bob Jones ProcedureA 51
      Bob Jones ProcedureB 24

      I only want procedures to be included in the count if "ProcedureDate" falls between "StartingDate" and "EndingDate".  How do I create this report?

        • 1. Re: Confused about Summary Fields and Reporting

          To find procedures in that date range, do a find on the procedures layout by entering:

          1/1/2011...1/31/2011 as criteria in the Procedures::ProcedureDate field.

          If the user enters these two dates into these global date fields: gDate1, gDate2, a script could perform this find like this:

          Go To Layout [Procedures]
          enter find mode [] // clear pause check box
          Set Field [Procedures::ProcedureDate ; Procedures::gDate1 & "..." & Procedures::gDate2 ]
          Set Error capture [on]
          Perform Find[]
          Sort [Restore ; no dialog] //need sort to group records by doctor, then by procedure so that sub summary part can show correct counts.

          Pleas post back if you have any other questions with how to set this up.