List Question: Replace Household with A1

I'm sure it's possible in SQL but my dumdum way would be to replace individuals with household (if exists) AND add all primary affiliates, then add an output element of a1/a2 indicator and filter by a1. I think that should work? 

just tinkering around, you might need to experiment with the Primary Affiliate filter as well. 

That's a good idea! I'm off to do some experimenting with that. Thanks, Eric!

Hi Lauren,

I did something similar the other day when I needed to have columns for each name on a household record.  I did this in Management Studio and you mentioned you could do this with SQL so sharing what i did.  I first created a temp table of the accounts on a list.  I then created a 2nd temp table and inserted into this the customer_no, and if it was a HH i put in the A1 name in one column and the A2 name in another, as well as a column for the individual.  

select t.customer_no, c.cust_type,
max (case when c.cust_type= 1 then t.customer_no else null end) as IND,
Max(CASE when c.cust_type = 9 and af.name_ind in ( -1)
then af.individual_customer_no else null end) as 'HHA1'   ----also had case statement for the A2 name which you could add

into #TempAcctNos
from #Temp t
join T_CUSTOMER c on t.customer_no = c.customer_no
left join T_AFFILIATION af on t.customer_no = af.group_customer_no
group by t.customer_no, c.cust_type
order by t.customer_no

From this I pulled in the names from t_customer for each of the columns:  IND, HHA1 and HHA2.  Below is code i used for pulling in the A1 names.  To pull in IND, change ta.HHA1 to ta.IND and the cust type to 1.

select ta.customer_no, c.fname, c.mname, c.lname, s.description
from #TempAcctNos ta
join T_CUSTOMER c on ta.HHA1 = c.customer_no
join TR_SUFFIX s on c.suffix = s.id
where ta.cust_type =9

Hope this gets you on your way.

T.C.

Pittsburgh Cultural Trust Consortium

Thanks for this, T.C.!

VS_CUSTOMER_WITH_PRIMARY_AFFILIATES probably will give you what you need, and I think could be easily worked into list manager with a manual query,  but should mention there's also an affiliates info output element in the cookbook (includes script for a different view with the details of affilates' phones and eaddresses). www.tessituranetwork.com/.../Tessitura.htm

You can use the Advanced Relationship Options to do this.

First you'll need to create a list of A1 constituents. To do this you'll need to either add a new criterion in T_KEYWORD or do this with manually editing the query. If you think you'll need this more than once, I'd add the criterion.

The Detail Table is VS_RELATIONSHIP
The Detail Column is name_ind, which is where the A1/A2 indicator is stored
The Data Type is Number

(Because it's late on Friday and no one was around to ask, I don't know how to set this up as a criterion with a dropdown value field, because I'm not sure if/where the A1/A2 values are stored. But if they are stored in a table somewhere you could do that)

Once you have that criterion, create a list using that criterion with a value of -1, which corresponds to A1. This will create a list of everyone who has an A1 affiliation. This will include both sides of the affiliation, both the individuals and the households. Because of how we are going to use the list, that doesn't matter, but if you wanted to be really clean, you could throw in a second criterion for constituent type and limit it to Individual constituents.

Once you have that list, go to your real data pull list or segment. In the Advanced Relationship Options for the criteria set for the list or segment, set Add Affiliated Individuals to the affiliation type you are using for households, probably Adult Member. The in the same section set Filtered By List to the list of A1 constituents we created. Finally, in the section at the bottom select Keep original constituent if no related constituents are found. That will mean any households will get replaced by their A1 constituents but anyone else (anyone who doesn't have an A1 affiliate) will be left on the list. If you wanted to dump anyone who doesn't have an A1 affiliate (which would be plain Individuals and organizations) you could select Only keep the related constituents that were added.

I hope that helps. I'll see if I can find out about the A1/A2 dropdown values next week and update the post with what I find.

Have a good weekend!

Wow, Kevin, this worked beautifully! Thank you! I've never used the 'Filter by List' feature in the 'Add Affiliated...' section before, but that is really handy. Thanks again for sharing this alchemy! 

Great! I'm glad that worked for you.

I did a little more digging, and if you want to turn the value into a dropdown where you can choose A1 or A2 instead of typing in an ID number, here's what you'll need to do:

First you'll need to add two new entries to TR_GOOESOFT_DROPDOWN. This is how we'll define the Description/ID combos we need for the value dropdown. When you add entries to this table, you need to use a Code value 1000 or greater, and use the same value for both entries. For the ID value, set it to -1 for the A1 entry and -2 for the A2 entry. Then put in A1 and A2 for the descriptions.

Once you have those set, go back to T_KEYWORD and update the criterion you added. Set the Ref Tbl to TR_GOOESOFT_DROPDOWN. Set the Ref IDCol to ID. Set Ref Descol to Description. And set Ref Where to Code = (the value you used when you added the entries).