Duplicate Checking with Workflow
When processing data such as invoices and POs, it is common to check for duplicates. Nobody wants to pay the same invoice more than once or issue 2 POs for the same items. In order to avoid that you need to know how to implement duplicate checking with workflow.
This article will show you how to perform duplicate checking using the “Search and Update” action. If we find a duplicate, we will set “DUPLICATE” to the value of “YES”. What you do with that flag afterward depends on your needs. Sometimes you cancel the item and sometimes you route the item to a different step.
For this example, we will assume we are processing an invoice. We need to see if there is another invoice in the system with the same Vendor, Invoice Number and Invoice Date.
Search and Update Action
The “Search and Update” action is used to search for Records that match a specific set of criteria. If any are found, then we can set values. If no Records are found, then no values get set. This is perfect for duplicate checking.
Add a “Search and Update” action into the proper place – Assignment Action or Button Action usually. Set the action to search for items that match the current invoice. For our example, set your values like this.
Notice we select the “Invoices” record type on top. Then we look for “Vendor” = “[Vendor]” to “[Vendor]”, “InvoiceNumber” = “[InvoiceNumber]” to “[InvoiceNumber]”, and “InvoiceDate” = “[InvoiceDate]” to “[InvoiceDate]”. If are not familiar with Variables, see the linked article below on how to use them.
We are using the FROM and TO values in case the values we are searching for are numeric or date. In those cases, we want to avoid searching for an open ended “find anything that has a value from 10 and up…” scenario. By using the same value in the TO field we avoid doing range searches and we use exact matches.
At first glance, this might look like enough for the search to work properly. However, we can still find the current invoice which will look like a duplicate. To avoid finding our current invoice with the dup check, we need to use the “Advanced Search” tab. Set the “By Record ID” field to “-[RecordID]”. This is a trick that tells the search to avoid the current Record’s ID when searching. Negative numbers in the “By Record ID” field mean NOT EQUAL and [RecordID] means the current Record’s ID so “-[RecordID]” translates into NOT EQUAL to the Current Record’s ID. By using this trick we end up searching for invoices that match the current one but do not have the same ID.
Next, we need to set DUPLICATE = YES if there is a match. For this we use the “Update” tab to set that value. We know that if there is not a match from the search that nothing will update so no match will NOT set the DUPLICATE value. If there is a match, then it will set the DUPLICATE value.
What’s Next
Now that you have a DUPLICATE field set or not set, you can look for that value in an Activation Rule in another trigger. If it is YES, then activate the trigger else don’t. What you do in that trigger there is up to you. You could just as easily cancel the workflow or assign it to someone to verify or ignore the duplicate.
Related Articles
Workflow Part 1; Introduction to the Basics
Workflow Part 2; Intermediate Configuration