UNmerging records

In terms of unmerging there's not an elegant way to do that.  I think a help ticket might be your best bet.

For the future, there are several options you can use to protect certain records against accidental merge.  There is a Void Merge attribute which, if put on accounts, will keep them from being merged.  We also developed a corresponding CSI which we ask people to add to any records they mark with this attribute explaining why the record shouldn't be merged.  We've also put some rules in place so that only certain departments can merge certain accounts.  For example Development wants to manage all Corporate records or Membership wants to manage all merges where both accounts have a membership constituency.  We use CSI's to alert other departments if users identify potential dupes that they don't have the rights to merge.

We put a lot of though into our merge process so if you have more questions don't hesitate to ask!  Best of luck getting your records untangled.

Quick update - I have posted the code we use for unmerges to my files section here.  It's called lp_unmerge_customer-2.sql.  We have been using it to clean up some issues here with no problems so far, but please try it in Test before you use it in Live to make sure it does what you expect.

 -steve carlock

Santa Barbara Center for the Performing Arts/The Granada

http://www.tessituranetwork.com/COMMUNITY/forums/t/981.aspx may be of interest. It won't help undo what's already been done, but it's mighty handy for future unmerging.

I agree with Steve here; in terms of prevention of unfortunate merges, LP_VALIDATE_CUST_MERGE is your best friend.

Among other tests, our version gives each constituent in a proposed merge a priority ranking based on various factors (constituencies, certain attributes, activity history etc) Under some circumstances it will disallow a proposed merge and suggest that the merge should be tried the other way, but if both potential records are above a certain rating (both high-touch, basically), it will disallow the merge and tell the merger to refer it to admin. Our Tess administrators have a special user group which can bypass that check, and they will only schedule the merge after checking with all parties who have history with the constituents that it's ok.

We use the void-merge attribute too, but only in a very limited way.

Hi - this is from Footprints:

There is not a mechanism in Tessitura to reverse the effects of a merge. Below are
some suggestions that may be helpful in guiding you to manually separating these
customers.

There are two procedures called when merging customer records and both procedures
are well commented. You can open these procedures and read step-by-step which
tables are merged. I have listed these tables, below, but please be aware that you can
always refer to the merge procedures to see exactly what is done during the merge
process.

Also, I suggest that you look over the merge procedures to insure that I haven't left
anything out. The two procedures called are AP_MERGE_CUSTOMER and
AP_MERGE_CUSTOMER2.


These are the tables that get merged:

tx_alias_cust
tx_const_cust
tx_cust_school
t_cust_doc
t_special_activity
tx_cust_asset
tx_cust_phil
t_cust_research
tx_cust_notes
tx_xref
t_solicitation
t_cust_activity
t_issue_action
t_cust_prem
t_subscription_hist
tx_sol_task
t_creditee
t_tck_hist
ta_membership_date
ta_audit_trail
tx_event_guest
tx_event_extract
t_promotion
t_cust_rank
t_order
t_cust_subscription_summary
t_notice
t_contribution
t_payment
t_account
tx_cust_membership
t_opera_label
t_pledge_bill2
t_address
t_eaddress
t_cust_login
t_phone
tx_cust_sal
t_ack_ext
t_list_contents
tx_cust_program
t_special_req
tx_cust_tkw
t_customer

After those tables are merged, LP_CONST_MERGE is called. You should check the data
in any tables updated in your local procedure, if any.

Lastly, t_potential_dups is updated, setting the status = M for all completed delete IDs.
You should check this table as well.


To un-merge the customer record:

1) Locate the T_ADDRESS row that was changed from the deleted customer_no to the
kept customer_no and change it back to the original customer_no.

2) Locate the TX_CUST_SAL row that was changed from the deleted customer_no to
the kept customer_no and change it back to the original customer_no.

**If the salutation attached to the deleted record was identical to the salutation on
the kept record, the salutation on the deleted record would have been deleted. In that
case, you need to rebuild the salutation row for the deleted record. Keep in mind that
you will not be able to open a customer record through Tessitura if the record does not
have a record in T_CUSTOMER, T_ADDRESS and TX_CUST_SAL, so you will have to
recreate the salutation record before you viewing the customer record.

3) Once the T_ADDRESS and TX_CUST_SAL rows have been reinstated for the
customer_no of the customer deleted in error, the T_CUSTOMER record for the delete
ID can be changed to re-activate it. To do so, you would change the INACTIVE column
from its current value of '5' (signifying 'merged') to a '1' (signifying active) and the
INACTIVE_REASON column should be changed to a NULL value.

Here are some things that you would need to do to unmerge ticketing orders:

Any orders, especially subscription orders, which can be determined to belong to the
customer deleted in error can be changed to the correct customer_no by calling up the
order(s) within Tessitura and changing the constituent ID for the order(s). The system
will warn you that you are changing the owner of the orders, which you can accept.

When the order(s) you can identify have been reinstated, if the season to which they
belong is still being processed by the LP_UPDATE_SUBS_HIST and
LP_UPDATE_TKT_HIST procedures, they should be tied to the correct customer with
the next run of the procedures. If these procedures no longer run for the seasons
involved, the ticket/subscription history records will need to be manually changed to
the correct customer_no.

In the record of the kept customer, locate the 'Merged ID' attribute (which references
the delete ID in the Attribute Value) column and delete it.

Other data that may belong to the customer merged in error must be separated
manually. Your best resource is your Test Server. If the test server is kept up-to-date,
then the deleted customer’s information can be reviewed there to determine any manual
updates required on the Live Server.

A Word document which lists all of the tables where customer data is merged which
may need to be reviewed to determine what data needs to be manually updated is
posted on AIM in the downloads area under WebEx Training Session
Materials/Frequently Asked Support Questions/Tables Merged During the Constituent
Merge Process.

Resurrecting this old thread as I look for solutions to some of our more tangled merge issues...

Ken, would you be willing to share your version of LP_VALIDATE_CUST_MERGE with me? That ranking routine sounds like a useful addition to our safeguards.

Stacey Voigt
Consortium Services Manager
Santa Barbara Center for the Performing Arts
svoigt@granadasb.org

Hi Stace

The objects that support our merging solutions are stored in my files here.

When I looked at them, I realised that they were pretty old versions (pre-v11), so I've updated the major ones, including LP_VALIDATE_CUST_MERGE, but I don't guarantee that they're all current versions - although I don't think the others have changed - anyway, should give you the general idea.

The other notable protective device that we've added since this thread is an Association (UCSS Do Not Merge) which blocks a pair of constituents from being merged into each other, without blocking either of them from being merged with other constituent records. 

Ken