Bookmark and Share

 

Add and Replace records using Import and Validate

If the Import method is Add and Replace, the system uses the key to see if a record already exists. If yes, it will choose to Replace that record, if no, it will Add a new record.

To prepare this demo, remove some records from the Products table, and make some changes to the records, preferable also to the Category field.

New Import - Name and Purpose

  1. start New Import - Name and Purpose 
  2. Enter the name 'Add or update Products'
  3. Select in group box 'Purpose of import' > Add & Replace: Replaces if a matching record is found, else a new record is added'
  4. Press [OK]

In the Import an Validate form the Select Source Table tab gets focus.

Select Source Table

  1. In the combo box 'Select Source Table or Query', select the 'Products - original data' table. The source contains the records to be imported

Note that the table 'Products - original data' is used as a backup for the demo. Whenever you are done, you can use cut and paste on the whole table to get back the original data for Products and Suppliers.

After selecting the source table, the Select Destination Table tab gets focus.

Select Destination Table(s) 

We now want to select the Destination Table, in our case table Products.  The reason there is a button instaed of a selection combo box is that it is possible to distribute the Source records data over multiple destination records.

  1. Press button [Add destination table ...]

The [Select Destination Table] dialog appears

  1. Select the Products table.
  2. Press OK

In the transfer pane, an matching is done of Source and Destination fields. Not so surprising, all fields have been matched.

  1. Switch to the [Start Import] tab

Changed values

If you did your initial preparation ok, you should be able to find a record with changed values using the navigation buttons . An example is the below:

You can see two fields changed: CategoryID and Unitprice. As most people don't know what the category codes stand for, we will use a lookup technique. 

  1. Switch to the [Extend Field Behaviour] tab
  2. Check Specify Lookup query for meaningful strings instead of IDs
  3. In column Lookup, enter the name of table Category. After leaving that cell, the row gets updated.

The Old Value now displays the text correponding to the ID. This went ok because the table's first two fields happend to contain the values we need: ID and Categoryname. If the table had been set up differntly we would have made a query. New Value still has an ID. If we want to know what it stands for, click on the New Value popup button. You will see the current value selected.

Process change

  1. Switch to the [Start Import] tab
  2. Press [ Process Record]. This updates the record with the new values

Unchanged record

If no changes have been detected, the [ Process Record] changes to  [ No Changes - Next]

Process New Record

If you did your initial preparation ok, you should be able to find a new record.

  1. Use the navigation buttons to find a new record. A new record is recognized in that there are no Old Values.
  2.  Press [ Process Record]. This updates the record with the new values