How can we add criteria to Output Set Builder for the following Affiliate names for Households contained in the list?
A1 First Name
A1 Last Name
A2 First Name
A2 Last Name
Is this possible?
Thanks,
Chuck
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_from201 Customer Last Name2 101 lname2 VB_CUSTOMER200 Customer Last Name 101 lname VB_CUSTOMER199 Customer First Name2 101 fname2 VB_CUSTOMER198 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.