How can I make email and attachment families stay together when I sort by email sent date?

Identify and export records with date sent and attachments, create a date sent database, and overlay the original database.

****Precaution: Make a backup of your database files before attempting any of the steps below or you could lose valuable data*****

Prerequisites:

• BEGATTACH is the same value for an email and all attachment records in the email/attachment family. This field is the matching field below.
• The matching field must be set to a Key field in the database structure.
• Datesent is a date type field in the database structure.

The steps below allow you to overlay the sent date value from an email into its attachment records so that the email and attachments stay together when sorted by sent date.

Identify and export email records with Date Sent and attachments

1. Clear the search history in the current database: 'Search' > 'Clear Search History'.
2. Run a search for records with sent date populated. These are your parent email records:
   DATESENT ne 00/00/0000.
3. Run another search to find the parent email records that have attachments to be updated:
   BEGATTACH ne "".
4. Combine searches to find all records that have DATESENT and BEGATTACH: 1 AND 2.
   
5. From the combined query, select 'Documents' > 'Export' > 'Delimited Text'.
6. Select the Export Wizard and click 'OK'.
   
7. Select 'Next' to accept the range.
8. Click 'Next' to accept the Concordance default delimiters.
   
9. Select the desired date format for the output.
   
10. Only select your BEGATTACH and DATESENT fields to export.
    
11. Check the box for 'Export Field Names' and click 'Next'.
12. Browse to your desktop and create a DAT file called overlay_datesent and click 'Finish'.
    

Create a Date Sent database

1. Create a structure from your original database.
2. Select 'Documents' > 'Export' > 'Structure'.
3. Browse to your desktop and create a new folder called "Structure". Click in the folder and
   create Structure.dcb.
4. Open the Structure.dcb in Concordance using File > ‘Open’.
5. With the Structure database open, select 'Documents' > 'Import' > 'Delimited text'.
6. Select ‘Update current database’.
    
7. Check 'Load option' and click 'Next'.
    
8. Use the Concordance default settings for delimiters and select the same date format selected earlier in your DAT file export.
9. Select the fields in the DAT file in order: BEGATTACH and DATESENT.
10. Check the box to Skip first line so that the header row is not imported as a record and select ‘Next’.
11. Browse to the DAT file created on your desktop and click ‘Next’ and ‘Finish’.
12. Confirm the number of imported records and the data format appear correctly.
    
    
    

Overlay the original database

You are ready to perform the overlay of the DATESENT into the Attachment records matching on the BEGATTACH field value.

1. Open your original database.
2. Select 'Documents' > 'Import' > 'Concordance database'.
3. Browse to your structure database and click ‘Open’.
4. Select only the DATESENT field.
5. In the append/replace window, select 'Replace matching records and append new records'.
6. Select 'BEGATTACH' in the Locate matching records by comparing list and make sure this is the only field selected.
7. Uncheck the box for Copy attachments with notes and click ‘Next’.
8. Select ‘Import’.
9. the message: "import complete... Do you want to switch to query zero to see all your documents?" will appear. Select ‘Yes’.
10. QC the results and re-index your original database.

Published by: Virginia Bachman