Adding A1 & A2 Names to Output Sets

We have just put these into the TR_QUERY_ELEMENT system table based off of the view VB_Customer. Haven't run into any problems yet but YMMV.  I'd definitely advise waiting until you have the newest VB_Customer view from the 11.0.4 patch as the 11.0 VB_Customer view was performing poorly here.

 id      description                            category           data_select            data_from
201   Customer  Last Name2      101                    lname2                  VB_CUSTOMER
200   Customer  Last Name        101                    lname                     VB_CUSTOMER
199   Customer  First Name2      101                    fname2                   VB_CUSTOMER
198   Customer  First Name        101                    fname                      VB_CUSTOMER

Hmm, it looks like we already have these in TR_QUERY_ELEMENT carried over from v10.  I believe we are on the newest version of v11 as we just went live a few weeks ago. 

My issue is that if a household account is on the list, the output set criteria for Customer First Name and Customer Last Name does not pull A1 information as we would like but instead a blank for First Name and the Household name for Last Name. 

Looks like something like this will work: first, add a view similar to:

create view lv_household_w_affils as

select h.customer_no,

max(a1.customer_no) "a1_cust_no",

max(a1.fname) "a1_fname",

max(a1.lname) "a1_lname",

max(a2.customer_no) "a2_cust_no",

max(a2.fname) "a2_fname",

max(a2.lname) "a2_lname"

from V_CUSTOMER_WITH_HOUSEHOLD h

join T_CUSTOMER cu

on h.customer_no = cu.customer_no and cu.cust_type = 7

left join T_CUSTOMER a1

on a1.customer_no = h.expanded_customer_no and h.name_ind = -1

left join T_CUSTOMER a2

on a2.customer_no = h.expanded_customer_no and h.name_ind = -2

--where h.customer_no = 9143 --testing

group by h.customer_no

Then add four rows to TR_QUERY_ELEMENT:

description data_select data_from

A1 Affil FName !.a1_fname lv_household_w_affils

A1 Affil LName !.a1_lname lv_household_w_affils

A2 Affil FName !.a2_fname lv_household_w_affils

A2 Affil LName !.a2_lname lv_household_w_affils

(I Hope the above makes sense given the forum formatting.)

This worked for us.  We only had to change cu.cust_type = 7 to cu.cust_type = 17 for our client.

As a note to others, if your list includes both Households and Individuals - you will still need criteria to pull names for both and do some massaging in Excel if you want A1 First Name for a Household account to be in the same column as the First Name for an Individual account.

Is the cust_type the household or individual?

Hi Chris,

I set this up as you described and it works great for households but individual records (I added that cust_type) don't show A1/A2 (obviously). If I include the Lname in the output set then I get individual names but also the household names. Did you run into this?

Unknown said:

Hi Chris,

I set this up as you described and it works great for households but individual records (I added that cust_type) don't show A1/A2 (obviously). If I include the Lname in the output set then I get individual names but also the household names. Did you run into this?

Yes, it was just a quick sample, intended for pulling A1/A2 names specifically for households. Yes, the cust_type specified above is for the households, and Chuck had to change it apparently due to custom cust_types at his org. 

Another view could be made that would include all names for all constituents, but it could be done so many ways, it might be outside the scope of this thread. If you want, e-mail me offline and I could suggest some code for that.

Hi all,

We build a custom version of VB_CUSTOMER that performs better (the 11.0.4 version still was very slow for us), and doesn't eliminate certain records. For example, we found that VB_CUSTOMER would not include A1 and A2 primary affiliate individuals, which is not good in many situations.  (Try doing select vb_customer where customer_no = <A1>, where <A1> is the customer_no of an A1 affiliate record in your database and you'll see what I mean.)  

Basically, we wanted a clean and simple way to pull customer name data in a fairly consistent manner. For example, fname column in this view is either the first name of an individual record or the A1 first name of the household record (i.e. you don't have to look in one column for households and a separate column for individual records). It also includes a few other helpful things, for example, it will build sort_name dynamically if sort_name happens to be null (which we found is the case for some of our records intermittently). 

We then added additional objects to TR_QUERY_ELEMENT to pull from this view, then use those in output sets. We also use this view in many of the stored procedures we use for our reports.

Anyway, this may not work for all situations - for example it does not add any affiliate data for organizations at this point. I placed the code in my file space in case it is of interest. Click here to get it.

Thanks,
David 

Great!

Thank you.

Years later and I am so very grateful to have stumbled upon this. Thanks Chris! 

Madeline Dummerth (Past Member) said:

Thanks Chris!

You're welcome!