Hi,
We are working on pulling a list for an invitation that will include individuals as well as organizations (with contact names). In Tessitura, for several organizations, we have more than one primary contact as we would like each of these people to receive major mailing information. We are having difficulty pulling a list where the output will include the following columns: organization name, primary contact name, address, etc. If an organization has several contacts, the list should pull in several lines for the organization, each listing a different contact name. Does anyone has any experience with this?
Tina,
We do this often at the perot by using a custom output set element for Affiliation Type with a parameter selection. Once you have this it is easy to run a list with the company and then export an output set like what you are describing.
Here is the recipe we used to set it up if you want to have your database person or superuser insert it into your TEST enviroment.
TR_QUERY_ELEMENT
Description: Affiliation Type*
Category: Constituent
Data Select: !.esal1_desc
Data From: LVS_AFFILIATION_OUTPUT
Data Where: affiliation_type_id = <<p##>> (The ## should be replaced with the table ID of the parameter set up in TR_QUERY_ELEMENT_PARAMETER) or you can hard code it to one Affiliation Type only by using Data Where: affiliation_type_id = #### where the numbers are the affiliation type ID from table TR_AFFILIATION_TYPE.
TR_QUERY_ELEMENT_PARAMETER
Description: Affiliation Type
Data Type: String
End of Day: Unchecked
Multi Select: Unchecked
Ref Tbl: vrs_affiliation_type
Ref ID: id
Ref Desc: description
Ref Where: (Leave blank)
Ref Sort: description
______________________
Hope this helps.
Sadie
Hi Tina:
One way to do this is to separate this into two lists (or two different segments in one extraction).
The first segment can be your regular criteria plus "Constituent Type IN Individual/Household". That way you are ONLY pulling records that belong to people that fit your criteria.
The second segment will be your regular criteria plus "Constituent Type IN everything that's not Individual/Household" so you will get foundations/corporations/etc.
Since this is a list of the institutions themselves, you must do one more step in this segment: go to Add Individuals and add your Primary Contacts, then in Keep/Replace Individuals, Only keep the related constituents that were added. Now the list will only be Primary Contacts and will return more than one for each organization.
Depending on how you format your contact records, you might be done. We have the organization name as part of the address for each contact. If you don't do that, I know it's possible to create a custom output set element to pull the name of an affiliated organization, but I don't know exactly how to do so.
-- Mike
There's a built-in feature that I don't think has been mentioned here. If you are using an extraction, there is special behavior that will occur when doing affiliation substitutions with affiliations that have addresses linked on them. It works as follows:
If you select a group constituent with your segment, use "add individuals" to select the individuals for the desired affiliation types. When you extract, you will get rows for each individual with an affiliation to the group, and the address output will be the one present on the affiliation record.
We were doing this and noticed that we needed to have the organization name included in this output, which we accomplished by reserving the street3 address field for the organization name, which is populated automatically by a database job and not accessible to the client interface.
We use affiliation types for this, affiliating the individual employee record to the umbrella company. We have “Head of Company”, “Primary Development Contact”, “Secondary Development Contact”.
We also have specific salutations set up for these so that we can pull:
Mr. David Smith
ABC Company
1234 Main St
Anywhere, USA 11111
Marie
---
Marie Kocher | Development Assistant
Kansas City Ballet | Todd Bolender Center for Dance & Creativity
500 W. Pershing Road, Kansas City, MO 64108
816.216.5582 (D) | 816.931.2232 x1382 (O) | 816.931.1172 (fax)
kcballet.org
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Tina Immink Sent: Wednesday, April 26, 2017 9:39 AM To: Marie Kocher <mkocher@kcballet.org> Subject: [Tessitura Development Forum] Report/list to include organization and contact
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
We considered doing that, but realized that a constituent can only have one of each salutation type on a record, whereas a constituent can have an affiliation to many different organizations and any constituent can have an unlimited number of address records, any of which can be selected on an affiliation record.
So, if a constituent was affiliated to multiple organizations, we would need to have a salutation type for each org on the individual in order to put each org's name in the salutation. In our way, the org name comes from the org record, and we don't have to do any extra work on the individual record outside of the affiliation itself.
My apologies, I should have followed that up with how we get those names.
When we pull our lists for the Company records, we use the relationship tab options at the bottom of the list manager builder screen to Include Affiliations. Then we choose the types we need.
The caveat is that this will return a number of records on your list and will require someone double checking the spreadsheet:
· Company – where the donation/sponsor information lives
· Additional Record(s) for “Primary Development Contact” (depending on how many you have affiliated)
· Additional Record(s) for “Head of Company” (ditto)
· Additional Record(s) for “Secondary Development Contact”
As long as the addresses are similar, or the Company name is entered into the salutation, you can sort to get those records grouped together.
Thanks,
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Marie Kocher Sent: Wednesday, April 26, 2017 11:12 AM To: Marie Kocher <mkocher@kcballet.org> Subject: RE: [Tessitura Development Forum] Report/list to include organization and contact
The database job was a good fix – wondering though just as the Affiliation pull – results in the Affilation address coded in the Affilation record couldn’ t you also code the saluation with the organizations name in it on Sal2? Or is that need for job title
From: Nick Reilingh [mailto:bounce-nicholasreilingh4883@tessituranetwork.com] Sent: Wednesday, April 26, 2017 12:41 PM To: McKinley, Leslie <LMcKinley@nycitycenter.org> Subject: Re: [Tessitura Development Forum] Report/list to include organization and contact
From: Tina Immink <bounce-tinaimmink2181@tessituranetwork.com> Sent: 4/26/2017 10:32:17 AM
I realize this is an old post, but we are doing the same. We're using 15.01, and pulling through an extraction, but the Title field in the Affiliation is not included. I'm wondering if you needed this and created an Output element for it. If so, can you share it? Many thanks.