• Replies 11 replies
  • Subscribers 414 subscribers
  • Views 302 views
  • Users 0 members are here
Related

Duplicates and how to manage

Stephanie Kemp (Past Member)
$organization

Hi, new to Tessitura as of December last year, though not new to databases.

We have a huge amount of duplicates, which seem to mostly be the result of donors logging in to buy tickets and setting up new records for themselves. Today I came across a record of a man who apparently has four daughters named Ruby! Each time new tickets (for classes in this case) have been booked, a new record is set up.

I know how to merge duplicates. My question is more around best practice in catching these and fixing as they come up, and also if anyone can shed any light as to why this happens and if we can do anything from the ticketing side to prevent it??

Any thoughts gratefully received.

Parents
  • $organization
    If your original record has an email address, but not a web login, creating a temporary web login will help reduce the number of duplicates created.
     
    Here’s a handy-dandy script that will help.  Have it modified for your organization and run it in Test first.
     
    USE [impresario]
    GO
    /****** Object:  StoredProcedure [dbo].[lp_create_logins]    Script Date: 03/09/2011 14:06:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Alter procedure [dbo].[lp_create_logins]
    AS
    /*           Creates logins for all email addresses that don't have one. 
     
                    The code below needs to be modified for specific site needs. At a minimum, the login and email types
                    need to be filled in with the approrpiate values.  Other modifications, such as only looking at primary
                    emails, can also be easily made in the Cursor query.  Also, the default password can be determined
                    by changeing the query in the Insert statement.
                   
                    Created by David Woodall
     
    */
     
    begin tran
    --
    -- Declare some needed variables
    Declare @customer_no int,
                                    @email varchar(80),
                                    @eaddress_no int,
                                    @new_login_no int,
                                    @login_type int,
                                    @eaddress_type int
                                   
    --
    -- Set the login type that should be created and the email type from which is should be created.
    select    @login_type = 1,
                                    @eaddress_type = 1
     
    -- This cursor gets all emails without logins, as of now.....
    DECLARE mycur INSENSITIVE CURSOR FOR
    SELECT e.customer_no, e.address, e.eaddress_no
                    from t_eaddress e
                    where eaddress_type = @eaddress_type
                    and inactive <> 'Y'            -- and primary_ind = 'Y'
                    and e.address not in (select login from t_cust_login where login_type = @login_type)
     
    OPEN mycur
     
    FETCH mycur INTO @customer_no, @email, @eaddress_no
                   
    While @@fetch_status >= 0
    BEGIN
     
    --             Only add a login if one does not already exist. We do not want dups, so we double check
    --             we have not added one since the cursor select was executed.
                    if not exists (select * from t_cust_login where login = @email and login_type = @login_type)
                      begin
     
    --                             New Login ID Value
                                    Exec @new_login_no = [dbo].ap_get_nextid_function @type = 'LO'
     
    --                             New Login is inserted
                                    insert into t_cust_login(login_no, customer_no, login_type, login, password, eaddress_no, primary_ind, temporary_ind )
                                                    select @new_login_no, @customer_no, @login_type,
                                                                                    @email as login,
                                                                                    convert(varchar,@customer_no)+'pass123' as password,
                                                                                    @eaddress_no,
                                                                                    'Y' as primary_ind,
                                                                                    'Y' as temporary_ind
                      end
     
                    FETCH NEXT FROM mycur INTO @customer_no, @email, @eaddress_no
    END
     
    CLOSE mycur
    DEALLOCATE mycur
     
    select * from t_cust_login
    order by login
     
    commit tran
     
     
  • $organization in reply to Dot Krebs (Past Member)

    Wow! Thanks Dot, that is very kind of you to pass on.

  • $organization in reply to Stephanie Kemp (Past Member)

    Hi Stephanie,

    If you go this route, which will certainly prevent a bunch of duplicate accounts, be sure that you communicate people's log-ins in future emails that ask that they take action. Typically I see orgs do it under the call to action button. "Your log-in is megan.hall@seattlesymphony.org."

    Without this step people will think that they don't have a login and they won't understand why Tnew is preventing them from creating an account.

    Good luck!
    Megan

Reply
  • $organization in reply to Stephanie Kemp (Past Member)

    Hi Stephanie,

    If you go this route, which will certainly prevent a bunch of duplicate accounts, be sure that you communicate people's log-ins in future emails that ask that they take action. Typically I see orgs do it under the call to action button. "Your log-in is megan.hall@seattlesymphony.org."

    Without this step people will think that they don't have a login and they won't understand why Tnew is preventing them from creating an account.

    Good luck!
    Megan

Children
No Data