5 Replies Latest reply on Jan 9, 2011 7:13 PM by NancySpoolman

    Find Duplicate ID, with newest start date, and mark old date to delete

    NancySpoolman

      Title

      Find Duplicate ID, with newest start date, and mark old date to delete

      Post

      I have a file which contains five fields:

      person ID

      student ID

      Eligibility Status

      Start Date

      End Date

      I want to write a script that will find the unique student ID with the most current Start Date. Many of the student ID's are duplicated.

      I can't use the counter and check duplicate calculation because it doesn't take in the date. I just don't know how to tell it to find the duplicate student ID with the most current date AND mark the other date to delete. 

      I have searched several of the duplicate posts and comments, but I just can't get it to do what I think it should.

      Thank you in advance.

      Nanc

        • 1. Re: Find Duplicate ID, with newest start date, and mark old date to delete
          njem

          If you did the counter and check duplicate method and then sorted the result by start date would that do it? If not then what else is needed?

          • 2. Re: Find Duplicate ID, with newest start date, and mark old date to delete
            NancySpoolman

            Yes, I did the counter and check duplicate, then I sorted by both ID and start date. I tried sorting by ascending and descending and no, it doesn't give me the current startDate if there are more than one entry. And, there are some who have four entries, different start dates. Is there anyway I can send the file so you know what I mean?

            Nanc

            • 3. Re: Find Duplicate ID, with newest start date, and mark old date to delete
              FentonJones

              Using a self-relationship on StudentID, a calculation test (result Number) could be:

              StartDate = Max ( self_student_id::StartDate)

              An unstored calculation using that should "mark" the last record for each student (it will show as a 1).* It would be good to be able to see it visually on the layout, before Deleting records. Good to first Duplicate the file (if it's closed), or Save a Copy As (if it's open); so you have a backup if something goes wrong during the script:

              Enter Browse Mode []
              Show All Records
              Go To Record/Request [ First ]
              Loop
              If [ StartDate = Max (self_student_id::StartDate) ]
              Omit Record  // omitting moves you to the "next" record
              Exit Loop If [ Get (RecordNumber) = Get (FoundCount) ]
              Else
              Go To Record/Request [Next; Exit after last]
              End If
              End Loop

              The reason I wrote it this way was so you could see the result. It should be only the earlier dates, having omitted the last ones. Then you can Delete Current Records.

              At least that's the theory, having written the above script manually, hence not tested.

              * It is assumed that all entries have valid dates. Because any records with empty or invalid dates would be considered "not the last", hence could be deleted. I suppose you could add that to the calculation, to allow empties to stay. But what use is that? So check your dates first.

              P.S. The records do not need to be sorted, as the self-relationship evaluates one record at a time, and does not care if they're sorted. A loop setting data into script variables, and testing that way may be slightly faster overall. But I'd have to write that in FileMaker to be sure; the self-related method is reliable even on a single record, as it does not care about the found set either.

              • 4. Re: Find Duplicate ID, with newest start date, and mark old date to delete
                njem

                First, when you do the counter & find dupe the 2nd copy of the table may need to be sorted by student ID and start date (in the relationship) so the "unique" it finds is always the most recent start date. Then you say you sorted by ID and Date. If you want to find the students among all with the most recent start date don't you want to sort either just by start date or at least start date first and then any other sort criteria? I may be misunderstanding.

                 

                • 5. Re: Find Duplicate ID, with newest start date, and mark old date to delete
                  NancySpoolman

                  Well, I did finally sort it out using the personID (Ascending) and startDate (Descending). It exported the correct start date by doing that! I couldn't believe it and I don't know why it didn't seem to work before. I must have had something wrong. Can I attached the file somehow and show you? I just hope it works when the student records are totally updated to this FRAM module so when I export this file it pulls the most recent free, reduced and standard. But, I tried it on 8 fake records with multiple records for some students, and it pulled it correctly as long as I sorted as mentioned above.

                  Nanc