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
Unknown said: I am having a difficult time wrapping my brain around pulling names for any given custom report.
I've been working on similar things. I have a favorite view that I use for a lot of custom reports that I could share, if you want. To get a sort of n1/n2-ish pull of "salutation name + virtual n1/n2 names", which lots of existing reports want, I do a couple left joins to subqueries to T_AFFILIATION.
Chris & Marty,
In general we are struggling with the same set of issues here. How do we consistently handle customer Names in an elegant way across the reports we need to repair.
Marty, the Custom view you are working on could be of great help. I know that we created such a view and use it in a number of current reports in Version 10 and before. However we have not gotten to the repair stage for this view.
Some of the particular challenges, our users have been seeing when pulling Version 11 reports and seeing the mix of accounts with First Name, Last Name and household names. This is causing a variety of problems. Including:
Regarding solutions, I'm wondering if we need to be using Salutations more often. Or, If I'm not yet aware of some view that pulls this together in a real nice way. Or if that would just cause more problems down the road.
All that said I'm wondering if this is an issue that may be too complicated for a bunch of emails here in a post. Anyone interested in a Google Hang Out (or something like that) to sort or bat around some ideas / approaches about this subject? We are all going to have to deal with these issues. And it would be great if we came up with similar solutions.
A couple points here:
1. In general Tom is right and you should try to use Salutations when possible. I think that will solve some of the problem.
2. Also, you should try to add the sort_name column from T_CUSTOMER to many reports and use that for sorting. That is what we did to many standard reports for v11. Even if you never display that column return it and reference it in the heading of your name column so that you can sort by it.
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.
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.
Also, quick minor point for those in the TSQL that T_CUSTOMER.cust_group has been deprecated in favor of TR_CUST_TYPE.cust_group.
Relating to the problem of the display of "Mary and John Smith Household" -- we too are finding that we have to rewrite reports so that the word "Household" is not seen. In some cases a Salutation fixes this.
But the use case I have right now is admittedly a backward-thinking one, but a real one nonetheless-- how do i get the A1 first name in a simple output set, when we just want to say "Dear Mary", because the communications have always said "Dear Mary"?
I suppose the "correct" way would be to have an informal salutation type with rules in TR_SALUTATION_FORMAT around which names to show, in this case, on the "inside" sal...and to somehow automatically generate that salutation for every record in the system.
But the solution I'm testing now is a very simple one -- just replacing T_CUSTOMER with VB_CUSTOMER in TR_QUERY_ELEMENT for the Customer_lname and Customer_fname fields. This seems like a viable and painless workaround. But i'm posting here in case anyone has a gotcha or a cleaner way to do this other than the Salutation route.
Michael
Normal 0 false false false EN-US X-NONE X-NONE
Hi Michael,
In this example, if you were going to send a letter addressed just to Mary, you should be pulling her individual record, not the household record, which would then output her individual name or salutation. This type of situation, when you want to communicate with only one member of the household, is one of the main drivers of the new constituent model.
If you want to add an individual record to your list but some or all the of data your criteria are looking at is on the household record there are several features that allow you to do so that have been added to criteria sets. For details see the Household Criteria Considerations and Advanced Relationship Options help topics.
Also, just a reminder that there is already a predefined salutation field for the inside letter address line. It's the Dear field you see on the General tab, which is formatted according to the LI columns in TR_SALUTATION_FORMAT.
-Kevin
The link to the Household Criteria Considerations topic was bad in my last post. Here's the correct link: Household Criteria Considerations.