How to create a query of possible duplicates when validating constituent import file

When validating a constituent import file and preview the exception report there can be instances of the exception 
"Validation error: Duplicate record found" followed by one or more constituent IDs, which represent the constituent record that is already present in Raiser's Edge and a possible duplicate of the constituent in the import file.
Depending on the number of exceptions with this message, you may wish to consider creating a query of these constituent IDs to check the records.

NOTE:
It is advisable that any other exceptions are resolved before proceeding, to limit the number of entries in the exception report.
  1. Export the exception report in Tab Separated Text format, then Click OK to proceed and note the folder where you save the file and the name of the file.
Export in Tab Separated Text format
  1. Open Excel, or similar application, and choose File > Open.  Navigate to the folder where the above file has been saved and change Files of Type option to show All Files.  Select / open the Tab Separated Text (TTX) file.
  2. On page 1 of the Text Wizard that is invoked, choose Delimited and click Next.
    On page 2 click Finish and the file will open.

  3. Highlight and copy all entries that start with "Validation error: Duplicate records found" and paste into column A of a new spread sheet.

  4. On this new spread sheet select column A and on the Data tab select the Text to Column option.
    On page 1 of the Text Wizard that is invoked, choose Delimited and click Next.
    On page 2 select the delimiters of Tab, Comma and Other - in the Other field enter a colon (:) and click Next.
    On page 3 use the horizontal scroll bar at the bottom until you see the last field in the row,  Hold down the Shift key and click into this last field, so all fields show as black / selected.  
    Change the Column Data Format to Text and click Finish.
    Here is a short video example of these steps - https://www.screencast.com/t/KtG9xh5c9R3

  5. Highlight the columns that just show text, in this example columns A and B.  Click right mouse button and select Delete so that only the constituent IDs remain.

Constituent IDs in separate columns

  1. Copy the values from column B and paste below the final populated entry in column A.
    Repeat for column C and any subsequent columns you may have on your spread sheet where the number of columns depend on the number of possible duplicates reported.
    When complete, the spread sheet should look similar to this, which is based on the above example

Constituent IDs copied to one column

  1. Once you are sure column A contains all the constituent IDs from all columns, highlight / select column A and on the Data tab click Sort and sort in the order of A to Z. This will result in the rows that are populated being together because, as with the above example, there may be some cells that are blank because some exceptions report more possible duplicates than others, Example in column B in the above screen show where B2 is blank, so row 5 of column A is also blank.

  2. Scroll down the spread sheet to identify the last row that is populated and you can then choose one of the following options, depending on this number.

    1. If there are less than 500 IDs listed then see How to copy and paste multiple values into Query using One Of.

    2. If there are 500 or more IDs then see How to create a query from an Excel list of constituent IDs.
      Note that the steps in this article are also relevant for less than 500 IDs, if preferred.

    3. The final option you may wish to consider, which can be for any number of constituent IDs, is to use the SmartPaste utility created by SmartT\HING, a Blackbaud' Partner .  
      This is a free utility which is installed locally on your workstation, whether your Raiser's Edge database is local or hosted by Blackbaud, therefore you may need to consult your IT Support before choosing this option - see https://www.smartthing.org/smartpaste/ for more information.
       
      Please note: We provide links to third-party websites in an effort to help you resolve your issue. We are not responsible for the information on third-party websites, and we cannot assist in implementing the solutions on these websites
       

Steps to Duplicate

  1. Validate the import file
  2. When the "Import Complete!" screen shows, it can return a number of Exceptions  - if exceptions present click Preview Report
  3. Review the exception report for instances of "Validation error: Duplicate record found" located in the Reason column of the exceptions.

Was this article helpful?


Thanks for your feedback! Did this solve your issue?

Comments (optional):


Thanks for your feedback!
We're glad it was helpful but sorry it didn’t solve your issue. If you need assistance, click Chat with Support below.
We’re sorry to hear that. Please tell us why.

 I don't like how this works.

 The answer is confusing.

 The answer didn't match what I was searching for.

Additional Comments (optional):


Thanks for your feedback! If you need assistance, click Chat with Support below.
Thanks for your feedback. Help us make our products even better by sharing details in our Idea Banks or our online Community.
Thanks for letting us know. We'll work on clarifying the information in the article. If you need assistance, click Chat with Support below.
Thanks for letting us know. We'll work on updating the search engine to return more relevant results.