Polaris SQL to Get Last Month’s Patron Checkouts by City

Thought I’d share! This took way longer than I’d like to admit. I had to use multiple sources including the Polaris forum, notes from my Polaris trainer, and some guessing on how to get this to work together. Hopefully my colleagues will have more queries for me to look up in the future so I can continue to strengthen my Polaris SQL skills.

Next up, figuring out how to create a .rdl report from this for my colleagues.

SELECT psc.description, COUNT(*) [Quantity]
FROM PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
JOIN PolarisTransactions.Polaris.TransactionTypes tt WITH (NOLOCK)
ON th.TransactionTypeID = tt.TransactionTypeID
JOIN PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK)
ON th.TransactionID = td.TransactionID
AND td.TransactionSubTypeID = 6
JOIN PolarisTransactions.Polaris.TransactionSubTypes sub WITH (NOLOCK)
ON td.TransactionSubTypeID = sub.TransactionSubTypeID
JOIN Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON td.numValue = pr.PatronID
JOIN Polaris.Polaris.PatronStatClassCodes psc WITH (NOLOCK)
ON pr.StatisticalClassID = psc.StatisticalClassID
JOIN PolarisTransactions.Polaris.TransactionDetails tdMat WITH (NOLOCK)
ON tdMat.TransactionID = th.TransactionID
AND tdMat.TransactionSubTypeID = 4
JOIN PolarisTransactions.Polaris.TransactionSubTypes subMat WITH (NOLOCK)
ON tdMat.TransactionSubTypeID = subMat.TransactionSubTypeID
JOIN Polaris.Polaris.MaterialTypes mt WITH (NOLOCK)
on tdMat.numValue = mt.MaterialTypeID
WHERE th.TransactionTypeID IN (6001)
AND MONTH(TransactionDate) = MONTH(DateAdd(Month,-1,GETDATE()))
AND YEAR(TransactionDate) = YEAR(DATEADD(Month,-1,GETDATE()))
GROUP BY psc.Description
ORDER BY psc.Description

Recent 1-on-1 Topics

I’ve had a busy time assisting people as of late. I’ve noticed that we’ve gone from a couple 1-on-1 appointments a month to 3 per week. People usually contact me on Monday to set up an appointment for later in the week. Usually it’s the next day. I somewhat think I’m better at providing personal assistance than I am at teaching classes since my individual patrons walk away with the exact help they need.

  • Copyright
  • Create a brochure — this took multiple meetings to process
  • Guiding patron to the right website tool for their site. Drupal Gardens in this case.
  • Fixed a laptop
  • Backing up a laptop. It then turned into a whole conversation about data management.
  • WordPress
  • Tablet help
  • Move from Yahoo to Gmail

During the summer the sessions were more focused on small business needs, but now they’ve switched to a little more mundane. I’m formulating an idea to teach a series of workshops for small business owners in the spring.

Content Strategy for WordPress

Found this excellent keynote speech by Stephanie Leary about controlling content strategy in WordPress. The accompanying slides. Watch Stephanie’s presentation to learn a lot of what you need to know about content strategy with WordPress.

I’m very pleased that there is no overall strategy here that I was unaware of. In fact, most of the discussion here about chunking content is something I figured out innately when I began to use Drupal in 2010. With that in mind, I’ve began to discuss the work involved for my next freelance project. The site may not be built in WordPress but LibGuides. How exciting it is that something I had to learn for work is something I can now use in my freelance time!

UX Review of U.S. Copyright Form

This story comes from a patron who needed some assistance with her U.S. Copyright Form.

On the outside, the U.S. Copyright Forms look easy enough. I printed out a copy of the Visual Arts form and read it before my meeting. Okay, this is some basic metadata fields (e.g. author, contributor, creation date, published date, etc.). My patron and I began in earnest to fill out the form. The troublespots:

A collection is very vague regarding visual arts. A group of poems makes sense. But what about artwork whose central focus is that the have the same creator? The website itself is unclear on what to do in this context. Other sites did not provide a reasonable explanation.

Input of multiple submissions is an ineffective use of time. For each entry, you have to add a new item, then go in and type up the information. Then save. Then go back to the main screen. Repeat. To be more user friendly, the submitter should only be on one screen the whole time with fields you can just add as needed. Doing it the other way breaks up the workflow which adds unnecessary time and frustration.

Instructions per page are sometimes vague. We’d have to go on a wild goose chase of looking up information in a separate screen to move forward.

The design does not feel trustworthy. Anyone can whip up a shiny WordPress website in a few minutes that impresses (especially spammers). It’d be nice if an official government agency’s website felt like an official website. The appearance of a website influenences users on how much they can trust you.

The language is too retail-centric. I apologize for not having access at the moment to the log-in version, but I believe the terms used was “carts.” As a first-time visitor of the site, I was confused. I only have an online cart if I’m creating something, right? Why would my copyright submission, the me-to-company work go into a cart?

Next stop after typing titles is confusing. Yes, on the left, is a little three cell table which in yellow highlights that you are on the Pay step. However, the user is so focused on the center screen, they are effectively dumped on a page which does not indicate what the next step is.

Image submission is tiresome. This is a repeat of the submitting multiple titles from above point. Why in the age of sweet drag-and-drop image uploaders, do you have to go in one at a time to upload your files? More than that, you have to then retype the name of the file next to the submission. On one hand I can understand this since not everyone knows how to title files in a way that is easy to read later. But it still felt like a lot of work to type out your file name all over again.

On the plus side:

You get a $30 discount by submitting your claim online.