Importing a list based on RE Legacy IDs

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, 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.

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. 

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