The Spotlight : Patron Edge

SQL Queries for Patron Edge, Part III

Nicholai Burton — Wed, 18 Nov 2009 20:08:00 GMT

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

Nicholai Burton — Fri, 13 Nov 2009 18:19:00 GMT

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!

SQL Queries for Patron Edge, Part I

Nicholai Burton — Thu, 12 Nov 2009 00:12:00 GMT

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.

3.401 and 3.41 update released!

Sean Long — Tue, 20 Oct 2009 17:39:00 GMT

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

Nicholai Burton — Thu, 15 Oct 2009 13:10:00 GMT

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.

Patron Edge Update Webinar FAQs

Nicholai Burton — Wed, 23 Sep 2009 20:38:00 GMT

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!

Scanning on a Shoestring

Nicholai Burton — Mon, 27 Jul 2009 18:21:00 GMT

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

Nicholai Burton — Tue, 14 Jul 2009 12:12:00 GMT

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

Nicholai Burton — Tue, 16 Jun 2009 18:59:00 GMT

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.

The 20-Minute Test Environment, Part I

Nicholai Burton — Thu, 07 May 2009 20:38:00 GMT

Things have been quiet on the blogging front for the last couple of weeks; I've been playing with some cool technologies like the CodeIgniter framework for creating web applications and the jQuery library (with these awesome video tutorials) for doing fun web stuff without having to learn a ton of JavaScript.

In light of my recent attempts at developing cool features and tools, I'm throwing out some instructions to help you build your own test environment. Being a tech support guy, I am a big fan of having separate machines for production and testing. It can be really cumbersome to try to put both the test and production systems on the same machine. In some cases there is even potential to break the live system by putting a test system right beside it. With that in mind, here is the 20-minute, four-step version of creating a test box. Use it for everything from developing your own custom reports, to testing new coupon schemes, to performing a big batch of merges.

Find a machine
You need a machine first, but it doesn't have to be a real one. The cheapest solution (under $150) is to download the free and excellent VMWare Server. Install Windows XP Professional and SQL 2005 Developer Edition, both with the latest service packs. SQL Dev Edition is only $49 and you can find XP Pro online for under $100.

Install Patron Edge
Since everything is going on one machine, just run the Patron Edge installer on the test machine and hit Next, Next, Finish. Leave everything at the default setting.

Restore some data
Grab last night's backup of your Patron Edge database and restore it onto the test box. Knowledgebase is your friend on this one if you're not very familiar with making and restoring from backups.

Set up credit cards

Take a copy of payment.ini from the live system and put it in C:\Program Files\The Patron Edge on the test box.
While you're in there, find Tix_PSC and do Send to Desktop to make a shortcut.
Install PCCharge (again, it's just a Next, Next, Finish deal).
Edit your PCCharge shortcut and add the /DEMO switch in the Target field. This puts it in demo mode so that cards aren't really charged
Edit your Tix_PSC shortcut and add the /a /online /force switches.

If you add in time to copy files onto the test system and restore the database, it may take a little while depending on your network speed and the size of your database. However, getting PE installed and credit cards working will only take about 20 minutes. Next week we'll do a Patron Edge Online test system in 20 minutes, followed by a Patron Edge/Raiser's Edge integrated system. By the end of the series, you will be able to create a full test environment in just an hour!

What does your test environment look like? Share your best practices in the comments.

Best Practices for PE/RE integration, part III

Nicholai Burton — Tue, 14 Apr 2009 19:41:00 GMT

This is the final part of my three part series on integration between Patron Edge and The Raiser's Edge. In this part, I'm going to go over one of the things that all organizations battle with, regardless of the CRM application used - duplicate records .

Once your database has been around for a while, duplicates start to become a frequent concern. There are two ways to deal with duplicates: preventing them and merging them.

Prevention - The best way to deal with dupes is to keep them from ever getting into the system. It takes more effort upfront but pays off in the long run with less ongoing data cleanup. Prevention requires a two-pronged approach using the features of the software and changes to your business rules.

Software-enforced rules
1. In Raiser's Edge you can define duplicate criteria to prevent duplicate constituents. Use Last Name, First Name and five digits of the ZIP. You can add more criteria, but remember that the more criteria you add, the less likely you are to catch potential dupes when adding a record.
2. In Patron Edge, define Duplication Conditions. Use Last Name, First Name and Email. Frequently this is all you can capture about someone on sign-up sheets or the web. Patron Edge can't do partial field matching, so don't use ZIP code here. However, Raiser's Edge can't match on email address so we take advantage of the PE feature.
3. Patron Edge Online is a different animal altogether because there's not a live person between the system and the patron. You can prevent dupes with a couple of Company settings. If you use the feature, remember the impact on the patron. If I get a popup when buying tickets online that says I already exist, but I don't know what email I used when signing up, I can't log in to use my account and I can't finish the sale as a new person. Catch-22. I either call the box office or give up on the purchase. I'm not saying to avoid the Patron Edge Online dupe prevention feature, just think carefully before implementing it.
Business rules
1. Choose a system of record, either RE or PE. Of course you can enter most data through either system, but when you're dealing with important donors/patrons, make a habit of working on those records from one side as much as possible.
2. Restrict access to add/edit records to those who truly need access. For folks who aren't trained on a system, only allow read access.
3. Train users to always search before creating a new record, and enforce the rule by auditing who is creating duplicate records.

Merging - Some dupes still slipped through, now what? In an integrated system, we use a special merge function that hopefully makes this easy for you. It does remove the move tab features of Raiser's Edge and forces you to only keep one record; we chose to do this in order to keep the data properly synched. The positive side of this rule though is that you can merge in batches. Set your duplication criteria in Raiser's Edge and run the merge from Administration, and you can merge lots of dupes at the same time instead of going one by one.

And that's the final installment of PE/RE best practices. If you got something out of it, leave a comment to let me know and I'll do more of these in the future.

Sweet New Features for Patron Edge

Nicholai Burton — Mon, 30 Mar 2009 20:22:00 GMT

If your organization is running Patron Edge 3.340, patch 2 is now available for download at http://downloads.blackbaud.com. It includes fixes for the following reported issues:

BB575312 - Error: Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
BB584847 - Error: Assertion Failed: unhandled control type - need to add to multiselectcontrolhandler BBBox1
BB581378 - Mastercard recorded as AMEX when a sale is made in Kiosk

That's the boring stuff, now for the really awesome features:

Debit cards - Now you can process debit cards through Patron Edge! Accepting debit cards is a great way to increase your ROI and use the money that you're not paying in fees to further your mission. Consider this scenario:

In a week, you perform 250 in-house transactions totalling $30,000. If you run all of those as credit cards at a 3% fee per transaction, you pay $900 in fees. If you run all of those as online debit cards (meaning you're capturing a PIN) at about $0.60 per transaction, you pay $150 in fees. Repeat for three more weeks, and you've saved yourself $3,000. Obviously not all of your in-house sales will switch from credit to debit, and fees vary based on your merchant services provider, but just looking at ballpark figures you can save yourself thousands a year.

Gift Cards - We have added support into Patron Edge for prepaid, reloadable gift cards. You can now sell your own personal-branded gift cards for patrons to give to their friends and family! Our current partner for this is Valutec .

Gift Certificate/Voucher Report - I wrote about this spiffy new report we've added into the Reports Wizard in this post a couple weeks ago, along with a screencast. It's solid, flexible, and can likely solve any of your reporting needs around vouchers without having to write your own custom reports.

All of these features are included standard in Patron Edge, no additional modules to purchase. To add debit and gift cards, you will want to speak with your merchant services provider to find out what information needs to be added into PCCharge.

Make sure you have a patch plan and can handle the downtime it takes to apply a patch, and don't forget to see Knowledgebase solution BB346664 to make sure any products you integrate with are compatible. If you haven't updated to Patron Edge 3.340 yet, call up your account rep and tell them that you're ready to get these new features!

Patch 11 for Patron Edge 3.310 has been released

Nicholai Burton — Mon, 16 Mar 2009 18:16:00 GMT

Patch 11 for Patron Edge 3.310 is now available for download at http://downloads.blackbaud.com. This patch adds compatibility with the upcoming release of The Raiser's Edge 7.9.

Feature Preview: Gift Certificate/Voucher Report

Nicholai Burton — Fri, 13 Mar 2009 19:18:00 GMT

So I'm always talking about using multimedia to engage your audience online, whether it is telling your story through pictures, audio or video. I decided to walk the talk a little bit and created this brief screencast to give you an idea of what to expect in the next couple of weeks when the next patch drops for Patron Edge 3.34. Our big new features are reloadable gift cards (using Valutec as the processor), debit cards, and a new report called the Gift Certificate/Voucher Report. I'm new to screencasting and couldn't find a microphone while making this, so it's a little awkward as I try to show off the new report using only the screen. Let me know what you think and I can definitely do more of these in the future.

Click here to see the Voucher Report. I'm still experimenting and Youtube made the video too grainy, so use this direct link to view it full size until I find out what I'm doing wrong.

If you haven't talked to your account rep yet to plan your upgrade to 3.34, I recommend doing so to take advantage of all the new features in both PE and PEO. System requirements for these new versions have changed significantly though, so be sure to check out the latest requirements here.

Patron Edge 3.310 patch 10 is now available

Nicholai Burton — Tue, 24 Feb 2009 21:48:00 GMT

Happy integration patch day! Patch 10 for Patron Edge 3.310 is now available for download at http://downloads.blackbaud.com and includes fixes for the following reported issues:

BB350540 - Error synchronizing membership for : Field is read only: Type
BB353804 - Memberships do not sync properly for specific records
BB403754 - Error synchronizing membership for : Expires on required if not a Lifetime Membership
BB417248 - Error: Error synchronizing membership for [XXXXX] Membership type [XX] is inactive in the Patron Edge
BB437126 - Spousal relationships do not sync properly
BB461306 - PE email addresses not synchronizing into RE
BB469214 - Error: 'Error merging from PE: Merges are not allowed outside of the Blackbaud merge application'
BB516866 - Incorrect number of transactions appear on Ticket Sales Commissions report detailed by event