3 Replies Latest reply on Nov 8, 2011 9:29 AM by philmodjunk

    Script Help - Duplicate records

    truji_

      Title

      Script Help - Duplicate records

      Post

      I have the following query that creates records in the SEDAC table based on students eligibility. However, a student may have more than 1 IEP with the same 'requirements' but a different meeting date. The IEP I need to report is the one closest to 8/1/2011.

      i.e. a student named xyz has 2 IEPs'. 1 with a date of 2/1/2011 and the other with a date of 6/7/2011. I need the result to only show the records of the 6/7/2011 IEP. Any help is appreciated. Thanks

      • Set Field [ SEDAC::StartDate; "8/2/2010" ]
      • Set Field [ SEDAC::EndDate; "8/1/2011" ]
      • Go to Layout [ “IEP Page 1: Cover Page” (IEP_CoverPage) ]
      • Enter Find Mode [ ]
      • Set Field [ IEP_CoverPage::cMeetingDate; SEDAC::StartDate & "..." &SEDAC::EndDate ]
      • Set Field [ Students::o_SpecEd; "yes" ]
      • Set Field [ IEP_CoverPage::ReasonForMeeting; "Determine Eligibility" ]
      • Perform Find [ ]
      • Sort Records [ Specified Sort Order: IEP_CoverPage::IEPID; ascending ] [ Restore; No dialog ]
      • Go to Record/Request/Page [ First ]
      • Loop
      • Set Variable [ $SASID; Value:IEP_CoverPage::SASID ]
      • Set Variable [ $IEPID; Value:IEP_CoverPage::IEPID ]
      • Go to Layout [ “SEDAC” (SEDAC) ]
      • New Record/Request
      • Set Field [ SEDAC::SASID; $SASID ]
      • Set Field [ SEDAC::IEPID; $IEPID ]
      • Go to Layout [ “IEP Page 1: Cover Page” (IEP_CoverPage) ]
      • Go to Record/Request/Page [ Next; Exit after last ]
      • End Loop
      • Go to Layout [ “SEDAC” (SEDAC) ]

        • 1. Re: Script Help - Duplicate records
          philmodjunk

          Would "date closest" mean the earliest meeting date not already in the past?

          If so....

          Use:

          Set Field [ IEP_CoverPage::cMeetingDate; Get ( CurrentDate ) & "..." &SEDAC::EndDate ]

          You may then need to use Sort your records in ascending order by meeting date and omit all records except the first one found.

          • 2. Re: Script Help - Duplicate records
            truji_

            Meeting Date closest to 8/1/2011 - so I'm looking at dates in the past.

            I cant use current date because all end dates are before 8/1/2011.I need to report only the IEPs whose meeting date is closest to 8/1, even if they have multiple IEPs. 

            • 3. Re: Script Help - Duplicate records
              philmodjunk

              In that case, sort your found set by this date field in ascending order and omit all but the first record in the found set this produces.

              If [ get ( FoundCount ) > 1 //more than one record was found ]
                 Go To Record/Request/Page [next]
                 Omit Multiple [no dialog ; Get ( FoundCount ) - 1]
              End IF

              would omit all but the earliest record found.

              I don't think, though that this will work for you as it seems you are finding records for multiple students and need to find the earliest date for each such student you find. That's a lot messier but can be done with a script that omits the extra recrords while looping through your found set.

              Sort [no dialog ;restore ] //sort first by student, then by meeting date in ascending order
              Go To Record/Request/Page [First]
              Loop
                 If [$StudentID = Students::StudentID // same student as previous record ]
                    Omit Record
                    Exit Loop If [Get (RecordNumber ) = Get ( FoundCount ) //stop loop if you omitted last record ]
                 Else
                     Set Variable [$StudentID ; value: Students::StudentID]
                     Go To Record/Request/Page [next ; exit after last]
                 End IF
              End Loop