4 Replies Latest reply on Apr 20, 2016 5:45 PM by Timothy Grzeczka

    Add Card ID to Existing Pharos Users

    Timothy Grzeczka Pioneer



      I'm back with another fun Card ID question. We are close to the start of our project to re-card all our students, faculty, and staff. This is our first card upgrade since I've worked here (15 years) so the jump from 85% plastic card with bar code / 15% prox cards with bar code to 100% card with prox, mag, and bar code is a huge step. I've done some testing and manually added a mag stripe number to my account in the Card ID field. What I'm trying to find is a way to update the records of the existing users en mass in Pharos. I've read into Userload's ability to update via a text file BUT all I want to update is the Card ID. Ideally, we'd have a file that our card system would export at set times that Pharos could ingest via a Userload scheduled task. My question is can the file be formatted in a way where you don't provide all the fields? I've included an example of what I mean below. In other words, if I have the proper number of commas will it leave those data values alone or will it blank those data values?



        • Re: Add Card ID to Existing Pharos Users
          Chris Axtell Navigator

          Hi Timothy,

          I took a quick look Pharos' Batch Load Technical Information document and it doesn't appear to be the case. It reads like any "blank" fields that are read will result in the current data in the Pharos User table being overwritten with the empty field.


          What we ended up doing is creating a second database on the MS SQL server that hosts the Pharos database. The new database serves as an ODBC conduit to our card server (e.g. Blackboard Transact). We went the route of creating a second database so we didn't have to mess with the schema of the Pharos database which makes Pharos software updates simpler. We then created a SQL Server Agent job with four steps in it.

          1. Check to see if the MS SQL server can reach the Card Server db (the two db's are on physically separate boxes), if so continue, if not fail and send warning
          2. Check to see if the temp "processing" table in the conduit database exists, if so then drop the temp table.
          3. Upload the Card ID information from the Card Server to the temp table (it's created as part of the process), and do some basic processing.
          4. Update the Pharos Users table "card id" field based on matching information (i.e. University ID) that is common in both the Pharos database User table and conduit database Card ID processing table.


          This process then automatically (scheduled as a nightly job) updates the "Card ID" field in the Pharos Users table based on information in the Card ID server. This does assume that that the individual's University ID is already loaded into the Pharos Users table (we use the "Custom Field 1" to store this data). Our nightly batch user account provisioning process uses the Pharos Userload tool to create/update the accounts with this and other information before the Card ID update process runs.


          The other nice benefit is between these provisioning steps and somewhat intelligent login/billing scripts the Pharos "print release" process can fairly dynamically handle use-cases where a patron's card ID changes between one nightly batch run and the next (e.g. they lost their university ID and was provided with a new one that has a new "issue code").


          Hope this helps,