Breaking the User Link in the ILLiad Database

In some cases, libraries may want to break the link in ILLiad between the user and their requests. This document explains a possible way to do this and the consequences in ILLiad. This script could be modified to allow, for example, only deleting items that are at Request Finished and are a certain length of time old, or only doing this for people who request it (or who request for it not to happen).

Limitations

If you break the username link in ILLiad, you will not be able to do the following items:

• View statistical reports based on department.
• View statistical reports based on status (unless you make several new usernames to move the requests to).
• Allow patrons to review past requests via the website.
• Do any billing or tracking of billing in Borrowing.

Breaking the Link Between Users and Transactions

To break the link in ILLiad, you'll need to replace all instances of a username with a new "standard" username. If you want to retain the status of the person, you can make several standard usernames (like "Faculty", "Staff", "Undergraduate", etc) or you can just make a single username to change the username to (like "BrokenUsername"). Create the username using the ILLiad web pages or through the SQL Server Management Studio.

Once you've created the usernames, you need to change several items in the database:

• The "Username" field in the Transactions table needs to be replaced.
• The "ChangedBy" field in the Tracking table needs to be replaced.
• The "AddedBy" field in the Notes table needs to be replaced.
• The "InternalAcctNo" field in the Transactions table needs to be blanked (breaking all ties for billing).
• The EMailCopies attached to the request should be deleted.

In order to do this, you'll have to decide which transactions you want to break the link on. Once you've decided on the selection criteria, you'll take the list of TransactionNumbers and, using a SQL script or an Access link, you'll make the changes needed from the list above.

Breaking the link in this fashion will remove all ties in ILLiad between the user requesting the item and the item itself. But since the item remains in ILLiad, the Copyright tracking functionality will continue to work.

Example Script

If you wanted to be able to break the links on all requests as soon as they were finished by changing the Username to "BrokenUsername", you could write a SQL Script to run once a day that would do this for you on the SQL Server. Be sure to read through the script and make any edits necessary before running on your database. This script is only an example and NOT designed to be run without editing/testing first. Here is an example:

-- BEGIN SCRIPT --
 
--Note that this should default to ILLData for all self-hosted servers
Use ILLData
GO
 
--This script defaults to changing the username to BrokenUsername. note that
--if you are on a shared server installation, you should change the username
--to BrokenUserABC where ABC is your site code. You would also need to limit
--the requests to those that are associated with your site code's users.
 
--cursor to iterate through all finished transactions that meet criteria
--provided
DECLARE finishedtn_cursor cursor for
SELECT t.username, transactionnumber from Transactions t
 
--joins on the Users table. If shared server, change Users to UsersALL
INNER JOIN Users u on t.username = u.username
 
--only changes borrowing and doc del requests, not lending
WHERE t.ProcessType in ('Borrowing','Doc Del')
 
--only changes requests that are at a status of request finished
AND t.TransactionStatus = 'Request Finished'
 
--only changes requests for users that are not in the following list
AND t.Username not in ('brokenusername','brokenuserABC','brokenuserXYZ')
 
--only changes requests for users with the following NVTGC site code(s)
AND u.NVTGC in ('ILL','ABC','XYZ')
 
--only changes requests that are finished more than 30 days ago
AND datediff(d,t.TransactionDate,getdate()) > 30
ORDER BY TransactionNumber;
 
--declare local variables to store username and transactionnumber
declare @Username varchar(50);
declare @TN int;
 
open finishedtn_cursor;
fetch next from finishedtn_cursor into @userName, @TN;
 
--Run through all Transactions that match the above query and edit
--the username fields
while @@FETCH_STATUS = 0 begin
 
--Change the username to BrokenUsername and clear the internal
--account number
UPDATE Transactions set Username = 'BrokenUsername', InternalAcctNo = ''
WHERE TransactionNumber = @TN and username = @username;
 
--Change the Changedby value for Tracking entries that have the user's username
UPDATE Tracking set ChangedBy = 'BrokenUsername' where TransactionNumber =
@TN and changedby = @username;
 
--Change the AddedBy value for Notes entries that have the user's username
UPDATE Notes set AddedBy = 'BrokenUsername' where TransactionNumber =
@TN and addedby = @username;
 
--DELETES the entire email entry from EmailCopies to remove any reference
--to that user
DELETE from EMailCopies where TransactionNumber = @TN;
 
--get the next username/transactionnumber from the initial query results
fetch next from finishedtn_cursor into @Username, @TN;
end;
 
close finishedtn_cursor;
deallocate finishedtn_cursor;

-- END SCRIPT --