**Self-Hosted**
Does anyone have a way to find the corresponding customer_no's for a large number of email addresses that lives in the client? I have a way that works now, that is in an Excel spreadsheet, that queries Impresario (the user puts the email addresses on one tab and then the id's are produced when they press refresh on another), but this solution requires the user to have impresario user permissions to a specific view in impresario.
I really need a solution that can live in the client and can be accessible via "reports and utilities" so that I don't have to give the permissions at the SSMS impresario level.
Any thoughts?
We are getting all these email addresses from people who attend zoom events and such (large numbers, like hundreds of them) and my coworkers need to know the Tess ID's to match those so they can make updates to the records (like marking an activity as attended).
Like I said, the current solution works but we don't want to keep doling out impresario permissions outside of the client, and it is increasing the workload for the few people who already have it. AND we have enough things here at SLSO that operate outside the client (Like that front of house Access form I have been asking for help with in the Dev forum). Managing yet another is not ideal.
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
Hi Ashley! I have a suggestion for you. We are going to "color outside the lines" a bit so stick with me. First, check out this import video series, specifically Part 3: Additional Import Tips: Better Constituent Matching with External IDs. It describes how you can store external emails, like ones used to register for a Zoom event, as attributes on constituent records, and then use them as a lookup key with the import utilities. (The emails can of course also be stored as regular emails.)
Once you have the data in place (this could be imported via the Constituent Import Utility for example), you can use the Order Import Utility to search for constituents, even if you don't intend to create an order. If you try to import a data file that only contains email addresses in the import_account_id column, and no order data, it will look like this in the import utility in Review mode:
As you can see, it matched an existing constituent, but failed the rest of the order. At this point, you can save the results to a list (Reports > Save As List) and then do whatever you'd like with them. You don't need to run the utility in Import mode.
I hope that makes sense!
I will also mention one more approach, which might be appropriate for you since I know you have SQL skills to employ here.
The Constituent Import Utility includes an empty procedure that is invoked during the import process and can hold any custom code you want to include. It's called LP_CONSTITUENT_IMPORT. You could customize this to check an imported file for any emails that already exist in the system, and populate the constituent ID if they do exist. This could be restricted to the pre-processing mode, so Tessitura doesn't generate errors about missing or invalid fields before you can do your own checking.
Thanks for this suggestion! It is my understanding that the order import utility picks up files from the Impftp drive on our production server. Since we don't map that drive to our staff members machines, it would be a challenge to show them how to drop the file there. When my box office coworker was using the order import utility at the beginning of this year it was a challenging experience explaining the difference between that drive and the folder that exist in the Tessitura drive that is also named Impftp (I am not actually sure why that folder exists, but it has some stuff in in from 2014 which was long before my tenure). It was also challenging showing them how to navigate to the right place using the UNC path.
I am wondering if anyone has a solution that is user friendly in that they just save this file of email addresses on their desktop or somewhere shared and run the utility that picks up the file from that place and outputs the ID's.
Ashley
Sure thing. The standard import utilities do require the file to be in a directory accessible to your SQL server. A custom solution using a report utility inside Tessitura would require this as well. The ImpFtp folder is a convenient and recommended place to do this because it usually already has the necessary permissions, but it's not strictly required. If you can set up a new shared directory that's more convenient for users and is still accessible to the SQL server, you can use that instead of ImpFtp. I know this doesn't address your desire to use the client desktop or similar but hopefully it does lower the burden a bit. Happy to see if anyone else has invented another solution.
Hi Ashley,
If you're just interested in returning IDs of current consitutents only (and not doing anything with the list of IDs or leftover emails in this process), I think there could be a simpler version of what you do now where instead of requring an impresario view, you are simply completing an excel vlookup against an output of existing emails on another tab.
A two tab excel sheet could contain an entry page for your staff to paste in emails from whatever the source is. It could have a pre-filled vlookup formula that would return the IDs instantly without the file being connected to any data source. This would require an output of all your database email addresses (a simply e-address output set executed) living on the second tab.
It might mean you would need to update that second tab output at a regular intervals if your email address numbers change frequently, and you might want to play with excel sheet locks/premissions/hidden fields if you really need to prevent your staff from accessing all your database emails but I think it would be quick and easy.
Of course, it's not within the client but still a time and effort save in the end?
Cheers, Tom.
Hi Ashley
The users saves files in the extraction Folder (or subfolder) in our environment.
We use it for Import constituencies, NCOA and etc.
My understanding, this folder stays for for different Tessitura import/export process.
Thanks, Svetlana
Ashley,
If the goal is marking events as attended, I have a thought, as we have been using Zoom reports in the same way. That said, our solution kind of comes at the problem from the completely other direction.
We have a custom report (that I wrote) which is a minor modification of the Performance Seating Book. The two important things it also outputs are 1) the patron's e-mail address and 2) the ticket's barcode. So our Box Office just uses that report to find e-mail address information and then scans the tickets in directly from that report.
Happy to share if you think that might be useful to you. That said, it DID require me to get RAMP to add a barcode thing to Visual Studio; not sure how to go about that for you since you are self-hosted.
John
Okay... I am thinking about your suggestion here again (I always circle back to stuff on the forums- even if it is a month later). I JUST got the Constituent Import Utility to work the way it is supposed to work. Now I am going to start trying to tweak it to do this other thing.
I will be back with more questions soon.