The preferred way to change due dates in Destiny en masse

Destiny is the district-wide library system we use from Follett.

One common request I get is to update a due date for a large number of books.  Usually this is because a librarian has checked out textbooks but then the end of the school year changes by a day, or the original checkout date was incorrect.

If the due date is in the future, and you are on Destiny 9.9 or later, Destiny has a feature for this.  Simply click on the Circulation tab at the top, click the Renew tab at the left and select the By Date tab on the upper right (could there be any more tabs? Smile).  This allows you to find books due in the future by date and renew them in bulk.

If your due dates are already past, or if you need to filter your books using more detailed criteria Destiny has no built-in way to fix this.  You have to manually go to each checkout and change the due date.  When you’re dealing with hundreds or thousands of books this can be prohibitive.

Destiny does, however, have what they call an Offline Circulation feature.  This is primarily used in case the Destiny server is unavailable but you still have to check in/out books to students.  You use a barcode scanner to scan in a checkout code, then scan the student’s ID and then finally the book they are checking out.  This creates a simple text file that you then upload into Destiny.  The really nice thing is that during the upload Destiny lets you select a date of the transaction.  So, if a mistake happened several weeks ago, you can set the date of the transaction to the day after, assuming that no transactions have happened on these books since the mistake.

The really nice perk about this is if you have had several hundred books marked as lost (because they were overdue for a long period of time) then you may have students with all sorts of fees on their records.  Simply checking out the books again to their accounts reverses all the fees and marks the books correctly as checked out.

But how do you create an Offline Circulation file for thousands of books?  Unfortunately Destiny doesn’t have a built-in way to do this either.

This is where SQL comes in. Smile  I’ve been working with the Destiny database directly in SQL for over 4 years for various reasons.  The Follett development staff do a good job of keeping it fairly well organized and understandable.

If you don’t have access to your Destiny database then buy lunch for one of your IT database admins and they’ll help you. Smile  We love free food.

Here is a sample script that will generate a Destiny Offline Circulation file.  It does this for all textbooks at a specific site that were marked lost on 4/24/2011.

-- Create Offline Circulation file to recheck-out all RVHS textbooks that were lost on 4/24/2011

SELECT  '%C125J' + char(13) -- Checkout to patron code
    + patronbarcode + char(13) -- Patron's barcode
    + copybarcode -- Textbook barcode
FROM    
    copy c -- copy table
    join bibtextbook t on (c.bibid = t.bibid) -- textbooks only
    join sitepatron p on (c.patronid = p.patronid) -- patron table
where 
    c.status = 200 -- only lost books
    and c.siteid = 215 -- only RVHS
    and p.status = 0 -- Active students only
    and datelost >= '2011-04-24'
    and datelost < '2011-04-25'
ORDER BY patronbarcode, copybarcode

The real magic is the ‘%C125J’ = char(13) portion.  This is the code that Destiny uses for checkout to patron.  The char(13) adds a line feed.  If you execute this and display the results as Text, rather than a table, you will get a line for the checkout code, a line for the patron’s barcode and an line for the textbook barcode.  This is formatted exactly like the Offline Circulation feature.

I hope this helps!