Hi all,
I'm working on a somewhat unique data pull and in cases where a household qualifies under the criteria I have set, I'd like the household to be replaced with just the A1 affiliate of that household (in other words, if the John and Jane Smith Household qualifies, I'd like my results to list John's individual record only). Essentially, I'd like to do the opposite of the "Replace individual constituents with their primary households, if one exists" functionality that List Manager offers. This is something that I am happy to do either in List Manager or in a SQL query, so ideas from either avenue are welcome. Thanks for any insight you can provide!
Lauren
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_RELATIONSHIPThe Detail Column is name_ind, which is where the A1/A2 indicator is storedThe 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).