Duplicate Detection Routing Rules

If you're on Facebook, you should join the ILLers group (https://www.facebook.com/groups/172179662942180/). Just recently, someone commented on issues with customers submitting duplicates, so I wanted to share my tips with everyone.

Type of duplicates: Loan requests from the same user AND in an active status

Process Type: Borrowing

Transaction Status: Awaiting Request Processing

Match String: t.RequestType = 'Loan' AND t.LoanTitle IN (SELECT distinct LoanTitle FROM Transactions WHERE TransactionNumber != t.TransactionNumber AND Username = t.Username AND TransactionStatus IN ('Awaiting Request Processing','Request Sent','Checked Out to Customer')) AND ISNULL(LoanTitle,'') !=''

New Process Type: Borrowing

New Transaction Status: Duplicate Review

Type of duplicates: Loan requests from the same user, with the same loan title AND same author

Process Type: Borrowing

Transaction Status: Awaiting Request Processing

Match String: t.RequestType = 'Loan' AND t.LoanTitle IN (SELECT LoanTitle FROM Transactions WHERE CreationDate >= GETDATE()-1 AND TransactionNumber <> t.TransactionNumber AND Username = t.Username AND LoanAuthor = t.LoanAuthor) AND ISNULL(LoanTitle,'') !=''

New Process Type: Borrowing

New Transaction Status: Duplicate Review

Type of duplicates: Second article request from the same user into the processing queue

Process Type: Borrowing

Transaction Status: Awaiting Request Processing

Match String: t.RequestType = 'Article' AND t.PhotoArticleTitle in (SELECT distinct PhotoArticleTitle FROM Transactions WHERE TransactionNumber != t.TransactionNumber AND Username = t.Username AND t.TransactionStatus IN ('Awaiting Request Processing', 'Awaiting Copyright Clearance')) AND ISNULL(PhotoArticleTitle,'') !=''

New Process Type: Borrowing

New Transaction Status: Duplicate Review

Type of duplicates: Second article request from the same user within 90 days

Process Type: Borrowing

Transaction Status: Awaiting Request Processing

Match String: t.RequestType = 'Article' AND t.PhotoArticleTitle in (SELECT PhotoarticleTitle FROM Transactions WHERE CreationDate>=GETDATE()-90 AND TransactionNumber<>t.TransactionNumber and Username=t.Username) AND ISNULL(PhotoArticleTitle,'') !=''

New Process Type: Borrowing

New Transaction Status: Duplicate Review

Type of duplicates: Second loan request from the same user into the processing queue

Process Type: Borrowing

Transaction Status: Awaiting Request Processing

Match String: t.RequestType = 'Loan' AND t.LoanTitle IN (SELECT distinct LoanTitle FROM Transactions WHERE TransactionNumber != t.TransactionNumber AND Username = t.Username and t.TransactionStatus IN ('Awaiting Request Processing')) AND ISNULL(LoanTitle,'') !=''

New Process Type: Borrowing

New Transaction Status: Duplicate Review

Type of duplicates: Article requests from the same user AND an identical article title in the last 540 days

Process Type: Borrowing

Transaction Status: Awaiting Request Processing

Match String: t.RequestType = 'Article' AND t.PhotoArticleTitle IN (SELECT PhotoArticleTitle FROM Transactions WHERE CreationDate >= GETDATE()-540 AND TransactionNumber <> t.TransactionNumber AND Username = t.Username) AND ISNULL(PhotoArticleTitle,'') !=''

New Process Type: Borrowing

New Transaction Status: Duplicate Review

For this rule, 540 was the number of days that this user wanted to check. If you wanted to check for the last 30 days, change 540 to 30. If you wanted to check for a year, put 365, etc.

 Some additional details and keep-in-minds

• These should be entered into the Routing Rules table in the Customization Manager and will be triggered upon the entry of a new request into the indicated TransactionStatus

• Shared server sites would have to also indicate a filter of: u.NVTGC = 'sitecode' for routing rules in Document Delivery or Borrowing. Lending rules would use t.Username

• Be careful with copying and pasting any rules with quotation marks.To be safe, copy and paste any routing rule into Notepad first, to remove any hidden formatting, then copy and paste it into the Customization Manager. Also - if you see something that looks like a double quotation mark, it's likely two single ones, so check your work. 

For more information on routing rules, check out our awesome docs: https://support.atlas-sys.com/hc/en-us/articles/360011908893-Configuring-Routing-Rules