Hello! I'm not sure what I'm asking is possible, so I figured some great mind in the community might be able to help me figure it out.
The ballet academy branch of our company uses a different database for students, tuition, schedules, etc. We are wanting to find the parents of academy students in Tessitura and give them a constituency to signify them as academy parents. Unfortunately because they use a different database, I do not have ID numbers for each constituent, only their name, address, email, and phone number.
Is there anyway for Tessitura to find these constituents without their ID to make a list for a constituency update, or will it need to be done manually one by one by searching their info?
You can build a manual list by searching for them individually, and then you can apply the constituency to the list.
In theory a properly configured Constituent Import would be able to match most customer records, and simultaneously apply a defined Constituency. There's a bit of setup for that, so you'd want to know how many records you were talking about to assess the time cost either way.
It's roughly 600 records to import. Is the bit of setup for running the Constituent Import utility or is there more back end to be done?
Okay, I haven't done this in ages, and not for this purpose, and it looks like it's a bit more tricky than I though...I think it's the Order/Contribution Import which try to match new information to existing Constituents.
https://www.tessituranetwork.com/Help_System_v151h/Tessitura.htm#Constituent%20Import/Constituent%20Import.htm
With the Const Import you have the option to create or skip duplicates. Maybe you could use the utility to create new records where they are not in your Tessitura database, and then use the duplicate error output to create a list?
On the heels of Gawain's suggestion, do you currently have a "link" between the two systems? From what I read, I seems like you don't.
Just to ensure I understand what you are attempting and keeping Gawain's suggestion in mind, I'm going to restate what you're asking to clarify your question.
You have two separate databases that you use for two different functions and using Gawain's suggestion, you can ensure both have the constituents listed in each. Also, If you currently do not have link between the ID number in the "ballet academy company" database to their Tessitura database ID.one suggestion I could make is to create an attribute in Tessitura for the "ballet academy company" ID and set that attribute value to be the ID number in the "ballet academy company" database for the related constituent.
Doing this will give you a "link" between the ID in the "ballet academy company" and Tessitura. You could then use this attribute to create the constituency update list.
Might be less messy this way. Hope this helps.
Our academy is managed in Tessitura now, but the was Phillip explained things is exactly how we did it with our previous system. It took manual work periodically, but it was worth it.
We do still use one third party app for open adult students - we just need to maintain the third party app IDs in Tess and then vice versa, their Tess IDs in the app.
I've done constituent import more recently. I usually begin by making a query list using eaddresses, and tend that list of already in the system manually. Follow up with just the ones not found by eaddress. Out of the box, Constituent import just matches on name and postal address. Eaddress is more distinctive. Agree with the comments about introducing a crossover ID using maybe an attribute for the unique ID in the other database, but eaddresses tend to mostly be unique to non-dupe accounts. DETAILS I use excel to create the ,'emailaddress' for a list manager list like the following. I fill down ,' to the left of eaddresses and ' to the right, then concatenate and paste into a list. NOTE, if you're on Office365, the first ' requires '' to render.
SELECT DISTINCT a.customer_no FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK) INNER JOIN (SELECT a1.customer_no FROM vs_eaddress AS a1 WITH (NOLOCK) WHERE a1.address in ( 'huffer@gmail.com' ,'puffer@yahoo.com' ,'blower@outlook.com' ) -- and ISNULL(a1.inactive, 'N') IN ('N') ) AS e ON e.customer_no = a.customer_no WHERE a.inactive = 1
Clarifying, that the ' in front fo the first eaddress required two apostrophes in Office365 (not quotation mark), and likewise the following apostrophe. Comma apostrophe ,' doesn't require two apostrophes. Also 600 eaddresses could over run the listmanager 4k character limit, so you might have to break your 600 parent eaddresses into multiple copies of the list with a distinct group of eaddresses in each. We run Constituent Import for our academy parents periodically and assign an attribute, but don't make any effort beyond name and eaddress to join the separate databases. But we do it only after all the performances where caretakers get tickets, so the attribute typically goes in during that process.
Where we really benefited from this process, though, was while we were using Eventbrite to sell streaming tickets during the pandemic. Constituent import can be quite cranky, and being able to query in listmanager on emails was a huge time savings.