Help Removing Duplicates

[mhotwagner]

Hi All,

I'm pretty new to FileMaker, but I've recently taken a new position, and we use it for our primary customer database.
We're working to schedule training with the guy who set up our system, but until he gets back in touch, I've got a problem I'd like to tackle.

We use an import script he wrote to bring new classes (did I mention we're a school?) into the database, and the script used to automatically remove any duplicates that came in, preserving only the new, unique classes (based on, I believe, two variables: an id number, and a class-code that is a combination of the class info and the start date (and thus, unique)).

I'm unaware of any changes that have been made to this structure, but between the last import two weeks ago and today, something broke, and the import is now bringing in duplicate classes in addition to the new ones.  Or, more correctly, the import is no longer removing duplicate classes after it pulls in the new records.

 

I've been trying to wrap my ahead around the script in question, but I'm unfamiliar with the syntaxt.  As far as I can tell, the problem must be somwhere in here:

 

Import Records [ Target: “CLASSES_IMPORT”; Method: Add; Character Set: “Mac Roman”; Field Mapping: Source field 1 import to CLASSES_IMPORT::__KP_ClassID_Vae

  Source field 2 import to CLASSES_IMPORT::Published

  Source field 3 import to CLASSES_IMPORT::Permalink

  Source field 5 import to CLASSES_IMPORT::__KP_ClassCode

  Source field 9 import to CLASSES_IMPORT::DateStart

  Source field 10 import to CLASSES_IMPORT::DateEnd

  Source field 13 import to CLASSES_IMPORT::Notes ] [ No dialog ]

Enter Find Mode [ ]
Set Field [ CLASSES_IMPORT::CountDuplicates.c; ">0" ] New Record/Request
Set Field [ CLASSES_IMPORT::__KP_ClassCode; "=" ]
Set Field [ CLASSES_IMPORT::__KP_ClassID_Vae; "=" ] New Record/Request
Set Field [ CLASSES_IMPORT::__KP_ClassID_Vae; "vae" ] Perform Find [ ]
If [ Get ( FoundCount ) > 0 ]

  Delete All Records

    [ No dialog ]

End If

 

 

Could someone give me an explanation of just what's going on in there? Specifically, I need help understanding the section beginning with Enter Find Mode [ ]

 

Any and all help is VERY appreciated!

 

Thanks much,

 

Michae

 

 

[Wim Decorte]

That section creates 4 search requests.  After it runs the found set will be:

 

- all records where the countDuplicates fields is greater than 0

and

- all records where the __KP_ClassCode is empty

and

- all records where the __KP_ClassID_Vae is empty

and

- all records where the __KP_ClassID_Vae starts with "vae"

 

 

If there are any records found by these requests, those records get deleted.

[LaRetta]

 

… and the script used to automatically remove any duplicates that came in, preserving only the new, unique classes (based on, I believe, two variables: an id number, and a class-code that is a combination of the class info and the start date (and thus, unique)).

 

 

Hi Michae, welcome to FMForums!  :-)

 

On the duplicates that have been imported, does CountDuplicates.c show a 1 > 0?  If so, please provide the calculation used in the part I have bolded above.

 

If it is the unique portion which is failing, it may not be the find at the end at all.

 

corrected "a 1" to ">0"

Edited November 5, 2013 by LaRetta

[comment]

I see only three requests:

• records where CountDuplicates.c is > 0;
• records where both __KP_ClassCode and __KP_ClassID_Vae are empty;
• records where  __KP_ClassID_Vae starts with "vae".

 

As LaRetta says, knowing exactly how CountDuplicates.c is being calculated would help.
 

[Fenton]

I would be most suspicious of the "CountDuplicates.c" field. The ".c" is likely his way of saying that it is a calculation type field. I imagine it is testing for a few things. The result of matches to this calculation may be only a 1, or it may be a count (1, 2, etc.).

 

Since there are multiple requests, the success of ANY of them will find a record. Two of the requests are just looking for "empty" in a field. The last one is looking for the word "vae" in the same field as the request above it.

 

One thing to look it is the Import Records step itself, the "Specify import order," (button at the lower right). If any of the (many) fields are not lined up together correctly, then one (or more) of fields may be getting the wrong data imported. Look at the 3 fields which are used by those requests (glance at the others if one of these is incorrect). Be careful however, as messing up an Import would bust it.

 

Another (safer) way to look at what's wrong would be to look at those 3 fields (only 3) data on a layout. One (or more) of those fields used by the requests likely does not have the correct data in them (of the Import with duplicates). It should be visible. However, if it's the "CountDuplicates.c" one, >0, you'll still have the problem of what the heck the calculation is looking at and/or doing.

[LaRetta]

Hello Fenton!  Wonderful to see you around!!  :-)

 

"two variables: an id number, and a class-code that is a combination of the class info and the start date (and thus, unique))."

 

And I would consider, instead of using a calculation to determine duplicate after imported, stop them from coming in at all by creating a standard text field (maybe called classCodeDateUnique which concatenates the two fields (class info and start date) then set it validate unique always, those duplicate records will be ignored completely by the import, because those duplicates will fail validation. 

 

I am unsure if you can change a process now but I wanted to mention it (learned from Comment several years ago now).  And actually the validation, since it applies to a record, can also test for the empty fields.

As for mapping issues, indeed yes, particularly if this solution started pre-12 and its maps broke from spontaneous map bug.