3 Replies Latest reply on Dec 11, 2013 10:43 AM by philmodjunk

    Avoiding duplicates when bringing in new records

    rickhurst

      Title

      Avoiding duplicates when bringing in new records

      Post

      I'm importing records (actually through Outlook Manipulator, but this would could apply to many issues of bringing records in). The way it is set up now, when I run my script, I get duplicate records each time I run it. I would like to have a test set up so that if the record already exists, it doesn't bring it in. Here's the specifics:

      They are calendar items. My calendar table has a record ID of course, typical start date, end date, etc, etc. Also have a field called "Outlook ID" which is a unique number linking the calendar events in Outlook to filemaker.

      I also have non-outlook calendar events in my table, so often a record will be blank in the "Outlook ID" field because there is no relationship there, so I need to allow for that field being blank in records.

      I believe that I would need to set up some kind of self-join relationship, i.e. Calendar Table first occurrence linked somehow to a second occurrence. I'm not sure exactly how to link them and what script steps to avoid the duplication.

      I'm working with FM Pro 12.


      Any help would be greatly appreciated.

        • 1. Re: Avoiding duplicates when bringing in new records
          RickWhitelaw

               I believe if you have a field set up to validate as unique, then  this would disallow the importing of duplicate records. Typically this would be the PK which should be auto entered serial and set to validate as unique always.

          • 2. Re: Avoiding duplicates when bringing in new records
            rickhurst

                 That is true, but with my issue, the incoming data does not have the pk number assigned to it.  That happens when the record comes in.  It does, however, have a unique serial number.  What you mentioned would work, but I have other records that have a blank in the serial number field.  In order for the table to interact with other processes I have set up, the fields in those records without serial numbers have to stay blank.

                 Because a blank field in not unique, that solution won't work.  I did try that, had problems, then I realized the blanks are the problem.

            • 3. Re: Avoiding duplicates when bringing in new records
              philmodjunk

                   I don't see why the blanks would be a problem. I just created 5 new, totally blank records in test file where the single defined field is set to only allow unique values.

                   Or do you have problems during import when this field is blank?