Associations from HH in output sets

Hi Claire,

Your timing is excellent--I just dealt with this issue this morning and then happened to see your post by chance.

I asked TASK about this a couple of months ago, and was told that they've provided a bridge view called VB_CUSTOMER which replicates the functionality of the old-style records so you can continue using output sets that include name2 information.

I updated all of my Customer elements in TR_QUERY_ELEMENT to use VB_CUSTOMER in the "Data Where" column, and everything seemed fine until yesterday.  In almost all cases, our Output sets are handling data from Household records or Individuals who don't have households, and there are no problems there.  It turns out, though, that the bridge view doesn't work if you need to export an individual who IS affiliated to a household.  In our case, we discovered the problem when we needed a complete list of deceased people, and a lot of them are attached to households.

So yesterday I wrote a new custom View, and this morning I updated all of the relevant TR_QUERY_ELEMENT entries.  Essentially what it does is for T_CUSTOMER records which have A1 or A2 records affiliated to them, it replaces the name and biographical fields for the N1 slot with the values from the A1 record, and those for the N2 slot with the values from the A2 record. I also pull in the descriptions from the key reference tables so I don't have to mess around with joins in the TR_QUERY_ELEMENT record.  As far as I know, it works, although obviously it hasn't stood the test of time.

Anyway, I've appended the code for my View below in case anybody is interested.

-Galen

select
c.customer_no   
,cust_group=(select description from TR_CUST_GROUP where id= c.cust_group)   
,cust_type=(select description from TR_CUST_TYPE where id= c.cust_type)   
,name1_cust_no=ca1.customer_no
,prefix=(select description from TR_PREFIX where id=ca1.prefix)
,ca1.fname
,ca1.mname
,ca1.lname
,suffix=(select description from TR_SUFFIX where id=ca1.suffix)
,ca1.sex   
,name_status=(select description from TR_NAMESTATUS where id=ca1.name_status)
,name2_cust_no=ca2.customer_no
,prefix2=(select description from TR_PREFIX where id=ca2.prefix)
,fname2=ca2.fname
,mname2=ca2.mname
,lname2=ca2.lname
,suffix2=(select description from TR_SUFFIX where id=ca2.suffix)
,sex2=ca2.sex
,name2_status=(select description from TR_NAMESTATUS where id=ca2.name_status)
,c.original_source_no   
,c.mail_ind   
,c.phone_ind   
,c.last_activity_dt   
,c.last_gift_dt   
,c.last_ticket_dt   
,c.emarket_ind   
,c.primary_address_no   
,c.email   
,c.inactive   
,c.inactive_reason   
,c.n1n2_format   
,c.create_loc   
,c.created_by   
,c.create_dt   
,c.last_updated_by   
,c.last_update_dt   
,c.sort_name   
from T_CUSTOMER c
left join T_AFFILIATION a1 on c.customer_no=a1.group_customer_no and a1.name_ind=-1
left join T_CUSTOMER ca1 on isnull(a1.individual_customer_no, c.customer_no)=ca1.customer_no
left join T_AFFILIATION a2 on c.customer_no=a2.group_customer_no and a2.name_ind=-2
left join T_CUSTOMER ca2 on a2.individual_customer_no=ca2.customer_no