Duplicate Detection Routing Rules
Patrons sometimes submit the same request more than once. Routing rules can catch these as they come in and hold them in a review queue for staff to look at, instead of sending them out to a lender.
There are a few common reasons to put these rules in place:
- A submission the patron wasn't sure went through. If a browser times out or the confirmation page doesn't load, a patron may refresh the form and submit the same request a second time without realizing it.
- A request that is already in process. When a patron hasn't seen an update in a while, they may forget they already submitted the request and place it again.
- Identifying material worth purchasing. Titles that come in repeatedly are good candidates to review for purchase rather than borrowing them again.
- Copyright review. Repeated article or chapter requests from the same publication accumulate against your copyright limits.
The rules below are samples. Use them as written, or adjust the fields and timeframes to match your workflow.
Rules in This Article
- Loans from the Same Patron in an Active Status — the patron already has that title on order or checked out
- Loans with the Same Title and Author — same title and author submitted within the last day
- A Second Loan for the Same Title — any other request from that patron for the same title
- A Second Article for the Same Title — any other request from that patron for the same article title
- A Second Article Within 90 Days — same article title requested in the last 90 days, any status
- An Identical Article Title Within 540 Days — the same check over a longer, configurable window
- Multiple Chapters from the Same Book in a Calendar Year — different chapters from one book, matched on journal title, ISSN, or ESP number
See also How These Rules Work and Things to Keep in Mind.
Duplicate Review is not a default ILLiad status. Add it to your custom queues before activating these rules, or route to a review queue you already use.
Give your duplicate rule a low RuleNo. Rules are evaluated in RuleNo order and only the first match applies, so a low number catches duplicates before other automation processes them.
How These Rules Work
Each rule triggers when a new request enters the TransactionStatus you specify. The match string then compares that request against the Transactions table to look for an earlier request from the same patron, using t. to refer to the request currently being evaluated. When the subquery finds a match, the request is routed to the review queue.
Most of these rules match on title fields, so they will only catch duplicates where the patron entered the title consistently. The ISNULL(...) !='' conditions keep requests with an empty title field from matching each other.
For general guidance on the Routing table and on writing match strings, see Configuring Routing Rules. For sample rules that solve other problems, see Example Routing Rules.
Loan Duplicates
Loans from the Same Patron in an Active Status
Flags a new loan request when the same patron already has a request for that title in an active status.
| Field | Value |
|---|---|
| RuleNo | 1 |
| RuleActive | Yes |
| ProcessType | Borrowing |
| TransactionStatus | Awaiting Request Processing |
| MatchString | See below |
| NewProcessType | Borrowing |
| NewTransactionStatus | Duplicate Review |
| RuleDescription | Routes loan requests to review when the patron already has that title in an active status. |
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,'') !=''
Adjust the statuses in the TransactionStatus IN (...) list to match the active statuses you want to check against.
Loans with the Same Title and Author
Flags a new loan request when the same patron submitted a request with the same title and the same author within the last day.
| Field | Value |
|---|---|
| RuleNo | 2 |
| RuleActive | Yes |
| ProcessType | Borrowing |
| TransactionStatus | Awaiting Request Processing |
| MatchString | See below |
| NewProcessType | Borrowing |
| NewTransactionStatus | Duplicate Review |
| RuleDescription | Routes loan requests to review when the patron submitted the same title and author within the last day. |
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,'') !=''
Change GETDATE()-1 to widen the window — GETDATE()-7 for a week, GETDATE()-30 for a month.
A Second Loan for the Same Title
Flags a new loan request when the same patron has any other request for that title, in any status.
| Field | Value |
|---|---|
| RuleNo | 3 |
| RuleActive | Yes |
| ProcessType | Borrowing |
| TransactionStatus | Awaiting Request Processing |
| MatchString | See below |
| NewProcessType | Borrowing |
| NewTransactionStatus | Duplicate Review |
| RuleDescription | Routes loan requests to review when the patron has another request for the same title. |
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,'') !=''
The status condition in this rule is written t.TransactionStatus, which refers to the request being evaluated rather than the earlier one. Because the rule only triggers on Awaiting Request Processing, that condition is always satisfied, so the rule matches on patron and title alone. To narrow it to earlier requests in particular statuses, remove the t. prefix, as in Loans from the Same Patron in an Active Status.
Article Duplicates
A Second Article for the Same Title
Flags a new article request when the same patron has any other request for that article title, in any status.
| Field | Value |
|---|---|
| RuleNo | 4 |
| RuleActive | Yes |
| ProcessType | Borrowing |
| TransactionStatus | Awaiting Request Processing |
| MatchString | See below |
| NewProcessType | Borrowing |
| NewTransactionStatus | Duplicate Review |
| RuleDescription | Routes article requests to review when the patron has another request for the same article title. |
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,'') !=''
The status condition in this rule is written t.TransactionStatus, which refers to the request being evaluated rather than the earlier one. Because the rule only triggers on Awaiting Request Processing, that condition is always satisfied, so the rule matches on patron and title alone. To narrow it to earlier requests in particular statuses, remove the t. prefix, as in Loans from the Same Patron in an Active Status.
A Second Article Within 90 Days
Flags a new article request when the same patron requested that article title in the last 90 days, regardless of status.
| Field | Value |
|---|---|
| RuleNo | 5 |
| RuleActive | Yes |
| ProcessType | Borrowing |
| TransactionStatus | Awaiting Request Processing |
| MatchString | See below |
| NewProcessType | Borrowing |
| NewTransactionStatus | Duplicate Review |
| RuleDescription | Routes article requests to review when the patron requested that article within the last 90 days. |
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,'') !=''
An Identical Article Title Within 540 Days
The same check over a longer window.
| Field | Value |
|---|---|
| RuleNo | 6 |
| RuleActive | Yes |
| ProcessType | Borrowing |
| TransactionStatus | Awaiting Request Processing |
| MatchString | See below |
| NewProcessType | Borrowing |
| NewTransactionStatus | Duplicate Review |
| RuleDescription | Routes article requests to review when the patron requested an identical article title within the last 540 days. |
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,'') !=''
540 days was the timeframe one library wanted to check. If you want to check the last 30 days, change 540 to 30. For a year, use 365, and so on.
Book Chapter Duplicates
Multiple Chapters from the Same Book in a Calendar Year
Flags a book chapter request when the same patron already requested a different chapter from the same book this calendar year. This one is aimed at copyright review, since chapter requests from a single book accumulate against your copyright limits.
Unlike the title-matching rules above, this rule identifies the book by journal title, ISSN, or ESP number — whichever of those is populated. It ignores cancelled requests, skips matches on the same chapter title, and also catches earlier requests entered with a document type of Book that have a chapter title filled in.
The rule also requires the two requests to share the same publication year (PhotoJournalYear) when both have one recorded, which keeps the match on a single edition; the check is skipped when the year is blank on either request. If publication year isn't entered consistently at your site, this condition can cause a genuine duplicate to be missed.
To drop the requirement, remove the entire condition from the match string, including the leading AND:
AND (ISNULL(t.photojournalyear,'') = '' OR ISNULL(t2.photojournalyear,'') = '' OR t2.photojournalyear = t.photojournalyear)
Removing only the t2.photojournalyear = t.photojournalyear comparison will not work. The two ISNULL terms are what let requests with an unknown year through; without that comparison, the condition requires one of the years to be blank, and the rule stops matching pairs where both requests have a year recorded.
| Field | Value |
|---|---|
| RuleNo | 7 |
| RuleActive | Yes |
| ProcessType | Borrowing |
| TransactionStatus | Awaiting Request Processing |
| MatchString | See below |
| NewProcessType | Borrowing |
| NewTransactionStatus | Duplicate Review |
| RuleDescription | Routes book chapter requests to review when the patron requested another chapter from the same book this calendar year. |
t.documenttype = 'Book Chapter' AND EXISTS (SELECT 1 FROM transactions t2 WHERE t2.transactionnumber <> t.transactionnumber AND t2.username = t.username AND t2.processtype = 'Borrowing' AND t2.transactionstatus NOT LIKE 'Cancelled by %' AND datepart(year, t2.creationdate) = datepart(year, getdate()) AND (t2.documenttype = 'Book Chapter' OR (t2.documenttype = 'Book' AND ISNULL(t2.photoarticletitle,'') <> '')) AND (ISNULL(t.photoarticletitle,'') = '' OR ISNULL(t2.photoarticletitle,'') = '' OR t2.photoarticletitle <> t.photoarticletitle) AND (ISNULL(t.photojournalyear,'') = '' OR ISNULL(t2.photojournalyear,'') = '' OR t2.photojournalyear = t.photojournalyear) AND ((ISNULL(t.photojournaltitle,'') <> '' AND t2.photojournaltitle = t.photojournaltitle) OR (ISNULL(t.issn,'') <> '' AND t2.issn = t.issn) OR (ISNULL(t.espnumber,'') <> '' AND t2.espnumber = t.espnumber)))
To check a rolling twelve months instead of a calendar year, replace the datepart(year, t2.creationdate) = datepart(year, getdate()) condition with t2.creationdate >= getdate()-365.
Things to Keep in Mind
- Add Duplicate Review, or whatever you name your review queue, to your custom queues before activating a rule that routes to it. See Creating Custom Queues.
- Enter these rules in the Routing table in the ILLiad Customization Manager under System > Routing > Routing. The RuleNo values above are examples only — assign numbers that place the rule where you want it in your existing rule order, and keep it early so duplicates are caught before other rules process them.
- Shared server sites need an additional site filter. Add
u.NVTGC = 'sitecode'for Borrowing and Document Delivery rules, ort.Usernamefor Lending rules. See Configuring Routing Rules for more on scoping a rule to a single site. - Be careful copying and pasting rules that contain quotation marks. Paste the rule into Notepad first to strip any hidden formatting, then paste it from Notepad into the Customization Manager. If a character looks like a double quotation mark, it is usually two single quotes — check your work before saving. Only single quotation marks should be used in a match string.
- Test each rule against a small number of requests before relying on it. A rule that matches too broadly will fill your review queue with requests that are not actually duplicates.