Resolve Duplicate Records

When building batches, you eventually get around to the topic of identifying and resolving potential duplicates. That is, you have to deal with the eventuality that rows within your batch staging tables may conflict with existing rows within the production tables. When you run across a potential conflict, what do you do? Do you delete the row from the batch staging table? Or do you mark the row as an update row by providing a value for the PRIMARYRECORDID column? The basics of duplicate record checking within a batch type spec are covered within the section Duplicate Record Check. If you are not familiar with this topic, take some time to review this article.

The DuplicateRecordCheck tag is a child tag of the BatchTypeSpec element. The SearchListID attribute refers to a search list used for finding potential duplicates.

Figure: The SearchListID attribute refers to a search list used to find potential duplicates.

The duplicate check is performed whenever you elect to validate a batch or when the Check for duplicate constituents option is selected at commit time. The duplicate check works by calling the search list for each row added to the batch. As it iterates through the list of rows, the form field IDs from the batch type that match the form field parameters on the search list are passed to the search list as its parameters. Below is the search list that is used to check for duplicate records. Note the filter form metadata fields, corresponding stored procedure parameters, and the logic within the stored procedure. See anything interesting?

Figure: SearchListSpec performs the duplicate search.

The blue highlights within the search list above represent the value for the PRIMARYRECORDID that is passed into the search list for each batch row when the duplicate record check occurs. If this value is null, then a potential row insert will be attempted at commit time. Therefore, we need to check for the existence of potential duplicates. If the value is not null, then the user has purposely selected a food item to be updated from the batch grid user interface and no duplicate check is necessary.