2 Replies Latest reply on Mar 23, 2016 3:56 PM by clayhendrix

    Report Consecutive Absences

    clayhendrix

      I have a table ATTENDANCE that imports records from the MEMBERS table each time an event for which attendance is recorded occurs.


      This table is not fully normalized as the rest of the database is because members become inactive at times and are moved to the INACTIVE_MEMBERS table. When that occurs, I do not want their attendance records to become unrelated to the ATTENDANCE table. I want to keep permanent attendance records and counts even if the member(s) become inactive.

       

      I am trying to create a report layout that lists members who have 2 consecutive absences. Then, I'll create a report for those with 3 consecutive absences, and then 4 or more consecutive absences.

       

      I created a report that shows the members name in a sub-summary section printed above and in the body section, it lists the dated for which the member was marked absent. I have created a summary field which counts the primary key of each record in the ATTENDANCE table and that is displayed in the sub-summary section next to the members name so that the report is something like this.

       

      LAST NAME, FIRST NAME       COUNT OF ABSENCES

                          ABSENCE DATE

                          ABSENCE DATE

                          ABSENCE DATE

       

      Members with no absence dates are not on the report after I perform a find on the field presence in the table ATTENDANCE for records that equal "absent."

       

      What I am unaware of how to do is:

      1.) Define a range of dates to be included in the report

      2.) Eliminate members from appearing on the report if they have only 1 absence within the date range or they have non-consecutive absences within the date range (the very date range that I cannot define.)

       

      Please help if you can.

        • 1. Re: Report Consecutive Absences
          erolst

          clayhendrix wrote:

          What I am unaware of how to do is:

          1.) Define a range of dates to be included in the report

          1. Define two fields of type date with global storage, say, gRangeBegin and gRangeEnd

          2. Amend your Find logic:

           

          # [ perform error trapping / sanity checks: both global fields filled in, end >= begin … ]

          Enter Find Mode [ no pause ; no restore ]

          Set Field [ ATTENDANCE::presence ; "absent" ]

          Set Field [ ATTENDANCE::absenceDate ; gRangeBegin & ".." & gRangeEnd ]

          Set Error Capture [ on ]

          Perform Find

           

          clayhendrix wrote:

          2.) Eliminate members from appearing on the report if they have only 1 absence within the date range or they have non-consecutive absences within the date range (the very date range that I cannot define.)

           

          1. In the Attendance table, create a summary field “Count of:” primary key (which every table should have; if you happen to not have one, use “Count of: ” absenceDate; let's call this field sCountAll

          2. Create a “List of” summary field for Attendance::absenceDate

          3. Use the fast summary technique, along with some shortcuts:

           

          Sort Records [ Attendance::id_member; ascending Attendance::absenceDate; ascending ]

          Go to Record/Request/Page [ First ]

          Loop

            Set Variable [ $groupCount; Value:GetSummary ( Attendance::sCountAll ; Attendance::id_member /* the foreign key */ ) ]

            Set Variable [ $nextGroupAt; Value:Get ( RecordNumber ) + $groupCount ]

            Set Variable [ $isLastGroup; Value:$nextGroupAt > Get ( FoundCount ) ]

            Set Variable [ $offset; Value:0 ]

            Set Variable [ $hasConsecutiveDates; Value:0 ]

            Set Variable [ $isSingle; Value:$groupCount = 1 ]

            Set Variable [ $hasConsecutiveRun; Value:Attendance::absenceDate + $groupCount - 1 = GetNthRecord ( Attendance:: absenceDate ; $nextGroupAt - 1 ) ]

            If [ not ( $isSingle or $hasConsecutiveRun ) ]
              Set Variable [ $dateList; Value:GetSummary ( Attendance::sListOfAbsenceDate ; Attendance::id_member ) ]

              Set Variable [ $i; Value:0 ]
             
          Loop

                Exit Loop If [ Let ( $i = $i + 1 ; $i = $groupCount or $hasConsecutiveDates ) ]

                Set Variable [ $thisDate; Value:GetAsDate ( GetValue ( $dateList ; $i ) ) ]
                Set Variable [ $nextDate; Value:GetAsDate ( GetValue ( $dateList ; $i + 1 ) ) ]

                Set Variable [ $hasConsecutiveDates; Value: $nextDate = $thisDate + 1 ]

              End Loop

            End If

            If [ $isSingle or not ( $hasConsecutiveDates or $hasConsecutiveRun ) ]

              Omit Multiple Records [ $groupCount ] [ No dialog ]

              Set Variable [ $offset; Value:$groupCount ]

            End If

            Exit Loop If [ $isLastGroup ]

            Go to Record/Request/Page [ $nextGroupAt - $offset ] [ No dialog ]

          End Loop

           

          • 2. Re: Report Consecutive Absences
            clayhendrix

            Both worked! Thank you!

             

            Do you have a resource to which you would point me to so that I can learn how to create scripts like the second script?

             

            I believe that is sorts by member, then by absence date.

            Then it cycles through to see if any members have consecutive absences.

             

            That sounds super simple compared to what actually had to be entered.