hi all...

i have a little script that imports some data in a filemaker file and then starts cleaning it. the first thing it does it looks for empty records and gets rid of those. that works fine. the second thing it does is looking for duplicates. by marking all duplicate records with 1. then when i want to do a find on all the records marked with 1 the script stops with 101 error which states: "Record is missing". awesome. which record are we talking about?

i can see that there are a bunch of records marked with 1. i also put Sett Error Capture [on] in the beginning of the script but the script debugger still stops at the second Perform find with the 101 error...

why?

thanks...

Second Perform Find??? You either build in all you requests into the first Perform Find[ step or you use:

http://www.filemaker.com/help/Script-Steps72.html

...for the second! However isn't you method to dedupe what consensus here is at the moment.

Since a recordID is almost omnipresent in every table when dealing with relational systems, could this be exploited. You make a selfjoin relation with a linking on the combination of fields you wish to obtain uniqueness upon.

Then do you make a flagging field, just as you already have but here is it a calc'field comparing the records ID with the ID seen over the relation ... if these differ, is the flag raised. The searching on these flags is all it takes, although it's a unstored calc' it done with - the found set is now the dupes, ready for deletion in one swell swoop.

Since this ought to be done just after an import, is it important not to destroy the found set the import makes, therefore is the search most sensible to be a contrain found set, since the original presumably are dupe free anyway. But an important issue here is to allow auto-enters to update during the import!

--sd

aha... the first one is done on all empty records. to get rid of those...

the 'deduping' is very simple. i added 2 fields to the file. one of them is just a number field the other one is a global. so basically what happens is sorting the records by the field that needs deduping and then run a loop that sets the global field to the 'dedupped' field and goes down the records. if it finds that the next 'dedupped' field matched the global field than it marks the record. in this case with 1. so after that's done it just does a find on all the records marked with 1. that's it. simple. no relational stuff... it's basically there to make sure the records are not loaded twice for some reason - since it's done by a machine...

so, again - that Perform Find that looks for the 1 fields stops the script and i'm not sure why...

thanks...

Edited July 16, 200817 yr by Guest
added stuff...

weird... i just rename the "dedupped" field and then changed it in the script and now it works... go figure...

field and goes down the records. if it finds that the next 'dedupped' field matched the global field than it marks the record.

I have obviously seen your approach before, it's just ... it's not particular fast - the search in the unstored calc'field is optimized better. Well why not try to throw in you approach in the attached template and make the comparison - the contamination script in my template uses Applescript wich should be alright with your profile!

--sd

example.zip

i see.. nice. i'll try it adding the Auto-enter Serial thing... good to know.... thanks....

one big, huge drawback... unless i'm doing something wrong....

i did create the local ID number field with the Auto-enter serial but no serials are generated at the time of importing the data. and i tried both options - on creation and on commit...

tried it a few times and also deleted all the records and importing them again... no serial numbers are generated ?!?!

bit if i do new record then it does create a serial.... awesome.

Hi,

The option during import to update autoenter fields, lookups, etc. will set the auto enter fields if you wish. Unselected the fields will not be updated, as you have noticed.

HTH,

Tim

i saw that. importing manually. i don't see that option when creating a script and using the Import records command under Records....

It comes when making the mapping of the import script step and completed that part ... just after!

--sd

Do you have any Auto Enter Fields that need to be updated? The dialog doesn't show if there aren't any.

Lee

Good point Lee, this is what I mean about the omnipresence of record IDs, if there isn't provisions neither would the dialog show up.

--sd