ITrack/Enterprise/Import Types

Import types are essentially 'recipes' defined in the database that ITrack uses that tell it how to import different types of data. Import types are currently used on the Data Management screen for users to import data.

ITrack Enterprise comes with many common import types used for basic functions. This list will expand over time. If you wish to automate a particular import type through data management, either:

1. Contact ISoft Data Systems for assistance
2. If you are a power-user who knows MySQL, the following documentation should help you create your own import types

Database Schema

There are several tables involved in creating a single import type. These will be addressed at a high level here. If you need in depth information on any particular table, please contact ISoft or refer to the examples.

`importtype`

This is the main table, and it contains one entry for each type of import that a user can perform with the screen. You can define a name, description, destination table, etc. There are also several columns that allow you to specify whether this import is insert only/update only and whether these options are on by default.

`importtypefield`

For each importtype entry, there are generally many entries in this table. Every entry in this table represents a 'field' that can be mapped by the user on the data management screen. Each field represents (generally) a column in the destination table that can/should be filled out. It is important to understand the difference between this and importtypefieldsource, which dictates ways that this data can come in. Each field should have an appropriate data type and column name set. Several of the more complicated and important fields are described here:

`requirement`

• 'Optional' means a normal field that doesn't need to be mapped.
• 'Required' is a field that must be mapped (this is common for specifying fields that really should be filled out in the destination table).
• 'Update' means a field that, when specified is considered to be part of the set of columns that will be used to match up against existing rows. For example, when updating existing models, the model name might be marked as Update, causing input rows to be compared to existing rows based on this field. Multiple columns can be marked as update if the table has a multi-column 'identity'.
• 'Update Required' means the same as Update but it also must be mapped before import is allowed.

`visible`

Visible columns can be seen by the user an mapped. Invisible fields can still affect the import process if they have a default conversion (see below). Invisible columns with a default are a good way to give columns complex defaults.

`databasefield`

If this is set to True, the `field` column is expected to be the name of a column in the destination table where data will go. If this is set to False, this entry will not go into the final INSERT/UPDATE. However, that doesn't mean it can't affect the import process. Non-database fields are often used for users to specify import options or for mapping information that is required for another field's conversion. For example, looking up a modelid often requires inventorytypeid, manufacturerid (through id or code), and a model name. This means the conversion for such a field will probably reference a couple non-database fields for inventory type and manufacturer.

`defaultconversion`

This is an advanced field that follows the conversion field format. If a field is present that has a default conversion, it will be used if that field has no import type field source that is mapped or has a fixed value. Generally, a default value is specified in here (such as NOW() for a date-time or 0 for quantity), or a default conversion query (such as REPLACE({?partnumber}, '-', ) which would strip dashes out of what is input into the partnumber field.

`importtypefieldsource`

Most of the time, every importtypefield will have a single entry in this table. However, sometimes there is more than one, in the cases where the data the user will supply differs from the data that is saved. This commonly takes the form of a field that saves a unique ID, but a field source that takes a name (and has a conversion that looks up the id for that name). If a field has only one source, the user will not see information about the source on the screen. In this case, the 'output' data type of the source is expected to be the same as the data type for the field. This means either that the data types match, or in the case of a source with a conversion (see below), the output of the conversion matches the field data type. If they don't, implicit casting will happen, causing unpredictable behavior, use at your own risk. If a field has 2 or more sources, the user will see all the sources in a tree on the screen. The sources' name column should help the user understand which they should map to. This commonly happens where a field is an ID, and there are multiple sources, one that uses the raw ID itself (no conversion), and one that uses the name to look up the ID using a conversion query. The user is only allowed to map (or supply a default fixed value to) 1 source for any particular field, making sources essentially different 'options' for how to fill out a field.

`conversion`

This is an advanced field that follows the conversion field format. If this source is 'used' for a particular field, this conversion will be used to translate between the input data and the data type required by the importtypefield. A common application for this is an importtypefield representing a unique ID and a importtypefieldsource that matches to a 'code' or a 'name' and a conversion query that looks up the ID based on the code or name. Refer to the conversion field format form more information.

Conversion Field Format

The data importer in ITrack uses a special query markup language with several advanced features that allows the developer to encode a certain amount of logic into a string. Conversion fields are strings that can contain fixed values, references to other fields, and/or MySQL code. These options can be combined in any logical way that will make sense inside of a query. Each option is explained below. Refer to current import types for details.

• Fixed values: a conversion field can simply have a fixed value specified, such as NULL, 0, or 'NE'. These values are common for hidden fields with default conversions. However, users often map fields to 'fixed values' using the software too. In those cases, they may specify such values and strings instead of a column reference.
• References to other fields: every field in the importtype can be referred to by its unique `field` column. For example, if an import type had a `inventoryid` field, another field's conversion could refer to this field with {?inventoryid}. This can be (and often are) mixed in with the other other options of fixed values and mysql queries. When another field is referred to this way, the reference will be replaced with that other field's conversion string. So, if a conversion string for the 'test' field was '{?inventoryid} + 1', and the inventoryid field was mapped to column3 in the input file, this conversion will turn into '`column3` + 1'. This works with fixed values and subqueries. If a circular reference chain is detected, the system will stop replacing when a loop is detected.
• Use of MySQL: since conversions are written directly into one of the queries during import (after field replacements), conversion queries can contain any amount of MySQL code. This includes:
  • References to functions ('f_get_uuid()')
  • Simple data conversion ('REPLACE({?partnumber}, '-', )')
  • Lookups in other tables ('(SELECT inventorytypeid FROM inventorytype WHERE name = {?self} LIMIT 1)'). Notice that, since this is a subselect, I added parentheses and a LIMIT 1. The LIMIT 1 is good practice because MySQL throws an error if a subselect returns more than one result when being used in a SELECT clause. This comes up when a certain lookup is expected to be unique, but a customer's database is missing a unique key or something.
• Other useful references:
  • Specific columns in the import file can be referred to by column number like this: `column1` or `column22`. Normally this is not suggested because import files can have their columns in any order. The system does this automatically in the background if a conversion query refers to a mapped field using {?fieldname}. However, this can be useful for creating a fixed import that expects a particular input format and you don't want to have any user input.
  • {?self}: this special variable can be used in conversion queries. Its essentially a shorthand to the mapping of this specific column. If this column is mapped to a column, self will be replaced with `columnX`. If this column has a fixed value or query, {?self} will be replaced with that value.
  • Specially defined variable mappings. See special variables for more details. Most of these variables are only available at some points in the import process

Import Process

In order to understand how to build an import type, you need to understand the process that the system goes through in order to import the data the user supplies. The following are the steps that the system takes after the user hits import or test.

1. Perform user data check and ensure all required data is mapped
2. Create an empty table with column names as `column1` through `columnN` for the user input data. That table's name is something like import_<destination>XXXXXXX where destination is the destination table and the XXX is a timestamp to avoid collision. The name of this table is mapped to the variable named {?raw_data_table}
3. Data is imported to this holding table
4. Conversion queries that are found to not involve references to the raw data table are run a single time now, for speed purposes.
5. All 'Before Import' import commands are run, in order of rank. All mapped variables are replaced. This includes the special variable {?raw_data_table} and all the mappings the user created in the mapping step. You should use these variables instead of directly referencing `columnX`. If a query fails,

Special Variable Mapping

• {?raw_data_table}: this variable is mapped before the 'Before Import' commands are run. Its value is the name of the raw data table (this is the table that contains all the user's raw string data in columns named column1 through columnX.