Merging duplicate records based on date calcuation
I am a filemaker newbie looking for the right script to do the following:
I have a database with 467,000 duplicate sets of records that need to be merged with certain rules.
The pairs have the same value in field SVOTERID1. Two fields (GEN08 and PRI08) need to have the value updated from either duplicate record of each pair if the other record of the pair has a blank value (e.g. Record 1 of the duplicate pair has a value of 7, but record 2 is blank, the value of 7 would be populated into record 2; however if both records are blank the value of the field would remain blank).
In addition, if field CTY in either of the pairs has differing or blank information, the newest record by date's value would populate the CTY field, discarding the older record's value; however, if either of the pair has a blank value for the field CTY, then the value in the other record with a value, regardless of date, would populate the field. Field RDATE1 is a date in the YEARMODY format. RDATE1 will govern the replacement.
Finally I need to discard the source pairs and generate one record with the above combined values.