I am having a difficult time wrapping my brain around pulling names for any given custom report. From what I am seeing, there could be 3.5 scenarios that I could be presented with. What I mean is the type of customer (Tr_cust_group) that could be given to me.
Individual
Organization
Household
Hey wait! That’s only 3, I said 3.5. The extra .5 is where I could be given an individual customer that could be part of a household depending on the type of report I am pulling. :)
What I am looking for is the best ways to pull the first and last names and not just the salutation. I think it is going to come down to the type of report I am pulling. Such as, ticket orders and contributions. Since these are at the household level, I wouldn’t be given an individual customer number that is part of a household. I would always be passed the household customer number. Please correct me if I am wrong here.
So in this case, I can get to the first and last names of the primary affiliates in a fairly easy fashion. But if it is just an Individual, there is no affiliation so should these records be handled differently and with a different query or can I get to these names using the same query as the household query but maybe using a left join?
Another case I am looking at is say elevated events. Here you can put the event in any of my 3.5 cases. Now one can assume that if the event is placed on an individual record that is part of a household, only pull the name of the individual but I am pretty sure that won’t fly. So am I looking at 3+ queries just to get the names?
Any help would be appreciated,
Thanks,
Marty
Chuck.
Thanks for the reminder about VB_CUSTOMER. The training did cover this. However, I reviewed this several months ago so maybe I need to re-review this again.
That said when I
select * from T_CUSTOMER where lname2 is not null
in our live system I get about 16,000 records. And when I
select * from VB_CUSTOMER where lname2 is not null
in our upgrade encironment we get only about 9,000 records.
Are there any broad general reasons why we should expect broad differents in these two sets of data. I would expect a few records different because Live has the lastest data. However an increase of this percentage seems very unlikely.
If there are not any general considerations that might make this level of diffrence. I take this up with the support team?
--Tom
From: Tessitura Next Generation Forum [mailto:forums-nextgeneration@tessituranetwork.com] On Behalf Of Chuck ReifSent: Wednesday, April 11, 2012 2:35 PMTo: Thomas BrownSubject: Re: [Tessitura Next Generation Forum] Getting the Names
There's always the bridge view VB_CUSTOMER which basically does exactly what Marty is talking about. It's the union of three queries based on the type of constituent.
From: Marty Jones <bounce-martyjones7649@tessituranetwork.com>Sent: 4/11/2012 1:28:00 PM
I feel that in most cases, the salutations would work. It is those cases where we want to know the full name of those individuals such as an donor event. What I am looking for is if there is an easy way for me to get those full names? I am getting the feeling that for those reports that require the full names that I would have to write 2 to 3 queries to get the information.
Query 1: If cust_group = Household then join to T_Affiliation then back to T_customer to get names of the 2 primary affiliates.
Query 2: If cust_group = Individual then just get the name from T_Customer
Query 2.5 If cust_group = Individual and customer is a primary affiliate of a houshold, Get back to the household and the get the names in a similar fashion as query 1. *Done on a case by case basis if required by report
Query 3 If cust_group = Organization then get the name from the salutation or if business rules allow only one primary affiliate for these type of records, get the name by going through the t_affiliation and so forth.
I was just hoping that there was an easy way to do it that I haven't found yet.
You were sent this message automatically by www.tessituranetwork.com because you subscribed to the Tessitura Next Generation forum email notifications. You may reply to this message or visit the site to reply to the post above. If replying via email, please consider deleting the previous message text before sending to help with readability on the site. Thank you!
Tom, there are a few reasons why I can imagine this happening, mostly due to how you might have set up your conversion rules. I would suggest that you find a few constituents that have this discrepancy and investigate them individually. You could pretty easily do this by linking the servers together and running a query across the servers. I would also make sure that empty values in lname2 aren't messing up your calculations so I might do something like this instead:
select count(*) from <v11>.impresario.dbo.T_CUSTOMER where Coalesce(lname2, '') = ''
select count(*) from <v10>.impresario.dbo.VB_CUSTOMER where Coalesce(lname2, '') = ''