Relationships are complicated.

PE has a relationships feature, which can link clients to each other using relationship types (and reciprocal types...) - Mostly I use these at my place of work to track seat-mates and spouses. If you have a large group of people that like to attend together, but pay separately, this is one way to handle the data.  The problem is that it is difficult to report on this data, since you can end up with any number of structures depending on how the links are entered. Ideally you want something that looks like an asterisk, with one group leader as the center that links to everyone else.  Usually this is not the case - you end up with circles, trees, and other shapes that look like tangles of string. Here is a script I wrote that will take this and put into groups of patrons, using the person with the most connections as the leader (or if it is a circular link, the lowest account number is chosen.)  If one were to run this on facebook, it would put 99% of people in Tom's group (or whoever that default friend dude is) - but these are closed groups of seat-mates, so not everyone is linked. (seven seatmates to Kevin Bacon anyone?)

Anyway- here are the goods - this takes the relationship type as input.  This script assumes the same reciprocal and main relationship type for each link.
declare @rtype int

set @rtype = 5

create table #l (cltcode int, link int, lnkgrp int, lnkgrpleader int)

insert into #l (cltcode,link)
select relcltparentid, relcltrelationid from relationships where relfromtype = @rtype or reltotype = @rtype
union select relcltrelationid , relcltparentid from relationships where relfromtype = @rtype or reltotype = @rtype

update #l set lnkgrp = case when cltcode < link then cltcode else link end
declare @i int
set @i = 0
while (
exists (select 1 from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp)
or exists (select 1 from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp)
) and @i < 10 --limit loop to 10 iterations (degrees of separation).
begin
update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select cltcode, lnkgrp from #l) as l2 on l2.cltcode = l1.link and l2.lnkgrp < l1.lnkgrp

update l1 set lnkgrp = l2.lnkgrp
from #l l1 inner join (select link, lnkgrp from #l) as l2 on l2.link = l1.cltcode and l2.lnkgrp < l1.lnkgrp
set @i = @i + 1
end

update #l set lnkgrpleader = lm.gpwinner
from #l as l1 inner join
(select l1.cltcode,
(select top 1 cltcode from #l as l2 where l2.lnkgrp = l1.lnkgrp group by cltcode order by count(*) desc, cltcode ) as gpwinner
from #l as l1)
as lm on lm.cltcode = l1.cltcode

create table #gl (acct int , lnkgrp int, isgroupleader bit, lname varchar(999), fname varchar(999))
insert #gl (acct, lnkgrp, isgroupleader)
select cltcode, lnkgrp,
case when cltcode = lnkgrpleader then 1 else 0 end as isgroupleader
from #l group by cltcode, lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end
order by lnkgrp, case when cltcode = lnkgrpleader then 1 else 0 end desc, cltcode

update #gl set lname = cltsurname, fname = cltfirstname
from #gl inner join clients on cltcode = acct

--select acct from #gl group by acct having count(*) > 1

select * from #gl

drop table #gl, #l

Remote Ticketing and Printing ~~~ tips and tricks

I recently had to look into options for doing off-site ticketing, with printers this time. There are many to do this. I tested quite a few of them, and here are the results:
There are many ways to connect and use PE: VPN, RDP, open the application ports on the firewall and do a public NAT for your PE app server (I didn't test this, since it could be a security risk). The option I settled on was RDP, which supports redirection of printers and ports (see next section for printing...) Testing over a VPN connection I noticed a sluggishness in PE - which I assume is due to the network latency. There is not much data going back and fourth, but instead hundreds of little requests to the app server every minute. When you add a 100 millisecond latency to each request, it really adds up to a bad user experience.  I assume that opening the application on the firewall would have the same results.  So - RDP it is then.  RDP is only sending bitmap image data, which may be more data, but the application will run much faster. I use a SSL-VPN 2000 from sonicwall to manage the connectivity, and a couple of XP virtual machines as the hosts.  The bandwidth used per workstation comes to about (from the host network's point of view) 6KBps ingress and 1.5KBps egress (or 48 Kbps up / 12Kbps down if you count in bits).  These are average numbers - the traffic fluctuates with use, so plan on twice the total for a good experience.

Printing
Now for the tricky part, as your ticket printer probably has a parallel port, and your laptop probably does not have a parallel port.  In addition, the "forward printers" feature in RDP will not create a permanent printer name for tix_printserver to use

1. First, I got an adapter cable to connect my boca miniMB as a usb printer. 
2. In order to print anything you need to install the printer on the local laptop.  I use the boca drivers for this, but it may not matter which driver you use, since it is just going to forward raw port data to the printer. 
3. After setting up the usb printer, go to printer properties > ports > check the "enable printer pooling" checkbox, and  also check the "com 3" port.  There will now be 2 ports checked; USB001 as the first, and COM3. 
4. Now on the host machine (the one you RDP to) create a local printer, and have it point to COM3.  Use the printer driver for the ticket printer.  
5. Configure your RDP terminal services connection to "forward ports".  No need to "forward printers", since we are doing it manually.
6. Print a test ticket on the host printer and also the local laptop printer.  Both should print a test ticket.
7. On the host machine, start tix_printserver.exe, and point it at the printer you just created.

 Other notes:

• If COM3 is forwarding, check device manager on the host, and disable the COM3 port if present.  This will force the host OS to send it to the RDP COM3 instead of a physical COM3.
• If your laptop has an LPT port, you can set the host to print to LPT1, and it will send it directly to the laptop's lpt1 port (without the need to install a printer on the laptop)  You will need to disable LPT1 on the host device manager > ports.

The only configuration required from users after all this setup is to start the tix_printserver.exe application once logged in.  (see BB docs about tix print server, the syntax is something like "C:\Program Files\Blackbaud\The Patron Edge\TIX_PrintServer.exe" /id36 /a /min) The salespoint configured for the host machine will be set to point to the printer you just set up, or perhaps another host's printer if you wanted to share printers.