Jump to content

Find all Duplicate records that have the earliest Date and mark them somehow


Nancy Spoolman
 Share

This topic is 4410 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have to redo one of my database exports for our student lunch program due to the fact the Student Information System is moving the F/R fields. Everything has been going so well and I'm happy about this move. However, now when I pull this data I will get duplicate students with a start and end date (good thing). I only want the most current meal status for each student.

What is the best way to tell the database to find the duplicate student and keep only the most current start Date?

Usually you can find something that is so close to what you are doing, but this one I'm totally lost on how to tell it to get the most current date for each duplicate record. I want to be able to mark the early date and script filemaker to remove the earliest date of the duplicate record.

Fields are:

studentID

stateID

eligibility

Start Date

End Date

Basically I want a script or (calculation maybe?) to say: If studentID is duplicated, look at startDate and keep most current date.

I can't create and use the normal Counter and Check Duplicates because the duplicate doesn't always appear by the earlier date. I knew that would not work. I just don't know how to tie the duplicate studentID and dates to pull most current together.

Nanc

Link to comment
Share on other sites

I'm having a hard time following what you are trying to do.

Are you doing a search for duplicate records? (enter find, set field to !, perform find) If so, in what field are you searching for a duplicate value?

Link to comment
Share on other sites

I normally create other fields called Counter and Check Duplicates when I want to find all duplicates and delete them. But, with this one I want to keep the record that is the most current startDate. I don't know how to tell it to find the duplicate record with the most current startDate and somehow mark the other one as Duplicate and the one to keep as Unique.

Sorry if this is confusing.

Nanc

Link to comment
Share on other sites

I can't create and use the normal Counter and Check Duplicates because the duplicate doesn't always appear by the earlier date.

I am not sure what is "the normal Counter and Check Duplicates" - but if you sort your records by student ID and by date, descending, the latest record will always be the first one in the group.

---

BTW, if you export your records grouped by student ID (after sorting them as above) you will get the clean list you are after.

Edited by comment
Link to comment
Share on other sites

>I am not sure what is "the normal Counter and Check Duplicates" - but if you sort your records by student ID and by date, descending, the latest record will always be the first one in the group.

In the help menu (to weed out the duplicates, under normal circumstances) it says to create a field called Counter and define that field as a number and auto-enter serial. Then you are to create another field that is called Check Duplicates and define it as a calculation If(Counter = lunchboxFRAM5 2::Counter; "Unique"; "Duplicate"). If there are only 2 duplicates of one student, then that might work. But, I sometimes have 4 duplicates of a student, and trying to sort both student ID and date, doesn't always select the newest date field as being Unique.

Here is my file. Notice the student whose personID is 6123. The date that falls as the unique one is not the most current start date.

Nanc

Link to comment
Share on other sites

I see. Assuming you already have a self-join relationship as:

YourTable::StudentID = YourTable 2::StudentID

go into the definition of the relationship and enable the "Sort records" option on the side of YourTable 2. Sort the related records by date, descending.

Here is my file

I don't see.

Link to comment
Share on other sites

>BTW, if you export your records grouped by student ID (after sorting them as above) you will get the clean list you are after.

I finally saw this, then went into the script and sorted Ascending by Student ID, Descending by Date, then exported with that sort order and as you said, group by Student ID and exported the file to get a clean file! Now all I had to do was import that new, clean file and make sure the meal status was matched to the correct student ID and everything will work great!

I took out the check duplicate and counter because it only confused what i was doing. The Unique never was the correct date, but when you said to do the sort and then group by studentID, then it worked. I even added other records to make sure it was going to export with only current ID. Sometimes we try to do things that don't need doing and sorting descending I'd not done! Plus, I also forget about using the sort option when defining the relationship between tables! Thank you for clearing up the muddy waters!

Nanc

PS... I tried to attach the file, but it said I was not allowed to upload that file. Strange.

Link to comment
Share on other sites

This topic is 4410 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.