Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Voyager to Alma Migration Query: Identify Patron Records that May Be Deleted Based on Cut-Off Date and Expiration or Purge Date Field

    Created By: Laura Guy (contact)
    Created on: 3/02/2021



    When you migrate from Voyager to Alma, and have selected a cut-off date, and which date field to use (expiration or purge), it may be useful to review the list of patrons to be deleted.

    This query, which can be run using Voyager Prepackaged Access Reports, will list those patron records.  The patrons that will be deleted will have an expiration date or purge date earlier than the cut-off date, and will not be linked to any current activity such as circulation charges, fines, requests, and etc.

    Note, to use purge date instead of expire date, substitute “PURGE_DATE” for “EXPIRE_DATE,” or change the field in query design view.

    The query will exclude those patron records that have non-zero activity counters.

    SELECT PATRON.NORMAL_LAST_NAME, PATRON.NORMAL_FIRST_NAME, PATRON.EXPIRE_DATE, PATRON.SSAN, PATRON.INSTITUTION_ID, PATRON_GROUP.PATRON_GROUP_CODE, PATRON_BARCODE.PATRON_BARCODE FROM (PATRON INNER JOIN PATRON_BARCODE ON PATRON.PATRON_ID = PATRON_BARCODE.PATRON_ID) INNER JOIN PATRON_GROUP ON PATRON_BARCODE.PATRON_GROUP_ID = PATRON_GROUP.PATRON_GROUP_ID WHERE (((PATRON.EXPIRE_DATE)<[Expire Date]) AND ((PATRON_GROUP.PATRON_GROUP_CODE) Not Like "UB*") AND ((PATRON.CURRENT_CHARGES) Like "0") AND ((PATRON.TOTAL_FEES_DUE) Like "0") AND ((PATRON.CURRENT_HOLD_SHELF) Like "0") AND ((PATRON.CURRENT_CALL_SLIPS) Like "0") AND ((PATRON.CURRENT_CHARGES_UB) Like "0") AND ((PATRON.REQUESTS_UB) Like "0") AND ((PATRON.LOST_ITEMS_UB) Like "0") AND ((PATRON.TOTAL_FEES_DUE_UB) Like "0") AND ((PATRON.DB_ID) Is Null Or (PATRON.DB_ID) Like "0")) ORDER BY PATRON.EXPIRE_DATE;

    As noted above, patrons with current fines and fees will be blocked from deletion. Voyager provides an option for you to say whether to delete patrons with historical fines or fees (i.e., patron XYZ paid a fine of $$$ on , etc.). You will find the setting that controls this option in the System Administration client: open SysAdmin, select Circulation, then select Miscellaneous. If the box for “Allow Deletion of Patron With Historical Fines” is checked, then patron records with historical fines or fees will be eligible for deletion.

    For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.

    Posted as is. If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.