Importing a list based on RE Legacy IDs

Jenni - You can do this.  There is the ability to insert SQL code into the list criteria.  I think that would be the easiest way to do it.  Do you have a database person on staff?  If not, I can help you with it.

Carol Keeney

Are you just asking about creating a manual list?

www.tessituranetwork.com/.../Viewing and Editing List Contents.htm

A few questions:

Do you only have the RE id's and need to figure out the Tessitura customer number? If so, then that is step one. Step two is just to create a manual list like Gawain suggested. 

Sounds like you have Raiser's Edge ID as a criteria for lists/extractions. From what I understand, items within the Attributes tab are keywords. Keywords are strings (varchar(30)). If you attempt to build the list using an extraction, you'll notice that it will not allow you to use IN. This is because a keywords are strings. In an extraction, you can show the query. This is where you'll see that the key_value is a string. TX_CUST_KEYWORD.key_value = '102146'. You're probably going to need to change the query and use something like the FT_SPLIT_LIST tabled-value-function. This doesn't seem like a simple change to a query, unless I'm missing something.

Hopefully, someone can come up with an easier way.

Thanks, Carol. We don't have a database person on staff so any help you could provide would be greatly appreciated. My old membership manager figured out how to do this once from a list of RE IDs, but he doesn't work here anymore and couldn't remember what he did!

Thanks, Neil. We just have the RE ID's, and it's enough (probably 200) that I don't really want to look up each Tessitura customer number individually.

Hi Jenni

If you don't have a database person who can import the table directly into SQL, I suggest using excel.

In Tessitura, create a list of everyone with the RE Legacy ID, adding other appropriate criteria so that you're only selecting a subset of your data that you know will include the 200 records of interest.
Make sure your output set includes the Legacy ID attribute.   Export the list to excel.

Do a VLOOKUP in excel between your list of 200 RE IDs and the Tessitura data to find the Tessitura customer number for each row.  This is the missing link you need.

Re-import that Tessitura IDs list into Tessitura - and use that to update the solicitor assignment attribute using the Manage Attributes utility.

best wishes

Alison

Jenni, I have another idea, but it involves using:

1. An extraction and manually editing the query. 

2. Excel to build the text you are going to paste into the query.

3. Saving the results of the extraction to a list.

This all relies on if you have rights to manually edit an extraction and have the RE Id's in an Excel document. If you have manual edit rights, then I can create some instructions for you.

For anyone interested, here are the directions.

Assuming you confirmed you have rights to manually edit an extraction query; you could do the following:

1. Create a comma delimited list of the RE id’s in Excel. This process is relatively easy. You just need a column with your RE id’s. Note that in Tessitura the RE id’s are not numbers (integers). They are text (varchar(30)). This means we will need a single quote surrounding each RE id.

          a. In Excel, create a spreadsheet like what you see below. Note that in order for a single quote to be viewable in a cell, you need to type in 2 single quotes.

           b. Use the CONCAT function to create the comma delimited list (I use Excel PowerQuery - Get & Transform to get the results, but CONCAT is easier for those that don't know PowerQuery).

          c. The CONCAT function will add an extra comma at the end. When pasting into the extraction, we will leave that last comma out (my PowerQuery version doesn't have the issue of the extra comma)..

2. Create a new extraction, add the criteria of Raiser’s Edge ID = %. In our version, Raiser’s Edge ID criteria is in the Constituent folder. The % is a wildcard. Note that it will not allow you to choose the IN option. No big deal. We will manually change it.

3. Your organization may have different rules as to where a membership is on a record. We have it on the Household (not the individual). I selected Replace individual constituents with their primary household, if one exists.

4.Click Manual Edit.

         a. Select the = ‘%’ text.

          b. Change = ‘%’ to IN ( ).

          c. Paste the comma delimited list of RE Id’s inside of the parenthesis.

5. Save and generate the count. Note that in my example, only 6 of the 10 found matches (I randomly generated some of the RE Id numbers to show the concept).

6. Click Save to List, name it. You now have a list of the constituent id’s.

On a side note, in Excel, you could use Get & Transform (PowerQuery editor) to turn rows from a table into various versions of comma delimited lists. You just past the data into the left part and click Data - Refresh All and it creates a comma delimited list for you.

In the above example, I have 2 result-lists. The top version with the double quotes I use for Analytics. It gives me a comma delimited double quoted version that I can use as an advanced filter in Analytics. For example, I had a column of the zip codes in MN. I then used the double quoted list as a filter in Analytics that pulled order data for those only with MN Zip Codes that I wanted to look at. The single quote version I use in the Where clauses in SSMS (SQL). This comes in handy when something is needed now and I don't have time to put together the correct SQL code. I use it as an "Easy button" option. When I get more time, I go back and write the correct SQL code to use in the future.

Excel tip if using Excel for web, two single quotes gets you one single quote, i.e. just hit the single quote key twice. 

Hi Jenni,

This is David Struyf with the Tessitura Network Support team. Let me know if you are still having any issues following the suggestions here in the thread and I can get a support ticket opened for you.

Cheers,

David

Thank you so much, everyone, for your help and suggestions. I used the manual edit on an extraction, as Neil suggested, and it worked!