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
-
Export the existing database via View Data.
-
Check the box next to the database name and click Export.
-
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).
-
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.
-
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.