Merge Guidelines

Dale Aucoin from the New York City Opera has generously shared with me a document spelling out merge guidelines.  The document is pasted below:

Jacob Houser

Tessitura

Merging Accounts:

When you schedule two accounts to be merged in Tessitura you must choose one account to “keep” and one to “delete”.  In reality Tessitura does a true merge, keeping almost all of the information from both accounts.  For the purposes of this document the account you choose as keep will be called “keep” and the one you choose to delete will be called “delete”.

There is some information that Tessitura cannot keep two copies of, this information will be kept only from the keep account, the corresponding information in the delete account will be lost.

The General tab is the main tab you need to pay attention to when selecting which account to keep and which to delete.  The information in almost all other tabs will be merged into the account you select as the keep account.

When you merge:

1. Information in the N1: and N2: lines – Prefix, First Name, Middle Name, Last Name, Suffix, Gender, Status – is kept only from the keep account.  Nothing in any of these fields will be transferred to the keep account from the delete account.

2. Information in the address area (1^st Salutation, 2^nd Salutation, Business Title, Opt. Address, Street Address, Locale, Country, Dear) is kept only from the keep account.  Information from the delete account will not be transferred over here however a copy of the address (Opt. Address, Street Address, Locale, Country fields only) from the delete account will be transferred to the Addresses tab in the keep account with the status “inactive”.

3. Phone 1, Phone 2 and Fax are kept only from the keep account, however a copy of these phone numbers will be transferred to the Addresses tab of the keep account as part of the inactive address record (see above).

4. Phone numbers that are added in the area below the Phone 1/Phone 2/Fax area WILL be transferred from the delete account to the keep account.

5. If the email address field at the bottom of the General tab in the keep account is blank, the email address from the delete account will be transferred to the keep account.  If there is an address in this field in the keep account, a copy of the email address from the delete account will be transferred to the Addresses tab and tagged as “inactive”.

6. Mail Restriction, Phone Restriction and E-Market Restriction settings from the delete account will be lost when a merge occurs.

7. The Original Source from the keep account is saved; the one from the delete account is lost.

8. Under the Names tab- Salutation button there can be only one salutation for each “Salutation Type” so for example, if there is a “Business Salutation” on both keep and delete accounts, only the one from the keep account will be kept.  This also applies to Program Lists/Preferred Name area, there can only be one program type for each constituent.

9. When merging two accounts where both have active membership records with the same “membership organization” in History – Memberships, it is generally advisable to delete the membership from the delete account and adjust the membership in the keep account to the correct level (and recog amt.).  This generally makes for cleaner records.

When merging accounts it is very important to select the correct account to “keep” and the right one to “delete”.  You do not want to lose information.  Generally you will need to examine the General tab of both accounts and, as necessary, move information from the delete account to the keep account so it is not lost. 

Especially important is the Mail Restriction, Phone Restriction and E-Market Restriction area.  If there is anything other than “ok to mail” “ok to call” “ok to emarket” in these areas in the delete account it must be transferred to the keep account manually before you schedule the merge.

Procedure:

This does not actually merge the accounts it schedules them to be merged when the procedure runs next.  This procedure runs several times a week in the morning.

Before You Merge Checklist:

Ø      Decide which account to keep

The Keep account should have the most complete and accurate N1/N2 information and the most complete and accurate address/phone1/phone2/fax

Ø      Check the GENERAL tab

Only merge accounts with a “Const Type” of Individual.  Leave all others for the Donor Information Department in Development

Transfer any missing name, address or phone information from the delete account to the keep account

Transfer all Mail Restrictions, Phone Restrictions and E-market Restrictions from the delete account to the keep account

Ø      Check the HISTORY tab– MEMBERSHIPS

If both accounts have a membership, do not merge – leave for the Donor Information Department in Development.

Ø      Check the ADDRESSES tab – LOGIN

If both accounts have active logins, delete the login that has an older “Last Web Login:” date

Ø      Check the NAMES tab - PROGRAM LISTS/PREFERRED NAME

If both accounts have entries with the same “Program” type listed, do not merge the accounts – send the account numbers to the Donor Information Department in Development.

When merging two records with different names you must pay close attention to all data in the accounts coded as N1/N2/Both.

The situation where this is most common is when merging a husband and wife into one account where both are N1 on their individual accounts.  The person who is N1 on the Delete account would then be added as N2 on the Keep account and all data from the Delete account would be transferred over to the Keep account.  In these cases you must examine all data on the Delete account coded as N1 or Both and change it to N2.

Tabs to examine on the Delete account include:

Addresses – Electronic

Constituencies

Associations

Research (all of the separate radio buttons)

Attributes

I tried to upload a PDF of our documentation, but the file size is too large to post it here.  If you go to my profile page (not sure how to include a link to it from here), I've uploaded the document to My Files.

It is pretty specific to our organization's guidelines and we're in a consortium, so I'm sure there will be things that are different.  The Word document includes hyperlinks.  If you would like a copy of the Word document, send me an e-mail (aalbert@ocpac.org) and I will forward it to you directly, sans screen shots.

Hope this helps.

Amber

For us: When merging accounts, keep the account with the msot recent ticket history because it will wipe out if merged as the deleted constituent. I don't know if this is just a glitch for us or not - hopefully this is useful to others.

Sarah,

The merge process is probably not deleting the ticket history records they may just not be included in the merge process.  Any custom or local tables (tables in the database starting with LP_) which have a customer_no column should be added to the merge by modifying LP_CONST_MERGE (http://www.tessituranetwork.com/network/Learning/Tessitura%20Knowledgebase/Constituents/Other/16226.aspx).   This procedure is called each time the merge is run both before and after the merge process and can be used to include custom data in the merge as well as change the default merge behavior. 

For assistance with modifying this procedure for you needs I would recommend contacting the consulting team at consulting@tessituranetwork.com.  Also I suspect some folks on the forum would be willing to share their versions. 

Best,

Anna

Anna E. Wessely

Manager Asia Pacific/Senior Application Specialist

Tessitura Network, Inc

+1 888 643 5778 x 308

awessely@tessituranetwork.com

www.tessituranetwork.com

Thank you so much for your advice - we'll fiddle around with the tables & finally be able to fix our merging process!

Sara,

I remember that the ticket history table was different than the standard LT_TKT_HIST. I don't remember why. I can come pay you a visit and add your ticket history table into the stored proc.

Christian @ The Tech Museum

To save you a few minutes coding - this is a bit of script that we've used to identify orphaned tix hist rows (when we had a problem with our merge script) and re-attach them to their new merged identity.

Should work for anyone, assuming their Merged-id attribute keyword_no is still 6.

Ken

-----------------------------------------
-- script to identify unmerged  rows in LT_tkt_HIST
select distinct c.customer_no keep_id, a.customer_no delete_id
into #temp
from lt_tkt_hist a
join t_customer b on a.customer_no = b.customer_no
join (select customer_no, key_value , create_dt
   from tx_cust_keyword
   where keyword_no = 6) c -- kw 6 should be Merged_id
  on c.key_value = a.customer_no
where b.inactive =5

-- select * from #temp

--drop table #temp

/* this bit does the fix, so it's commented out until i want to do it.  */
/* update lt_tkt_hist
set customer_no =  b.keep_id
from lt_tkt_hist a
join #temp b on a.customer_no = b.delete_id
-- select * from lt_tkt_hist
where customer_no in (select delete_id from #temp) */
/* select * from t_customer where customer_no in (select delete_id from #temp)
order by customer_no */

----------------------------------

Woo-hoo! More brainpower to share with the Rep! I'll let Jean know - she'll be ecastatic!