• Replies 2 replies
  • Subscribers 338 subscribers
  • Views 44 views
  • Users 0 members are here
Related

Uploading E-mails as a customization of Constituent Import

Simon Basyuk
$organization

 

I want to share a technique we use to import constituencies which have only name and e-mail address - as a customization of Tessitura Import process. The idea behind it: to avoid importing e-mails and then finding and merging duplications, the procedure identifies dups during the process, prevents them from importing and records them for further reference. (In this case we define duplicate as the one with email address matched to an existing Tessitura primary e-mail). I used this customized import a few times, with several thousand e-mails in each import.


The database procedure is attached, it is a customization of Tessitura's AP_CONSTITUENT_IMPORT (10 lines of code were added).

Here are the steps for the import preparation and run.

1. Fill out your Constituency Import Excel file, the columns:
------
B First_name
D Last_name    
S E_address 

also the mandatory columns with default values, according to your business rules:
------
K Street1   
M City  
N State  
O Postal_code 

2. Run the procedure lp_chc_email_CONSTITUENT_IMPORT the same way you would run AP_CONSTITUENT_IMPORT
(use parameter @cCreatePotentialDuplicate = 'N');

3. create two lists:
--A. of the IMPORTED customers:
select distinct customer_no from TW_CONSTITUENT_IMPORT
where session = <the CONSTITUENT_IMPORT session>
and customer_no is not null

--B. of the EXISTING customers (having primary E-mail address found in the Excel file):
select distinct customer_no
from lt_chc_CONST_IMPORT_email_rejected 
where sessionID=<the CONSTITUENT_IMPORT session>

If you wish to compare names of the existing customers against the ones from the Excel file (they can be different) please use the table lt_chc_CONST_IMPORT_email_rejected.

Thanks,
Simon Basyuk, DBA of Carnegie Hall
sbasyuk@carnegiehall.org

  • $organization

    Duplicate checking in v11 can include Email address as a criteria now.

     

    The Constituent Import Utility has been updated to include this.  The Constituent Import utility has also been updated to support XML format files for import specifications.  So you can truly import only what data is needed for your constituents.  No more need for lots of empty columns in your import data set.  There’s more information in the help doc for this utility.

     

    http://www.tessituranetwork.com/Help_System/Content/Constituent%20Import/Constituent%20Import.htm

     

     

    +Ryan Creps

    +Tessitura Network

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Simon Basyuk
    Sent: Friday, March 02, 2012 10:58 AM
    To: Ryan Creps
    Subject: [Tessitura Technical Forum] Uploading E-mails as a customization of Constituent Import

     

     

    I want to share a technique we use to import constituencies which have only name and e-mail address - as a customization of Tessitura Import process. The idea behind it: to avoid importing e-mails and then finding and merging duplications, the procedure identifies dups during the process, prevents them from importing and records them for further reference. (In this case we define duplicate as the one with email address matched to an existing Tessitura primary e-mail). I used this customized import a few times, with several thousand e-mails in each import.


    The database procedure is attached, it is a customization of Tessitura's AP_CONSTITUENT_IMPORT (10 lines of code were added).

    Here are the steps for the import preparation and run.

    1. Fill out your Constituency Import Excel file, the columns:
    ------
    B First_name
    D Last_name    
    S E_address 

    also the mandatory columns with default values, according to your business rules:
    ------
    K Street1   
    M City  
    N State  
    O Postal_code 

    2. Run the procedure lp_chc_email_CONSTITUENT_IMPORT the same way you would run AP_CONSTITUENT_IMPORT
    (use parameter @cCreatePotentialDuplicate = 'N');

    3. create two lists:
    --A. of the IMPORTED customers:
    select distinct customer_no from TW_CONSTITUENT_IMPORT
    where session = <the CONSTITUENT_IMPORT session>
    and customer_no is not null

    --B. of the EXISTING customers (having primary E-mail address found in the Excel file):
    select distinct customer_no
    from lt_chc_CONST_IMPORT_email_rejected 
    where sessionID=<the CONSTITUENT_IMPORT session>

    If you wish to compare names of the existing customers against the ones from the Excel file (they can be different) please use the table lt_chc_CONST_IMPORT_email_rejected.

    Thanks,
    Simon Basyuk, DBA of Carnegie Hall
    sbasyuk@carnegiehall.org




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization in reply to Ryan Creps (Past Staff Member)

    Thanks Ryan,

    I am not able to test this new functionality of v11. However I analyzed code of the relevant procedures:
      AP_CONSTITUENT_IMPORT ,
      FT_DUPLICATES_FOR_NEW_CONSTITUENT

    I found the Identify Duplicate method in AP_CONSTITUENT_IMPORT was defined as:

       Select TOP 1 @IdentifyMethod = description,
         @DuplicateConstituentId = pd.customer_no,
         @DuplicateName = Case When pd.name_ind = 2 Then @sLname2 + Coalesce(', ' + @sFname2, '') Else @sLname + Coalesce(', ' + @sFname, '') End
       From @PotentialDuplicates pd
        JOIN dbo.TR_DUPLICATE_IDENTIFY_METHOD m on pd.identify_method = m.id

    Then, consider that TR_DUPLICATE_IDENTIFY_METHOD was populated (by v11.0_new.sql) as:

     Insert Into dbo.TR_DUPLICATE_IDENTIFY_METHOD(id, description, inactive)
     Values (1, 'Name and Primary Address', 'N')
     Insert Into dbo.TR_DUPLICATE_IDENTIFY_METHOD(id, description, inactive)
     Values (2, 'Primary Email Address', 'N')
     Insert Into dbo.TR_DUPLICATE_IDENTIFY_METHOD(id, description, inactive)
     Values (3, 'Name and Primary Email Address', 'N')
       
    and the table var @PotentialDuplicates was populated as:

      Insert @PotentialDuplicates
      Select customer_no, identify_method, 1
      From dbo.FT_DUPLICATES_FOR_NEW_CONSTITUENT(@sLname, @sFname, @sStreet1, @sPostalCode, @sEaddress, @ConstituentGroup, 'N')
      Insert @PotentialDuplicates
      Select customer_no, identify_method, 2
      From dbo.FT_DUPLICATES_FOR_NEW_CONSTITUENT(@sLname2, @sFname2, @sStreet1, @sPostalCode, @sEaddress, @ConstituentGroup, 'N')

    As I can see from the code of FT_DUPLICATES_FOR_NEW_CONSTITUENT, it allows only two methods for the duplicate identification:

    name/primary address --identify_method=1
    name/primary eaddress --identify_method=3

    Based on that I would assume the Identify Duplicate method "Primary Email Address only" (the one I was describing) is not available in v11 as of now.

    Simon