Long-time listener, first time caller here. We use OrangeBoy/Savannah for incident reports, marketing, and analytics. Recently I ran an analytics query designed to catch registration errors on Youth accounts. Out of over 30,000 results, the query returned several dozen accounts with blank addresses, but when I checked in Polaris, the addresses were there. After some troubleshooting, I reached out to the awesome folks at OrangeBoy, they tried it on their end and confirmed it’s working but was also returning some NULL values in the address fields. They provided their default SQL query and I ran it in our training server with the same results. It’s working beautifully except for some random accounts that return with NULL addresses! I’ve confirmed it’s not a patron code error because it’s happening to Adult and Computer Access cards too, and it’s not an address formatting issue, but I’m baffled as to what it could be. I’m submitting a ticket to Polaris, but I’m wondering if anyone here has encountered this issue or something similar and what the cause was? Thanks so much, and Happy Holidays!
Hi @IO504 , if you shared the SQL, we might be able to find the reason behind your mystery. I also recommend jumping into Discord to collaborate on SQL topics and more.
Hi Eric,
Thanks for responding, the SQL is below. Because only a small number of patrons are affected, I’ve been inclined to think it’s something in Polaris, but another perspective would be most welcome. And thanks for the tip about Discord, I just joined and it looks like a great resource!
Best,
Izzy
select
p.PatronID, p.Barcode, p.LastActivityDate, p.ChargesAmount,
pr.NameFirst, pr.NameLast, pr.PhoneVoice1, pr.EmailAddress, pr.ExpirationDate, pr.Birthdate, pr.RegistrationDate,
a.StreetOne, a.StreetTwo,
pc.City, pc.State, pc.PostalCode, pc.County,
pcd.PatronCodeID, pcd.Description as PatronCodeDescription,
o.Abbreviation as BranchID, o.Name as BranchName
from Polaris.Patrons p
left outer join Polaris.PatronRegistration pr on p.PatronID = pr.PatronID
left outer join Polaris.PatronCodes pcd on p.PatronCodeID = pcd.PatronCodeID
left outer join Polaris.ViewPatronAddresses pa on p.PatronID = pa.PatronID and pa.AddressTypeID = 2
left outer join Polaris.Addresses a on pa.AddressID = a.AddressID
left outer join Polaris.PostalCodes pc on a.PostalCodeID = pc.PostalCodeID
left outer join Polaris.Organizations o on p.OrganizationID = o.OrganizationID
Total shot in the dark here, but could it have something to do with the setting in the patron account for which address to send the Notices to? I’ve not run into the specific situation you’re describing, but we did have some issues with our recent patron address correction job with UMS because Polaris defaults to the “Notices” address for things like SimplyReports. Some of our patrons had more than one address in their accounts and their “primary” address wasn’t the one selected in the “Notices Address” dropdown of the patron registration.
I agree with Amy. I ran your query on our server - first of all, it pulls from patrons primarily rather than patronregistration, which is going to return some invalid results (or maybe not if your data is cleaner than ours), but more importantly, it doesn’t return addresses for patrons who don’t have an address selected under notification settings > notices address. You could resolve this by just taking out the “and pa.addresstypeid=2” clause but then you’d probably want to find a way to deal with patrons who have multiple addresses.
Thanks so much, Eleanor! I’ll be darned, that’s exactly what it was and removing “and pa.addresstypeid=2" from the query returned all the values! I’m genuinely surprised this was the issue since everything I saw in OrangeBoy looked to be pulling from the primary address. The query is now returning patrons with multiple addresses like you predicted, but I exported the results to a spreadsheet and filtered out the duplicates by card number. You and Amy are total rockstars, thanks again!
