portal won't delete duplicate records

[phoenix94]

Longtime listener, first time caller, finally over my head.

I've set up a self-join relationship, created a portal and scripted to delete dupes, as outlined here for FMP 6.

Yet no records are deleted.

My script:

Go to layout ["de-dupe"(LegacyTest)]

Loop

Loop

Exit loop if [ "Count ( LegacyTest 2::unique ) = 1"]

Go to portal row [select, First]

Delete portal row [No dialog]

End loop

Go to Record/Request/Page [Next; exit after last]

End loop

I'm hopeful that the problem is something that is new in 7 and only I don't know it. Any thoughts?

[-Queue-]

Check the Portal Setup. Is 'allow deletion of portal records' selected?

[phoenix94]

Sadly, yes. Wish it were that simple.

In fact, since the odds are that it IS exactly that simple, I'm attaching a sample file.

legacyTest.zip

Edited July 18, 2005 by Guest

[-Queue-]

Try zipping or stuffing it. Then put it in place of the original attachment.

[-Queue-]

Change your layout to View as Form or List. Portals do not work well on Table layouts. Then remove the quotes surrounding Count(legacyTest 2::unique) = 1, so that you are not testing with text, which will always be false. Then, change your 'unique' field to be a calculation with a text result; as an auto-enter calculation it has no value, until you modify one of the fields it references. Then, remove the 'delete related records' option from BOTH sides of your self-relationship; otherwise, deleting one record deletes all its duplicates as well. Lastly, add a Commit Records step before the Exit Loop If step, so that the record updates Count(legacyTest 2::unique) correctly and does not delete the first record's duplicate and then itself, putting FM into an infinite loop.

I would also add a Freeze Window step to the beginning, and a Go to Record/Request/Page [First] step before entering the first loop.

[phoenix94]

Thanks! the problem I knew I had was the one having to do with committing the changes, so fixing that got it moving.

The Freeze Window script step seemed to stop it completely, though I may have put it in the wrong place. I don't want to evaluate from the first record forward every time (because it's a slow process with 25K records) so I took those suggestions as extras. The frosting, if you will.

The cake was pretty good--I still have a job!

[-Queue-]

Freeze Window will not stop the script; it will only keep the window in a constant state so that you do not see it flashing during the loops, which in turn will speed up the process.

I can understand why you would not want to loop through 25K records each time, but you should be able to limit the found count significantly by first performing a find with ! in the Unique field. This will find records where the value in Unique is not unique throughout the table, i.e. an original and its duplicates; any records without duplicates will be ignored.