Tracking merged ids back to an active account

I don't think I ever mentioned the use case: we share our contribution information with a campus development system of record, and the biggest challenge is constituent matching.  We have some processes for trying to update our customer accounts with new campus system ids when those change (i.e. are merged), while I'm working on building a custom API endpoint to help them update their system with correct Tessitura ids.  I'd want them to be able to enter a Tessitura Id and either confirm it or return the new id that it was merged into.

It seems like you are averse to using a loop because of the length of time it might take to resolve that in active use, is that correct?  This might not be the most elegant solution ever, but would this work?

• Create a local data table with two columns, old/original_customer_no and merged_customer_no.
• Populate it initially with all existing customer_nos, with those customer_nos in both columns.
• Set up a nightly procedure to insert the day's new customer_nos into the table.
• Run a one time, loop-laden/data intensive update to that table updating the merged_customer_no column to final end result customer_no after all successive merges.
• Then create a nightly procedure which uses the data from your merge process and updates the requisite customer_nos for that night's merges.
  • This would need to be a far less intensive process since what will happen is you have, say customers 1, 2, 3, 4 and 5 all of whose merged_customer_no is 5.  So your update process just searches for all merged_customer_nos = 5 and change them to 6 when 5 gets merged to 6.
• Then it is used kind of like how you would use V_CUSTOMER_WITH_HOUSEHOLD.  You put in the customer_no, it finds the old/original_customer_no and then returns the merged_customer_no.

Again, not exactly elegant, but as long as you do the full data dump up front, and set a couple of procedures to take care of the nightly work, it should get you what you want, no?  I mean, if we assume that you can keep your local table up to date, calling that table should be a very quick return.

Yeah, making my own table was pretty much what I wanted to avoid, but I'll have to consider it.  Keeping it up would be a simple matter of a line in LP_CONST_MERGE.

Yeah, making my own table was pretty much what I wanted to avoid, but I'll have to consider it.  Keeping it up would be a simple matter of a line in LP_CONST_MERGE.

I almost suggested putting that update in LP_CONST_MERGE, but I wanted to leave the window open in case you were worried that the merge would fail for some other reason.  In which case, the already updated information would need to be rolled back.  Though there are of course ways to solve that issue, too.

No need to worry about that:

This procedure
is actually called twice during the procedure, once before any other data is merged and once after
the final step in the merge which is when the T_CUSTOMER row for the deleted id is marked Merged.
Both calls are inside the merge transaction and will be rolled back if any error occurs. Likewise,
if this procedure raises an error, then the entire merge process will be rolled back and the next
constituent merge will begin. Failed merges are noted in the merge error log.

Fair enough then.  We happen to actually not have anything in either LP_VALIDATE_CUST_MERGE or LP_CONST_MERGE, so I cannot say have paid extremely close attention to when they are called beyond knowing that they are called to validate the merge and during the merging process.  But good to know.

It works pretty nicely.  I've pulled out our custom code there into one function and two stored procedures.  It's hard to remember, but certainly any time you add a custom table that references customers you'll want to add an action in there.

IF @merge_stage = 'B'
  BEGIN

	select @errmsg = dbo.LF_CPSMA_CONST_MERGE_PRECHECK(@kept_id, @deleted_id)
	if(@errmsg <> '')
	begin
		select @err_table = @errmsg
		goto GiveErrMsg
	end

	exec @return_status = dbo.LP_CPSMA_CONST_MERGE_PREOP @kept_id, @deleted_id, @errmsg OUTPUT
	
	if(@return_status <> 0)
	begin
		select @err_table = @errmsg
		goto GiveErrMsg
	end

END --@merge_stage = 'B'

If @merge_stage = 'A'
BEGIN
	
	exec @return_status = dbo.LP_CPSMA_CONST_MERGE_POSTOP @kept_id, @deleted_id, @errmsg OUTPUT
	
	if(@return_status <> 0)
	begin
		select @err_table = @errmsg
		goto GiveErrMsg
	end


END --@merge_stage = 'A'