2 Replies Latest reply on Aug 16, 2009 1:51 PM by comment_1

    Merging duplicate records based on date calcuation



      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.


      Please help!




        • 1. Re: Merging duplicate records based on date calcuation

          Let me try to state this more simply.  I want to find duplicate records based upon the field SVOTERID1 and combine the values for each field into one record.  If there is no value in the field for the newest record as determined by field RDATE 1 the value from any iteration of the record would populate the field; if there is a value in the field for multiple iterations, then the newest record determines the value for the field.


          Could this help someone write a script for me?




          • 2. Re: Merging duplicate records based on date calcuation

            You could try something like this:

            1. Backup your file.

            2. Sort your records by SVOTERID1 and RDATE1, descending, so that the newest record is the first one in each pair.

            3. Click in the field you want to process, say CTY, and select Replace Field Contents... with the following calculation:

            Case (
            Mod ( Get ( RecordNumber ) ; 2 ) and IsEmpty ( CTY ) ;
            GetNthRecord ( CTY ; Get ( RecordNumber ) + 1 ) ;


            4. Repeat step 4 for the rest of the fields (don't forget to change the references to the currently processed field).


            5. Export your records, grouped by SVOTERID1.