n this example we are checking for duplicate value in a field “InvoiceNo” in a table.
Step 1:Create a field as Creation date&time (auto enter timestamp while a new record is added) in the same table.
Note: Your table most have a auto-enter primary key, else create that (e.g. pk_ID)
|
Step 2: Create a self join relationship between same table with the fields matching (the field value which you want to check for duplicate value) here in this e.g we want to check the duplicate value in “InvoiceNo” field. So match InvoiceNo = InvoiceNo. Sort by “Creation Date&Time” ascending order in right side table (Duplicate).
Step 3: Create a new calculation text field as “Duplicate_flag” having calculation
|
If ( not IsEmpty (InvoiceNo ) ; If( pk_ID = Duplicate::pk_ID, "", "Duplicate" ) ; "" )
You can now get the records where “InvoiceNo” is duplicated