How to omit duplicate records but keep the latest one. Tried using GetNthRecord but not working.

[Hanz57]

 

 

I am trying to omit the duplicate email address but keep the last and latest one on my foundset. 

What I have tried:

After getting my foundset, I sort by email address and then by Creation Time Stamp.

Then I tried to loop through each record and compare the Email field with the next record Email Field using GoNthRecord but it is not working. 

Gurus out there, please help.

My Code

1. Sort Records [Restore; With dialog: Off] // sort email then by CreationTime
2.  
3. Go to Record/Request/Page[First]
4. Set Variable[$i;Value:1]
5. Loop
6.  
7. if [T02d_actcrm_EMAIL||id_contact||preferred||::Email_Address = GetNthRecord(T02d_actcrm_EMAIL||id_contact||preferred||::Email_Address; $i+1)
8. Omit record
9. else
10. Set Variable[$i; Value:$i+1]
11. Go to Record/Request/Page[Nesxt;Exit after last: On]
12. end if
13. End Loop

 

[comment]

I believe it should be sufficient to do:

# SORT BY CATEGORY
Sort Records [ Restore; No dialog ]
Go to Record/Request/Page [ First ]
Loop
  Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) ]
  If [ YourTable::Category = GetNthRecord ( YourTable::Category ; Get ( RecordNumber ) + 1 ) ]
    Omit Record 
  Else
    Go to Record/Request/Page [ Next ]
  End If 
End Loop

 

However there is a faster way: define a summary field sCount that counts some field in your table that cannot be empty. Then make the script do:

# SORT BY CATEGORY
Sort Records [ Restore; No dialog ]
Go to Record/Request/Page [ First ]
Loop
  Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) ]
  Omit Multiple Records [ GetSummary ( YourTable::sCount ; YourTable::Category ) - 1 ] 
  Go to Record/Request/Page [ Next ]
End Loop

 

Edited November 26, 2020 by comment