Until Dynamics NAV 2013, the only possibility was to iterate through the table in a loop and then create a sub-loop where another instance of the same table is filtered to check for duplicates.
For example, to check for duplicate names in the Contact table, the code would look like this:
Dynamics NAV 2013 onwards, we can use queries to create a more efficient implementation of the same logic.
The solution involves that you create a query to return duplicates, and then invoke it from a method that would test the value of the query to identify if duplicates were found.
Step 1 – Creating the Query
- The query must be created with the table we want to search in as the dataitem.
- The field we want to search for must be created as a grouped field.
- Create a totaling field on the count, and add a filter for Count > 1. This ensures that only records with more than one instance of the field that we selected in the previous step are included in the query result.
- Continuing with our Contact Name example, here is how the query would look:
Step 2 – Invoking the Query to Check for Duplicates
Now that the query is created, all we need to do is to invoke the query and check if any records are returned, which would mean that there are duplicates.
Here is an alternate implementation of the FindDuplicateContact method using the query that we created: