Updating a flexible database with recipient_id

When there is no unique identifier defined in the database (flexible database), it is important to update existing records without introducing any duplicates. The solution is to update with the system field Recipient_ID as the unique identifier.

About this task

Use Case

Date field is populated with invalid date format in the database (e.g. yyyy-mm-dd). This prevents date comparison queries to produce correct results. These invalid date formats were likely introduced via API. How to update those date values with the least amount of effort?

Attention: This section highlights areas that might be unfamiliar to users. It is not designed to be step by step instruction on how to import/update a list in general.

Procedure

  1. Export the existing database via View Data.
  2. Check the box next to the database name and click Export.
  3. Under Field Selector (Optional), go to Include These Fields From the Database for each Contact, make sure you click These Fields From the Database and include Contact Identifier (Recipient ID).
  4. Click Apply.

    The exported file is stored in the Export Files folder. The date exported is in the format of yyyy-mm-dd. Date should be stored as mm/dd/yyyy in order for query's date operators to find correct contacts.

    Tip: You can pull the file directly from Stored Fileslocation when you perform the import later without having to download the file to your hard drive and select from your hard drive.
  5. Perform Import/Update Field values to the existing database. Choose the second radio button.

    Update Field Values is the only option among the 4 radio buttons you should choose in order for the RECIPIENT_ID field in the source file to be mapped automatically to Contact Identifier (Recipient ID) in the Map Field step. Many people can be stumped here if they choose the 3rd radio button as they will not be able to see the following in Field Mapping step of the Import.

    Choose the 2nd radio button because when choosing Add New Contact, the Recipient_ID does not exist yet as Recipient_ID system field value is assigned after it is brought into database.

Results

For the use case above, where we are trying to update the Date field, you need to indicate that the format of date columns as they exist in the source file is yyyy-mm-dd in order for them to be updated to mm-dd-yyyy in the database.