The Merge Data command merges field information of two ProVal databases, updating the records of a target database file with the data found in corresponding records of a source database file.

Note: if you simply want to add new records after the existing records of the current database, you should use the Append Data command instead of the Merge Data command.

The first dialog box displayed after you select the Merge Data command is the library of all entries previously defined and unhidden in the current Project, if any such entries exist. To edit or use an entry, click its name. To set up a new set of Merge Data parameters, click New.

Name is a descriptive phrase used to identify the Merge Data library entry.

First, select a target database file. This represents the database that will receive update data. Note that this can also be set as the "<Current database>," for cases in which you want ProVal's current database to receive the update data, where the user setting for the current database can change (e.g.) between annual valuation cycles or during the valuation process. Also select a source database, which is the update file selected from among the client database files containing data to be merged into the target database.

To select only some of the records in the source database, you may optionally enter a Selection expression to apply to the source database. When the cursor is in this expression box, press the F1 key to summon a useful list of database fields, codes and their associated labels, and operators; or you may use the arrow to the right of the field to access the Selection Expression library.

Next select one or more key fields from the list of all fields in the current database. If you choose a character field, the match case in keys check box becomes accessible. The default is to ignore alphabetic case differences when matching key values (so, for example, “Joe Smith” matches “JOE SMITH”). Check this box to treat case differences as significant (so “Joe Smith” and “JOE SMITH” will be treated as different key values). 

Alt Key is an optional parameter that can be used to specify a secondary key field (or fields). When a matching record cannot be found based on the primary key field, a second check is performed using the alternate key. This can be helpful in situations where Social Security numbers or employee ID numbers are corrected from one year to the next. As an example, consider a current year database with primary key field EEID and alternate key field OLDEEID. When matching EEID in the current year database against the prior year database, a second step will be taken for any for any unmatched records. Those unmatched records in the current year will also have the alternate key field OLDEEID checked for possible matches against EEID in the prior year. 

Select one or more source fields to read (to import into the target database) from the list of all fields in the source database. An asterisk (*) marks fields that are defined in the current database as well as in the source database. (Fields without the asterisk are defined only in the source database and will be initialized in the target database with missing values before the merge process begins.) Click the All or None buttons to select all or none of the fields in the source database, respectively. By default, fields in the source database are used to update identically named fields in the current database. (Or, if you are importing new fields, the fields created in the database will have the same name as the fields in the source database.) To choose different names for the imported fields, click the Target Flds… button.

The Target Fields dialog box lists the fields you’ve chosen to import (in the spreadsheet column Field in Source Database). In addition, the fields in the target database into which these fields will be mapped are listed. By default, these fields are listed in the second column, representing field names that are Existing Fields (i.e., already exist in ProVal's Data Dictionary). Alternately, you can manually choose to map into a New Field in the target database (i.e., a field that is new to ProVal's Data Dictionary).

The last onscreen options dictate how the data update should be performed. The first option specifies what should be done for records that were found in both files (i.e., records that were "matched" based on value(s) of the key field(s)). Select, for records found in both files, if you want to overwrite:

• All values. Data from the source database will REPLACE existing data in the target database. Check Allow values to be overwritten with missing values in order to update the target database with missing values from the source database. , If unchecked, missing values in the source database will not replace non-missing data in the database. That is, existing (i.e., non-missing) source data will always overwrite target database data. 

• Only missing values. Existing non-missing values in the database will not be altered, but any "holes" in the database (i.e., missing values) will be filled in with data from the source database.

• Nothing. Neither missing nor non-missing data in the target database will be updated (i.e., records that already exist in the target database will be unchanged). This option is uncommon but could be useful when you are using the merge process only to add new fields or records to the database.

The second option specifies what should be done with the records themselves (i.e., what records should be preserved in the merged target database). Select, in the merged target database, whether you want to keep records if present in:

• The target database. Records found ONLY in the source database will be ignored, and records present in the target database but NOT in the source database will remain in the target database.

• The source database.  Records found ONLY in the source database will be appended to the target database, and records present in the target database but NOT in the source database will be deleted.

• Both databases. Records found ONLY in the source database will be ignored, and records present in the target database but NOT in the source database will be deleted.

• Either database. Records found ONLY in the source database will be appended to the target database, and records present in the target database but NOT in the source database will remain in the target database.

Lastly, the option to Create MergeStatus field allows you to specify whether you want a "merge status" field in the database. This coded field, which will be named MergeStatus, classifies each record as one of:

• Matched: found in both the target and source databases
• Not Matched: found only in the target database, or
• Appended: found only in the source database and added to the target database

Click the Run button to perform the merge. When the merge is complete, the system displays a summary of what was done and asks for confirmation before saving the changes in the file. The summary includes the following information:

• Number of records in the database.
• Number of records in the source database and, if you provided a selection expression, the number of selected records.
• Number of records found in both files (i.e., matched records).
• Number of records updated for each field. This is the number of records for which the source database contained data different from that in the target database.
• Number of records found only in the source database, and the number that were appended to the target database. Only records satisfying the selection expression are counted.
• Number of records found only in the target database, and the number that were deleted.

Click “Yes” to save the changes, or click “No” to discard the changes and return to the Merge Data parameters. If you choose “No”, the merge will be canceled, but any new fields that were created (and filled with missing values) during the merge will still exist. You can erase these fields, if you wish, by using the Database > Edit Data > Delete Fields command. (Note: The Merge Data command produces large amounts of unused space in the database file, even if you choose not to save the changes made in the update. You can eliminate the unused space by running the File > Pack Files command.)