SQL Queries for Patron Edge, Part III

This is my final entry in the series on SQL queries for Patron Edge. Today let’s take a look at the access control tables, so we can do some cool stuff with barcode scanning results:

select *
from Barcodes_Log
inner join Barcodes on Barcodes.brtSourceCode = Barcodes_Log.brtlCrossRefID and Barcodes.brtSourceTable = 2
inner join Barcodes_Scanners on Barcodes_Scanners.brtscCode = Barcodes_Log.brtlScannerID
inner join SubsStaticReport on SubsStaticReport.ssrSubsProgNum = Barcodes.brtSourceCode

Scanning tables are pretty complex, so let’s break down some of the fields:

• brtlCrossRefID – Corresponds to Barcodes.brtSourceCode
• brtlScanningResult – Corresponds to the lookup table Barcodes_ResultMessages
• brtSourceCode – Corresponds to ReportStatic.rpsTickCode for tickets, SubsStaticReport.ssrSubsProgNum for membership cards and series
• brtSourceTable – 1 for ticket, 2 for membership, 3 for series ticket, 4 for series event ticket
• brtEntityType – only used for tickets, 1 for Show, 2 for Event
• brtEntityCode – Corresponds to Shows.shCode or Events.evCode, based on the brtEntityType
• brtStatus – 0 for active barcodes, 9 for canceled/reprinted

Now you can report on barcode scanning to do things like plan staffing at entrances, figure out traffic flow through your venue, etc. How are you using scanning data? Leave a note in the comments.

SQL Queries for Patron Edge, Part II

Continuing on yesterday’s theme, here are a few more tables and joins to do with what you will. We’ll start with the SubsStaticReport table this time, which is a data warehouse table that contains all of your subscriptions and memberships.

select *
from SubsStaticReport
inner join Clients on Clients.cltCode = ssrSubsNum
inner join StatusType on StatusType.stsStatus = ssrRenewalStatus

• ssrSubsNum - Corresponds to Clients.cltCode
• ssrCurrentStatus and ssrRenewalStatus – Correspond to the StatusType lookup table
• ssrLastTransact – for series/memberships that are edited or  canceled, this field points to ssrTransactNum of the original series/membership

Now let’s add on commissions and discounts:

select *
from SubsStaticReport
inner join Clients on Clients.cltCode = ssrSubsNum
inner join StatusType on StatusType.stsStatus = ssrRenewalStatus
inner join CalculatedCommission on CalculatedCommission.clcTicketNum = rpsTickCode and clcType = 4

You can refer to SQL Queries for Patron Edge, Part I to see what all the commission fields mean. Leave a comment below with your questions!

Tips from a Troubleshooter: Stealing the Spotlight

Hey,

My name is Sean, I work in Patron Edge Support and with Nicholai on Patron Edge and Patron Edge Online.  I've contributed to blogs for Education Edge and Student Billing in the past, and now I get to stretch my legs and really explore the Arts and Cultural marketplace.

I've gotta say that I really like the people I've talked to already, and the market is very interesting and full of innovation and creativity.  Personally, I've always been a bit of a nerd and I've been learning as much as I can about the networked environments that clients use to make Patron Edge, Patron Edge Online, and Patron Edge Kiosk work.

Over the next few posts, I hope to share some insights into the meat of how all of these databases talk to each other, and share some tips and utilities I've found useful for troubleshooting our software.  As always, if there is specifics you'd want to know more about just leave a comment!

Have any tools (open source, other blogs, or otherwise) you use daily in an IT environment?  Let me know!

SQL Queries for Patron Edge, Part I

While giving a session at the Museum Computer Network conference on extracting data from a Patron Edge database, one of the attendees suggested creating a handful of sample queries. The idea is that with some samples showing the important table joins, users can run with them, expand on them, or even just push them directly into Excel to do pivot tables.

With that in mind, here are a couple of samples to get started:

select *
from ReportStatic
inner join Clients on Clients.cltCode = rpsMailingList
inner join Orders on Orders.ordCode = rpsOrder

The ReportStatic table is one of the major data warehouse tables in the system. It has a row for every ticket sale and return, including event tickets that are part of a subscription. Below are some useful fields that aren’t intuitive without having worked in the back end for a while. Check them out:

• rpsTickCode – For single tickets, this is the Tickets.tiCode field. For subscription event tickets, it is the TicketsSubscription.tisCode field
• rpsMailingList – Corresponds to Clients.cltCode
• rpsOrder – Corresponds to Orders.ordCode
• rpsSaleStatus – Active tickets have a status of 1, canceled tickets are a 9
• rpsTickType – Corresponds to Colors.clrStatus
• rpsPriceLevel – Corresponds to Tariff.taCode

Now, let’s add a little more to the query:

select *
from ReportStatic
inner join Clients on Clients.cltCode = rpsMailingList
inner join Orders on Orders.ordCode = rpsOrder
inner join CalculatedCommission on CalculatedCommission.clcTicketNum = rpsTickCode

This gives us some new fields to look at:

• clcCommission – Corresponds to Commission.cmsCode
• clcCachInOut – 0 means a positive amount (i.e. commission), 1 is a negative amount (i.e. discount)
• clcType – 1 for single tickets, 4 for memberships/subscriptions
• clcStatus – 0 for active, 9 for returned

Hopefully this will help you get started writing your own queries and reports. Be sure to leave a comment here, or start a new thread on the Patron Edge forum, with questions and to share your own queries.

Rotating show images in Patron Edge Online

My colleague, Matt Cook, recently showed off a cool Image cube he put together for Patron Edge Online.  Take a look at the information below, it would be cool to see this put to work.  The really cool thing about this is that each side of the cube can link to different things, so this could help drive interest in things like special events, classes, or whatever the creative minds in your marketing department can think up!  As the cube is set up right now, it uses the loader.asp file to allow it to link directly to specific shows.  Pretty cool!

Disclaimer: This will require you to do a small amount of coding. The example is pretty easy to follow if you are familiar with HTML, Javascript or other coding languages, but you won’t be able to contact support for assistance setting this up.

           1. Create a new screen part on the page where you want the image to display (In this example, I’ll use Template 1)

 ·            Type = Include

 ·          File Name = imagecube.html

 ·          Top, Left, Width, Height – position the element as desired, but you want it to be the size of your images for best resolution.  I used 450,20,120,80

           2.        Grab the latest copy of the image cube library Note: There are literally thousands of cool jquery libraries out there, many of them dealing with images, this is just one that looked slick to me.  Most are easy to use and would be interchangeable.  Unzip the library into your PEOSite folder (by default, C:\Program Files\Blackbaud\The Patron Edge Online\PEOSite)

           3.        Open up your favorite text editor, create imagecube.html and drop it in your PEOSite folder

Here’s the code for my file:

<head><script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="jquery.imagecube.js"></script>
</head>
<div id="defaultCube" style="width: 120px; height: 80px; overflow: none;">
 <a href="loader.asp?target=show.asp?shCode=64"><img src="images/shows/show64.jpg" alt="Show 64 Title" title="Show 64 Title" style="width: 100%; height=100%;"/></a>
 <a href="loader.asp?target=show.asp?shCode=38"><img src="images/shows/show38.jpg" alt="Show 38 Title" title="Show 38 Title" style="width: 100%; height=100%;"/></a>
 <a href="loader.asp?target=show.asp?shCode=19"><img src="images/shows/show19.jpg" alt="Show 19 Title" title="Show 19 Title" style="width: 100%; height=100%;"/></a>
</div>
<script type="text/javascript">
$(document).ready(function() {
                $.imagecube.setDefaults({speed: 1000, pause: 6000});
              $('#defaultCube').imagecube();
});
</script>
You’ll need to make a few changes.  Namely, I referenced show codes in my sample database.  You can generate your loader link as needed.  Your images will also likely not be named show64, so update the img src reference as well as the alt and Title as needed. There are a lot of benefits to using Google’s jQuery library, but you can download your own if needed. 

Try it out and leave a comment if this worked for you!

3.401 and 3.41 update released!

We are happy to announce that we have released new versions of The Patron Edge and The Patron Edge Online.

Here are a few things to get excited for:

Patron Edge 3.401:

• Coupon codes: You can now use Alphanumeric codes for Coupons!  They no longer need to be things like 1234, they could be BOGO (Buy One Get One) or something similar
• Change Due prompt in General Admission Sales: Now your touchscreen users can have a prompt to remind them how much change to give the client, after making a cash sale.

Patron Edge Online 3.4.1:

• Same Day Internet Sales: You can now allow users to purchase tickets the same day as the event online, allowing those procrastinators to still get to see the shows.

And of course, there is great information on these enhancements and our PCI compliance enhancements located in our Update guides:

http://www.blackbaud.com/files/support/guides/pe/ug3401us.pdf
(Patron Edge Update Guide)

http://www.blackbaud.com/files/support/guides/pe/peo341ug.pdf
(Patron Edge Online Update Guide)

Please contact your Account Manager if you are interested in updating to these versions.

PE and PEO Updates Demystified

Being the type who always wants to use technology to take things to the next level, I've done some more experimenting with screencasting. This video will take you through an update for Patron Edge 3.401 and Patron Edge Online 3.4.1. The video is about 17 minutes and should answer any questions you have. If you missed the update webinar a few weeks ago, this is even better, as I take you through every screen and explain every step of the process.

Thoughts, concerns? Leave a message in the comments.

[Update] I've been told that the best practice is to run the update on the workstations first, before doing the detach/reattach and logging in to update the data.

Call for Upcoming Conference Feedback

One of the sessions at November's Blackbaud conference will be Effective Pricing and Discounting Strategies for Arts & Cultural Organizations. With that in mind, we wanted to gather as much feedback as possible to tailor the session to your needs. If you can, please check out the quick, five-question survey below.  Please email your responses to the session coordinator jackie.huffman at blackbaud dot com. The more responses we get, the better the session will be. Thanks!

1. When do you offer discount pricing to the general public (non-members)? Choose all that apply.
   1. The day of the event
   2. The week of the event
   3. The month of the event
   4. All of the above
2. Do you use any of these promotion strategies? Choose all that apply.
   1. Coupon with a promotion code
   2. Buy one get one free offers
   3. Combo offers that include multiple events
   4. Packages with different types of events/items (e.g. dinner discount and event tickets or a CD and the performance tickets)
   5. Other? Please specify.
3. Have you used any revenue management techniques? Choose all that apply.
   1. Airline style pricing in which the closer to the event the higher the prices
   2. Charging premium prices for specific events
   3. Last minute discounting
   4. Other? Please specify.
4. What are the biggest challenges your organization faces in regards to pricing and promotion? What are you doing to deal with them?
5. Would you be willing to share your experiences? If so, please provide contact information and one of our presenters will contact you.

Patron Edge Update Webinar FAQs

Thanks to everyone who was on the update webinar today. Amie did a great job of explaining the process and showing the new features, and we heard some really good questions. With that in mind, I wanted to go ahead and post the information so those of you who missed the webinar can still catch the highlights.

When will Windows 7 and Windows Server 2008 be compatible?
There is not currently a timeline for compatibility with Windows Server 2008 or Windows 7.

What are the implications for PE/RE/FE integrated clients?
There are no special requirements for The Financial Edge. If you integrate with The Raiser's Edge, ensure that you are running 7.85 or higher and that you disable the integration while updating Patron Edge.

Is The Raiser's Edge 7.85 still compatible with Patron Edge 3.400?
Yes, 7.85 and 7.91 are compatible.

How soon do we need to run the update?
This will depend on your merchant services provider. However, it is important to note that on July 1, 2010, Visa will stop working with acquirers whose clients do not use PA-DSS certified applications. See this link from Visa for more information.

Will custom PEO design elements still work as expected (i.e. free text, images, menus)?
I always recommend setting things up in a test system to ensure everything works as expected. There are no known changes to Patron Edge Online that would affect design elements like screens, screen parts or menu items.

Is the upgrade process different if we're on 3.340 than if we're on 3.310?
The main difference is that there will be fewer steps, since PCCharge, Windows Server, SQL Server and The Raiser's Edge are already on the required versions.

Can we update PCCharge to 5.8 now if we're on Patron Edge 3.310?
Absolutely. As part of the call with your account manager, we can arrange to put the required PCCharge version in your FTP folder.

Is Moneris Canada now supported?
Yes, PCCharge for the US and Moneris for Canada are the two standard credit card interfaces.

Is PCCharge 5.8 Client still incompatible with Patron Edge?
Yes, this is a known issue. At this time, PCCharge 5.8 Client and Patron Edge cannot be installed on the same machine. PCCharge Payment Server does not have any conflicts with Patron Edge and continues to work normally.

Whose IP addresses will have to change for hosted clients?
These are done on a one-off basis. Generally, an organization currently running Patron Edge Online 3.2.5 or below will receive a new IP address. As part of starting the update process, Blackbaud will let you know several weeks in advance if the IP address for your website will change. This will allow plenty of time to coordinate with your domain name provider to schedule the change on that end.

Does each product have to be on its own server to be PCI compliant?
I don't have any information about this. Please refer to the PCI Security Standards Council website to see the full set of PCI rules.

Have Packages changed since 3.340?
Coupons can now use both alpha characters and digits. The remaining Package features are the same as in version 3.340.

Are we planning digital signature capture?
I have not seen this on any product roadmap for the next year.

How long should we plan to be down?
This can vary greatly. To be safe I would plan for at least half a day. If the only thing you have to update on the big day is Patron Edge and Patron Edge Online, and you have carefully read the PE and PEO update guides, you'll be able to keep downtime to a minimum. There may be additional steps to perform for Patron Edge Online after the update, but those steps are based around new features and won't prevent you from having a working website.

If you are hosted and your IP address requires a change, the length of time it takes for that change to be recognized can vary. This Wikipedia article explains the technical bits about what happens when an IP address changes and why it can potentially take a while to propagate. In my experience (please take with a grain of salt), most of these changes take effect within a few hours.

If you feel the update is more complex than you can handle or you want to minimize downtime by having Blackbaud to perform the update for you, just let me know and we can set up a call with your account manager and I to work something out.

Other update questions? Leave a message in the comments!

Get Your Giving Clubs Right (With Free Stuff)

Not too long ago, I got a really interesting Raiser's Edge question. "How can I find a constituent's longest stretch of consecutive years given, for my giving clubs?" You may have a great donor who gave from 1995-2005, missed a year, and then started giving again. He wouldn't show up in a Consecutive Years Giving report due to that one missing year, but you still want to send the right ask letter and amount.

I was intrigued by the problem. Intrigue being my greatest motivator, I was in the lab for a little while coming up with a good solution and thought I'd share it with you, my faithful readers. Just download and run this query, save the results as an import file, and import it as a constituent attribute called Max Consecutive Years. Now the field is available in queries, exports and mailings!

You can download the query here and run it against your Raiser's Edge database in SQL Management Studio. The result set will serve as your import file with all the required fields pre-filled. Now just perform a quick constituent attribute import and you're done!

Max consecutive years given

As with any stuff I give out on The Spotlight, this isn't a core piece of the product; it's custom so that means it is unsupported. My analysts (or Raiser's Edge analysts) cannot help run the query or take questions on it. RE support can definitely help if you're stuck on the import side of things, however. If you have any problems with it, please leave a comment, hit the forums, or send me an email.

Add A Facebook Fan Box To Your Site

My colleague Chad recently wrote about 6 Dogooding Webby Things Your Nonprofit Should Check Out, and I wanted to show how quick and easy it is to implement one of those webby things, the Facebook Fan Box. It's not a fit for every organization, but if you have a strong Facebook presence and want to promote it on your website, don't let the idea of building this custom piece intimidate you. And the steps work for everyone, whether your site uses PEO or a different application.

Step One: Visit your Facebook page. Go to your organization's Facebook page and click the link on the left that says Add Fan Box to your site.

 

 

Step Two: Get the code. You will be taken to a second page where you can check whether you want to include both your stream and fans, or just one of the two. You will get the auto-generated code along with a preview, like this:

 

 

Step Three: Add the code to your site. Edit the "connections" entry in the code if you want to display more than 10 fans at a time, and the "width" bit if you want it to be wider than 300 pixels. If you use Patron Edge Online as your content management system, save this code to Notepad as a .htm file and add it to PEO as a screen part. If you use a different CMS, the process should be pretty similar. Of course there are advanced options and extra styling you can get into, but these three painless steps get you from zero to Fan Box in five minutes.

Is your site using the Fan Box feature or custom Facebook applications? How do they fit into your marketing strategy or promote your mission? Leave a message for other readers in the comments.

Scanning on a Shoestring

I know some of you have looked into getting Wireless Access Control for Patron Edge, and then reconsidered after seeing how much a quality wireless scanner costs these days. The Symbol MC55 is about $1000 a piece, and lower-end scanners tend to be less reliable in scanning successfully.

One of our clients recently became fed up with their scanners' lack of reliability and network connection problems. Instead of giving up on scanning, they found a clever solution that allows them to scan tickets for less than half the cost of a high-end scanner. How did they do it? They purchased cheap netbooks with Windows XP Pro, installed Patron Edge, and attached wired scanners. To scan, they launch ScanningValidation.exe so that it doesn't require a Patron Edge user license.

While this solution may not work for everyone, it's an easy way to do entry-level scanning. You can use it to figure out if moving to wireless scanning and purchasing high-end scanners makes sense for your organization. Laptops have legs though; I recommend either having very trustworthy volunteers to handle access control, or locking the laptop to some kind of permanent fixture.

How has your organization managed to meet your technical needs on the cheap? Share some advice in the comments.

The Summer of Updates

As updates to our security release continue over the summer and our PA-DSS-compliant build is being finished, we've hit all of the usual issues that come up with any update. Here are a few things you can do now that will make sure your update goes as smoothly as possible.

Get the environment right - You can update to Windows 2003 and SQL Server 2005 at any time. As long as you are already on 3.310, go ahead and do this update. Since SQL 2005 isn't the latest, several organizations have purchased SQL 2008 and then contacted Microsoft to "downgrade", where they were provided with a copy of 2005. This will save cash on future updates by not having to buy another version in the future.  You can always check out the latest system requirements at http://systemreqs.blackbaud.com.

Review the guide - I know it's long, but the update guide covers everything required to update the core pieces. You don't have to read every page, just skim the headings and then dig into the steps that are different from past updates.

Prepare for encryption -  This update requires you to create a new SQL login that's going to manage credit card encryption. Create this ahead of time and give it 'db owner' rights to the Patron Edge database. It won't take you two minutes and will save time later on. The update will ask for this login and have you create a master key. This is just a password used to encrypt credit cards while they're being processed. To prevent getting hacked, make it strong. Give it at least eight letters, a number and a special character.

Do big changes ahead of time - If you're planning to move servers around without an experienced network administrator, it will make life easier to move them before the update, especially if you're dealing with firewall changes and Internet stuff. If you have an IT person with solid understanding of your network, feel free to do it at the same time as the update.

Test PCCharge - This one should only take about 15 minutes, but there have been some rare cases where we've had to get help from Verifone after the PCCharge update to version 5.8 (usually when FDCN is the processor). When you're testing the update, be sure to check credit card processing so we can work out any issues with that.

Just follow these guidelines and your update should be pretty painless. Have you had any update troubles? Leave a comment to help out the folks who are still planning theirs.

Three Questions About Virtualizing Patron Edge

I've been on upgrade planning calls with about a dozen organizations in the last few weeks, and several have asked about putting Patron Edge in a virtual environment. Three questions have come up repeatedly and I want to address them and give some food for thought when planning your next server hardware purchase.

Will it work? - The Patron Edge Quality Assurance team and several members of our Support team use VMWare to test multiple versions of PE at once, and it works perfectly. Keep in mind this is for the server side of things. While a virtualization solution will do fine on workstations as far as the program is concerned, I can't guarantee that your printers/scanners/card swipes will work flawlessly.

Is it slow? - In-house, I haven't seen a performance difference between a physical server and a virtual server with the same system specs. Several organizations currently use VirtualBox or VMWare because they can buy extremely fast servers and then split resources over several virtual machines. The bottleneck of a virtual machine setup is the hard disk, so be sure to read up on best practices to keep your virtual machines running as fast as possible.

Is it supported? - Server-side, it's absolutely supported as long as the virtual machine meets our system requirements. Currently, at least one organization has a fully-loaded server with a fast Linux distribution installed. They put Patron Edge and their other enterprise programs on virtual servers.

Supportability is only an issue is on the workstation side, and that is because we don't test peripherals on virtual machines. I have seen a Macbook, with a Datamax printer connected, running Patron Edge through VMWare Fusion and printing tickets without any issues. But if the printer didn't work in that setup, my Support team wouldn't be able to assist since we don't have that kind of computer hardware and it wasn't verified as working by the Quality Assurance team.

What other questions do you have about putting your Blackbaud products in a virtual environment? Have you been doing it for a while at your own organization? Leave a note for others in the comments.

Two Cool Tools for the Back Office

I'm about six months into my own nonprofit arts organization (parkcirclefilms.org) and am always looking for ways to work smarter and automate repetitive tasks. I wanted to share two excellent tools I've implemented to manage the administrative stuff so I can focus on our mission.

TWiki

TWiki is a really cool, open source wiki and collaboration system. It can be a bear to install, but the wiki engine is powerful and it sports awesome features like:

Project management and project development - Your IT staff can use it for coordinating on website or report development, and the marketing folks can use it to collaborate on your next big campaign.

Knowledge management - As my organization grows, more volunteers have started getting involved. To keep the learning curve low, volunteers are given a login to TWiki that gives them access to our Policies and Procedures web and other pertinent information. Things they shouldn't be allowed to read are kept private via group security.

Document management - This was what caught my eye first. We have meeting minutes, image files and other documents that I need to share easily and securely. Google Docs was my first attempt, but it lacks version control and you can only do text documents and spreadsheets. TWiki is good for keeping track of documents, and keeping them organized in a way that makes sense (because the file name "mrkt_sum_09_letter_stats.xls" won't make any sense in two months).

If it's good enough for Yahoo!, Cingular and SAP, I figured it was worth a shot, and I'm not disappointed at all. This is a great product that will scale into a nice intranet as we continue to grow.

Wordpress

Wordpress is the best blogging platform currently available. It's free and open source, and has hundreds of great plugins to extend it. We didn't need a full content management system, so this was a way to build a decent-looking web presence in just a couple of days. Creating and editing content is simple, so if I'm unavailable and we need a piece of information added to the website, I've got four non-techy people who can do it without breaking a sweat. I'm even testing out an event calendar plugin to let people more easily see our upcoming film schedule.

If you're not using Patron Edge Online for your web presence or to sell tickets, I highly recommend checking out Wordpress, or even Drupal if you need a full-featured content management system without paying full-time web developers to build and maintain a custom website.

What tools do you use at your organization to automate back office tasks or keep things flowing smoothly on the administrative side? Share your experience in the comments.