A Quick Script to Help Update Your Current Student Photos in Destiny from Aeries

At my district we use Aeries, our Student Information System from Eagle Software.  We also use Destiny, our district-wide library system from Follett Software.  Both are great products and, better yet, both are housed on our Microsoft SQL Servers.  This allows us another level of integration since we can get to the raw database for various needs.

One way we take advantage of this is how we load our student photos into Destiny.  Destiny takes a zip file, which contains jpeg files of the students as well as a idlink.txt file, which is an index file that ties the jpeg to the student’s record.

I’m subscribed to the Follett Talk list serve, which is an email list that several Follett Destiny administrators are a part of and every day there are great discussions and help from various users around the country.

When I mentioned that we had a script to help out when updating student photos in Destiny, several were interested .  I’ve been trying to write up a formal blog post on this with great screenshots and directions but, alas, I haven’t had the time.

Anyway, one of our neighboring libraries politely called me about it this morning and I thought I should just put it out there as is for anyone who can use it.

The SQL script file and a quick documentation document are attached to this post.  Get them below.

What it does is grab all the photos (from the school sites you designate) from the server you store your Aeries photos on (this used to be our ABI server but is now our Aeries.Net server), generates the idlink.txt file and puts them all in a zip file for you.  Since it pulls the photos straight from the information in Aeries and also generates the idlink.txt automatically I always get the latest photos (such as from a picture makeup day or newly enrolled students) and also any corrections that may have been put into Aeries.

The script runs in less than a minute for our entire district and then I have a nice easy zip file to upload to Destiny.

There is a little documentation attached but whomever will do this procedure at your site will need some foundational knowledge of Aeries and SQL.  This is not for a librarian at a site that does not have knowledge/access to the Aeries server.

This does not write to any data in the Aeries database so there is no harm in running it.  If there is an error in the script (for instance, you forgot to add your proper school sites and server name) it will simply give you an error but will not cause any changes to data, so don’t worry. 

I hope this helps!

P.S. For those of you who also use BlueBear I had two extra lines of code on the script.  We use BlueBear as our ASB accounting system for our high schools.  It takes photos in the same way but needs them unzipped, all just stored in a single folder.  The last line in my script would unzip the results from above into a folder per school site for our high schools.  This way I would just run the Student Photo Update process in BlueBear and point it at the folder for the correct site.

If you have BlueBear here are the two extra lines I use:
print ‘ECHO Extracting photos for Blue Bear…’
select distinct ‘”c:Program Files7-Zip7z.exe” e c:pics’ + cast(stu.sc as varchar) + ‘Pics.zip -oc:pics’ + cast(stu.sc as varchar) + ‘  >nul 2>&1’
from stu
join #pics on (stu.sc = #pics.sc and stu.bm = #pics.shortname)
where del = ” and tg = ” and bm <> ”
and stu.sc in (310, 320, 410, 430)

Destiny Student Photo Import Quick Documentation

Student Photo Queries.sql

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
    copy c -- copy table
    join bibtextbook t on (c.bibid = t.bibid) -- textbooks only
    join sitepatron p on (c.patronid = p.patronid) -- patron table
    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!