# Data Migration

## Overview

Migrating documents and files into the database comprises the following steps.

1. Complete the [metadata field](metadata-types.md) [configuration](configurator-level.md)

2. Generate an import workbook

3. Populate the import workbook with data

4. Validate the import workbook

5. Import the data

For a list of the import-related commands, enter `Import` into the **Actions** field of the [Command Palette](command-palette.md). 

Note that the import function is only designed for importing data.  It does not manage bulk updating of data.  


## Import Workbook Function

The Import Workbook function generates an Excel spreadsheet that can be used to prepare data for bulk importing documents, files and associated metadata into Elyse. The workbook is populated with current lookup data from the database and contains empty data entry templates for the user to fill in.

The function is available to users with Editor or Controller role privileges.

In the [Command Palette](command-palette.md), enter `Import` or `Workbook` into the **Objects** field to locate the **Generate Import Workbook** command. Alternatively, navigate to **Advanced** > **Data Migration** > **Generate Import Workbook** from the navigation bar.

When the command is executed, the application fetches the current metadata from the database, generates the workbook and opens a file browser for the user to save the file.

### Workbook Structure

The workbook contains three categories of sheets, distinguished by colour-coded headers.

#### Data Entry Sheets (Green Headers)

These are the primary data entry sheets where the user enters the records to be imported.

- **Document ID List** — Document ID records to create, based on the `base.document_id_list` table.

- **File Metadata** — File metadata records, based on the `base.file_metadata` table. The `file_path` column should contain the full path to the file on disk so that the import function can locate and load the file. Additional columns include `document_id`, `docidautogen`, `file_group_id`, `doc_group_id`, `retention_months` and `release_id` which correspond to the stored procedure input parameters.

- **Release Identifiers** — Document release identifier records, based on the `base.release_identifiers` table. See [Release Management](release-management.md) for more information on release identifiers.

#### Link Table Sheets (Orange Headers)

These sheets define the relationships between documents, files and their metadata. Each sheet corresponds to a database link or value table.

- **File to Document Links** — Links files to documents (`doc_id` and `file_id`).

- **Doc Date Values**, **Doc Free Text Values**, **Doc Integer Values**, **Doc Real Number Values** — Open-input field values for documents.

- **Doc Multi-Select Links**, **Doc Radio Button Links** — Entity-reference field links for documents.

- **File Date Values**, **File Free Text Values**, **File Integer Values**, **File Real Number Values** — Open-input field values for files.

- **File Multi-Select Links**, **File Radio Button Links** — Entity-reference field links for files.

- **Doc ComObj MS Links**, **Doc ComObj RB Links** — Common object multi-select and radio button links for documents.

- **File ComObj MS Links**, **File ComObj RB Links** — Common object multi-select and radio button links for files.

- **Doc Cross References** — Cross-reference links between documents.

- **Doc Group Links**, **File Group Links** — Document group and file group membership links.

See [Elyse Metadata Basics](metadata-types.md) for an explanation of the different metadata field types.

#### Lookup Sheets (Blue Headers)

These sheets are populated with the current metadata from the database. They are read-only reference sheets that provide the valid IDs for the data entry and link table sheets.

- **Base Lookups** — Release ID Types, Transaction Groups and Units of Measure.

- **Cross Ref & Groups** — Cross Reference Types, Document Group Names and File Group Names (note: the File Group Names block uses the `File Group Name ID` column alias as returned by the stored procedure).

- **Common Objects** — Common Object Attributes, List Names, Objects and List Members.

- **Doc Attribute Names** — All document attribute field names including date, free text, integer, real number, multi-select and radio button fields.

- **File Attribute Names** — All file attribute field names including date, free text, integer, real number, multi-select and radio button fields.

- **Filter Group Links** — Filter group associations for document and file multi-select and radio button attributes. Contains four blocks: Doc MS Attr Filter Group Links, Doc RB Attr Filter Group Links, File MS Attr Filter Group Links and File RB Attr Filter Group Links.

### Dropdown Lists

Where a data entry or link table column references an ID from a lookup table, the workbook automatically provides an Excel dropdown list for that column. Clicking on a cell in the column will display a dropdown arrow. Clicking the arrow will display the list of valid IDs from the corresponding lookup sheet. Selecting a value from the dropdown will populate the cell. If a value is entered that is not in the dropdown list, a warning message will be displayed.

### Transaction Groups

Transaction groups are used to group a set of documents or files that have been created as a batch so they can be later retrieved as a set.  It is recommended that a transaction group be created during metadata configuration and applied to the documents and files to be created.  In the [Command Palette](command-palette.md) enter `Insert` into the **Action** field and `Transaction Groups` into the **Fields** field to locate the **Create Transaction Group** command. 

### Document IDs

If a document ID has already been allocated to a document then enter the document ID into the Document ID column. If a document ID has not been allocated then it can be automatically generated by the database. But the import function needs to link documents to parameters via a link table and hence requries a placeholder identifier. If a document ID has not been allocated then enter a number or a string into the Placeholder Doc ID column. The only constraint on this field is that it must be unique and the value in Placeholder Doc ID columns in the link tables must match a value in the Document ID List sheet. If a value is entered into the Document ID column in the Document ID List sheet then the database will create a matching document ID in the database, provided that it does not already exist.

If there is a value in the Document ID field and a matching document ID already exists in the database then the import function will reject all metadata associated with that document ID.

If the Lock Status field is set to 'Locked' then the document ID will be locked against change. Only a user with Configurator privileges can unlock a document ID.

Note that for any instances where the document ID field is blank and the database will automatically generate the document ID, the import function is not idempotent. This means that if the import is run successive times, each run will automatically generate another new unique document ID with the same metadata as before.

### File IDs

File IDs are a sequence number which are only allocated by the database. As with Document IDs, the import function needs placeholder file IDs to link files to associated metadata. The Placeholder File ID can be any unique number or string.

Elyse ships with duplicate checking set to `On` by default. This automatically prevents loading of duplicate files into the database. Note that if duplicate checking is `Off` then the import function will not be idempotent. This means that if the import is run successive times, each run will re-load the files and create duplicate records.

### Usage

1. Execute the **Generate Import Workbook** command.

2. Save the workbook to a location on disk.

3. Open the workbook in a spreadsheet application.

4. Fill in the data entry sheets (green headers) with the primary records to import. Use the dropdown lists in ID columns to select valid values.

5. Fill in the link table sheets (orange headers) with the relationships between records.

6. Save the completed workbook.

## Workbook Validation

In the [Command Palette](command-palette.md), enter `Import` or `Workbook` into the **Objects** field to locate the **Validate Import Workbook** command. Alternatively, navigate to **Advanced** > **Data Migration** > **Validate Import Workbook** from the navigation bar.  Select the workbook file to validate.  The validation function will display a report of the outcome of the validation.

## Workbook Import

In the [Command Palette](command-palette.md), enter `Import` or `Workbook` into the **Objects** field to locate the **Import Data from Workbook** command. Alternatively, navigate to **Advanced** > **Data Migration** > **Import Data from Workbook** from the navigation bar. Select the workbook file to import.  The function will first validate the workbook and then after importing the data will display a results report.  
