SCRIPT: Remove Duplicate Records Except Newest

[Steve T.]

Howdy, folks! I normally prowl the CDML/web forums but I'm new to scripts and can't figure out how to exit the LOOP I made for eliminating duplicate records but keeping the latest record created. I'm basing my script on a Jason Wood script ( JASON WOOD POST ). It's not really checking ALL FIELDS for exact duplicate records, just if a (supposed to be) unique field occurs on more than 1 record, the newest is kept and the rest are deleted.

LOOP

A find for ! in a field will find all records that do not have a unique value in that field.

[EXIT LOOP IF... Here?]

I set a global field to the value of the User Name field (my key field) in the first record.

I perform a 2nd find for records with User name = global field value

I sort (descend) to have the most recently made record occur first .

I omit the first (newest) record.

I delete all remaining records in that found set

END LOOP

Here's the script:

Go to Layout ["Main"]

LOOP

Enter Find Mode [ ]

Set Field ["User Name", " "!" "]

Perform Find [ ]

Exit Loop If ["Status(CurrentError)=401"]

Set Field ["globaltemp", "User Name"]

Enter Find Mode [ ]

Set Field ["User Name", "globaltemp"]

Peform Find [ ]

Sort [Restore, No dialog]

Omit Record

Delete All Records [No dialog]

END LOOP

My Exit Loop If... isn't working or is inappropriate/wrong syntax or something. I also tried Status(CurrentFoundCount)=0 earlier but maybe my syntax was wrong there, too. The script otherwise works great... it just doesn't exit the loop correctly.

Any advice/fixes would be appreciated.

--ST

[Fenton]

I don't believe a 2nd Find is needed. If you Sort the records by user name, you can loop the found set backwards, the default sort order being by user name, then creation order descending.

The gUserName global holds the current value to compare.

Perform Script [sub-scripts,

[Ugo DI LUCA]

Hi Steve,

I agree with Fenton about the solution to your "fix script".

I'm just wondering why you use this script while the thread you linked also includes one of the most "powerful" and easy way to do it.

Andy's technique, which is now also demoed on his Web Site, would involve a 2 line script step (Find/Delete). You'd just need a new calculated field.

I'm quite sure even Jason adopted this technique.

[Steve T.]

Hi, Fenton! Hi, Ugo! Thanx for the info. The solution I had come up with was not elegant but it used simple commands with which I was already familiar (find/sort/omit). Fenton's IF ELSE conditions are easy enough for me to follow, so I'll give it a try (SHOUT OUT TO FENTON, THANX!), but I think the other one refers to Self-joins with which I am also unfamiliar. Maybe I can grow into it, though, eh (wink & smile)?

It's kinda weird, but what I'm doing is letting users sign up for accounts via the web and I'm holding their applications/requests in a temporary db. I only want their most recent application/request to be evaluated, so before I transfer them into the Web Security Database, I want to eliminate duplicates. I doubt I'll have many duplicates (if any) and probably won't have more than a handful of records to check at a time, so I wasn't too worried about efficiency other than a good programmer's mantra of "doing it right". I guess I have to explore the tool box a bit more, eh? No more pounding nails with a wrench?

Thanx again!