ITrack/Enterprise/Import Types - Revision history

Dillon Sadofsky: /* `databasefield` */

2015-10-15T16:06:22Z

`databasefield`

← Older revision		Revision as of 11:06, 15 October 2015
Line 27:		Line 27:
	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.		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.

	NOTE: when this is False, these 'non-database' fields will still be present in {?converted_data_table} ~~(I'm not sure about {?mapping_data_table})~~ though they will not be part of the import into the target table.		NOTE: when this is False, these 'non-database' fields will still be present in {?converted_data_table} though they will not be part of {?mapping_data_table} or the import into the target table.

	===`defaultconversion`===		===`defaultconversion`===

Dillon Sadofsky: /* `databasefield` */

2015-07-29T23:24:07Z

`databasefield`

← Older revision		Revision as of 18:24, 29 July 2015
Line 26:		Line 26:
	===`databasefield`===		===`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.		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.

			NOTE: when this is False, these 'non-database' fields will still be present in {?converted_data_table} (I'm not sure about {?mapping_data_table}) though they will not be part of the import into the target table.

	===`defaultconversion`===		===`defaultconversion`===

Dillon Sadofsky: /* `importtypecommand` */

2015-07-29T23:22:58Z

`importtypecommand`

← Older revision		Revision as of 18:22, 29 July 2015
Line 46:		Line 46:

	==`importtypecommand`==		==`importtypecommand`==
	This is a type that stores queries (commands) that can be run before and after an import is performed to extend current import behavior. These queries that reference {?raw_data_table} will follow the [[#Conversion Field Format\|conversion field format]] for references to other columns and data. For commands referencing data in {?converted_data_table} and {?~~mapped_data_table~~}, use the value in importtypefield.field to reference column names.		This is a type that stores queries (commands) that can be run before and after an import is performed to extend current import behavior. These queries that reference {?raw_data_table} will follow the [[#Conversion Field Format\|conversion field format]] for references to other columns and data. For commands referencing data in {?converted_data_table} and {?mapping_data_table}, use the value in importtypefield.field to reference column names.

	Currently, the user doesn't see these commands (this may change in the future), but every command should have a user friendly name, description (for describing what is happening as the command runs), error description (for describing what went wrong/couldn't finish), and values for several more complex fields which are described below:		Currently, the user doesn't see these commands (this may change in the future), but every command should have a user friendly name, description (for describing what is happening as the command runs), error description (for describing what went wrong/couldn't finish), and values for several more complex fields which are described below:

Dillon Sadofsky: /* `importtypecommand` */

2015-07-29T23:22:24Z

`importtypecommand`

← Older revision		Revision as of 18:22, 29 July 2015
Line 46:		Line 46:

	==`importtypecommand`==		==`importtypecommand`==
	This is a type that stores queries (commands) that can be run before and after an import is performed to extend current import behavior. These queries follow the [[#Conversion Field Format\|conversion field format]] for references to other columns and data. Currently, the user doesn't see these commands (this may change in the future), but every command should have a user friendly name, description (for describing what is happening as the command runs), error description (for describing what went wrong/couldn't finish), and values for several more complex fields which are described below:		This is a type that stores queries (commands) that can be run before and after an import is performed to extend current import behavior. These queries that reference {?raw_data_table} will follow the [[#Conversion Field Format\|conversion field format]] for references to other columns and data. For commands referencing data in {?converted_data_table} and {?mapped_data_table}, use the value in importtypefield.field to reference column names.

			Currently, the user doesn't see these commands (this may change in the future), but every command should have a user friendly name, description (for describing what is happening as the command runs), error description (for describing what went wrong/couldn't finish), and values for several more complex fields which are described below:
	* `timing`: this dictates when in the [[#Import Process\|import process]] that a command is run. The current options are (See the import process section for more info):		* `timing`: this dictates when in the [[#Import Process\|import process]] that a command is run. The current options are (See the import process section for more info):
	** Before Conversion: this happens after the data is put into the raw data table. No other tables exist yet.		** Before Conversion: this happens after the data is put into the raw data table. No other tables exist yet.

Dillon Sadofsky: /* Conversion Field Format */

2015-07-29T23:20:20Z

Conversion Field Format

← Older revision		Revision as of 18:20, 29 July 2015
Line 61:		Line 61:
	=Conversion Field Format=		=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.		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.

			This format is used both in the conversion queries and in commands that reference columns in the {?raw_data_table}. References to later tables will use importtypefield.field as a column name instead.

	* 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.		* 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.

Dillon Sadofsky: /* Import Process */

2015-04-08T19:17:26Z

Import Process

← Older revision		Revision as of 14:17, 8 April 2015
Line 83:		Line 83:
	# All 'Before Conversion Validation' commands are run. All mapped variables are replaced, including the special converted data and raw data table names. This step is present to allow an opportunity to perform extra pre-validation or add more data to be validated.		# All 'Before Conversion Validation' commands are run. All mapped variables are replaced, including the special converted data and raw data table names. This step is present to allow an opportunity to perform extra pre-validation or add more data to be validated.
	# A 'mapping' table is created that has the same structure as the destination table. This is done by running CREATE TABLE XXX LIKE `destination_table`. This table name is stored in {?mapping_data_table}.		# A 'mapping' table is created that has the same structure as the destination table. This is done by running CREATE TABLE XXX LIKE `destination_table`. This table name is stored in {?mapping_data_table}.
			# All 'Before Mapping' commands are run. All mapped variables are replaced, including the converted, raw, and mapping table names. This step is present to allow an opportunity to modify the mapping table before it receives data. Sometimes unique keys on the mapping table require extra steps to be taken before importing data.
	# Data is copied from the conversion table into the mapping table. This step is important because the mapping table (which is like the destination table) contains primary and unique keys, which can cause duplicate entries to be compressed into one row. This is dealt with by the copy INSERT using ON DUPLICATE KEY UPDATE (and incrementing a special count variable on each row, which can be queried).		# Data is copied from the conversion table into the mapping table. This step is important because the mapping table (which is like the destination table) contains primary and unique keys, which can cause duplicate entries to be compressed into one row. This is dealt with by the copy INSERT using ON DUPLICATE KEY UPDATE (and incrementing a special count variable on each row, which can be queried).
	# [Import only (not test)]: [If the backup table before import checkbox was checked by the user, the destination table is copied into a backup. This usually takes the format of backup_importer.<table_name><timestamp>. The backup_importer database is the default value for a global setting that can be defined at each company.]		# [Import only (not test)]: [If the backup table before import checkbox was checked by the user, the destination table is copied into a backup. This usually takes the format of backup_importer.<table_name><timestamp>. The backup_importer database is the default value for a global setting that can be defined at each company.]
Line 92:		Line 93:
	# All 'After Import' import commands are run, in order of rank. This is pretty much identical to the Before Import command step, except more tables exist.		# All 'After Import' import commands are run, in order of rank. This is pretty much identical to the Before Import command step, except more tables exist.
	# All the temporary tables created in earlier steps are dropped.		# All the temporary tables created in earlier steps are dropped.


	=Special Variable Mapping=		=Special Variable Mapping=

Dillon Sadofsky: /* `importtypecommand` */

2015-04-08T19:15:53Z

`importtypecommand`

← Older revision		Revision as of 14:15, 8 April 2015
Line 50:		Line 50:
	** Before Conversion: this happens after the data is put into the raw data table. No other tables exist yet.		** Before Conversion: this happens after the data is put into the raw data table. No other tables exist yet.
	** Before Conversion Validation: this happens after data has been converted and put into the converted table, but before validation has been run. This step exists for the user to run queries that will perform extra validation or fill out more data that needs to be validated.		** Before Conversion Validation: this happens after data has been converted and put into the converted table, but before validation has been run. This step exists for the user to run queries that will perform extra validation or fill out more data that needs to be validated.
			** Before Mapping: this happens after the 'mapping table' is created, but before data is copied in from the conversion table. Commands at this stage allow preparing the mapping table for the converted and validated data.
	** Before Import: this happens after data is copied from the conversion table to the mapping table, but before data is inserted into the final table.		** Before Import: this happens after data is copied from the conversion table to the mapping table, but before data is inserted into the final table.
	** After Import: this happens after import is complete. This is after everything except for the cleanup.		** After Import: this happens after import is complete. This is after everything except for the cleanup.

Dillon Sadofsky at 17:57, 11 October 2012

2012-10-11T17:57:04Z

← Older revision		Revision as of 12:57, 11 October 2012
Line 29:		Line 29:
	===`defaultconversion`===		===`defaultconversion`===
	This is an advanced field that follows the [[#Conversion Field Format\|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.		This is an advanced field that follows the [[#Conversion Field Format\|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.

			===`conversionvalidation`===
			This field specifies whether to perform validation on the data after the conversion step. 'Invalid values' count as either NULL, or less than or equal to 0 for numeric fields, or empty string for all other types of fields. It should be noted that, currently, every invalid cell is reported with a seperate entry in the warning/error list. This could lead to a lot of entries on large files with big problems.
			* None: do not perform validation, any post-conversion values will be implicitly cast to the destination table during the mapping step
			* Warn: any invalid values should be put into the warning section of the import, but import should not halt
			* Error: any invalid values should be put into the error section of the import, and halts import

	==`importtypefieldsource`==		==`importtypefieldsource`==
Line 41:		Line 47:
	==`importtypecommand`==		==`importtypecommand`==
	This is a type that stores queries (commands) that can be run before and after an import is performed to extend current import behavior. These queries follow the [[#Conversion Field Format\|conversion field format]] for references to other columns and data. Currently, the user doesn't see these commands (this may change in the future), but every command should have a user friendly name, description (for describing what is happening as the command runs), error description (for describing what went wrong/couldn't finish), and values for several more complex fields which are described below:		This is a type that stores queries (commands) that can be run before and after an import is performed to extend current import behavior. These queries follow the [[#Conversion Field Format\|conversion field format]] for references to other columns and data. Currently, the user doesn't see these commands (this may change in the future), but every command should have a user friendly name, description (for describing what is happening as the command runs), error description (for describing what went wrong/couldn't finish), and values for several more complex fields which are described below:
	* `timing`: this dictates when in the [[#Import Process\|import process]] that a command is run. The current options are the 'Before Import~~' step and~~ the 'After Import~~' step~~. ~~See~~ the ~~import process section for more info~~.		* `timing`: this dictates when in the [[#Import Process\|import process]] that a command is run. The current options are (See the import process section for more info):
			** Before Conversion: this happens after the data is put into the raw data table. No other tables exist yet.
			** Before Conversion Validation: this happens after data has been converted and put into the converted table, but before validation has been run. This step exists for the user to run queries that will perform extra validation or fill out more data that needs to be validated.
			** Before Import: this happens after data is copied from the conversion table to the mapping table, but before data is inserted into the final table.
			** After Import: this happens after import is complete. This is after everything except for the cleanup.
	* `rank`: this field (defaults to 1) dictates the order of command execution for all commands with the same timing.		* `rank`: this field (defaults to 1) dictates the order of command execution for all commands with the same timing.
	* `permanence`: Dictates what types of operations the command is used on. 'Test' means only on test imports, 'Import' is only on real imports, and 'Both' is for both.		* `permanence`: Dictates what types of operations the command is used on. 'Test' means only on test imports, 'Import' is only on real imports, and 'Both' is for both.
Line 65:		Line 75:
	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.		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.
	# Perform user data check and ensure all required data is mapped		# Perform user data check and ensure all required data is mapped
	# 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}		# 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}. All column types are TEXT.
	# Data is imported to this ~~holding~~ table		# Data is imported to this raw data table
	# Conversion queries that are found to not involve references to the raw data table are run a single time now, for speed purposes.		# Conversion queries that are found to not involve references to the raw data table are run a single time now, for speed purposes.
	# 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, error handling depends on the postfail and errordescription fields (see [[#`importtypecommand`\|importtypecommand]] for more details).		# All 'Before Conversion' 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, error handling depends on the postfail and errordescription fields (see [[#`importtypecommand`\|importtypecommand]] for more details).
	# A converted data table is created that has the same structure as the destination table. This is done by running CREATE TABLE XXX LIKE `destination_table`. This table name is stored in {?~~converted_data_table~~}.		# The 'conversion' table is created. This table stores 1 row for every input table row, but as data is inserted into it, all conversions are run against it. There is a column for every mapped field in this table, and the column names are the same as they will be in the destination table. However, the data types of these columns are dictated by MySQL and are NULLABLE. This is different than the mapping table, which has all the data types and keys of the destination table. This table also has an AUTO_INCREMENT column which gives the programmer access to which input row id each item relates to. The name of this table is stored in the special variable {?converted_data_table}.
	# ~~The conversion query~~ is ~~run. This query pulls data~~ from the ~~raw data~~ table ~~and puts it~~ into the ~~converted data~~ table. ~~At this stage, all~~ the ~~mappings~~ and ~~conversion queries are run~~ to ~~turn the raw data~~ into the ~~destination table data~~.		# All 'Before Conversion Validation' commands are run. All mapped variables are replaced, including the special converted data and raw data table names. This step is present to allow an opportunity to perform extra pre-validation or add more data to be validated.
			# A 'mapping' table is created that has the same structure as the destination table. This is done by running CREATE TABLE XXX LIKE `destination_table`. This table name is stored in {?mapping_data_table}.
			# Data is copied from the conversion table into the mapping table. This step is important because the mapping table (which is like the destination table) contains primary and unique keys, which can cause duplicate entries to be compressed into one row. This is dealt with by the copy INSERT using ON DUPLICATE KEY UPDATE (and incrementing a special count variable on each row, which can be queried).
	# [Import only (not test)]: [If the backup table before import checkbox was checked by the user, the destination table is copied into a backup. This usually takes the format of backup_importer.<table_name><timestamp>. The backup_importer database is the default value for a global setting that can be defined at each company.]		# [Import only (not test)]: [If the backup table before import checkbox was checked by the user, the destination table is copied into a backup. This usually takes the format of backup_importer.<table_name><timestamp>. The backup_importer database is the default value for a global setting that can be defined at each company.]
			# All 'Before Import' commands ar erun. All mapped variables are replaced, including all special table name variables.
	# [Import only (not test)]: [If the user has the 'empty table before import' checkbox checked, a TRUNCATE TABLE statement is run]		# [Import only (not test)]: [If the user has the 'empty table before import' checkbox checked, a TRUNCATE TABLE statement is run]
	# [If the user has the insert new rows checkbox checked, new rows are inserted into the destination table (import only) using an INSERT INTO <destination> ~~ON DUPLICATE KEY UPDATE...~~ query. If this is a test, a special query is run that JOINs the ~~conversion~~ table to the destination table, looking for rows with no matches on all fields marked as Update or Update Required.]		# [If the user has the insert new rows checkbox checked, new rows are inserted into the destination table (import only) using an INSERT INTO <destination> query. If this is a test, a special query is run that JOINs the mapping table to the destination table, looking for rows with no matches on all fields marked as Update or Update Required.]
	# [If the user has the update existing rows checkbox checked, existing rows that match the input data get their fields updated (import only) using ~~and~~ UPDATE statement that joins the ~~converted~~ data table to the destination table using all the Update and Update Required fields. If this is a test import, a special query is run that JOINs the conversion table to the destination table, lookign for rows with matches on all Update and Update Required fields.]		# [If the user has the update existing rows checkbox checked, existing rows that match the input data get their fields updated (import only) using an UPDATE statement that joins the mapping data table to the destination table using all the Update and Update Required fields. If this is a test import, a special query is run that JOINs the conversion table to the destination table, lookign for rows with matches on all Update and Update Required fields.]
	# [Import only] [If the 'Optimize Table after import' checkbox is checked, an OPTIMIZE TABLE statement is run against the destination table]		# [Import only] [If the 'Optimize Table after import' checkbox is checked, an OPTIMIZE TABLE statement is run against the destination table]
	# All 'After Import' import commands are run, in order of rank. This is pretty much identical to the Before Import command step, except more tables exist.		# All 'After Import' import commands are run, in order of rank. This is pretty much identical to the Before Import command step, except more tables exist.
Line 82:		Line 95:
	=Special Variable Mapping=		=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.		* {?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.
	* {?converted_data_table}: this stores the name of the table that whose schema is like the destination table. This variable is not mapped in the Before ~~Import~~ command ~~stage~~.		* {?converted_data_table}: this stores the name of the table whose column names match the destination column names, but there is still one row per input row. All fields are NULLABLE, and there are no keys except for a special AUTO_INCREMENT key that is used to keep track of which input row created each item. This variable is not mapped in the Before Conversion command stage.
			* {?mapping_data_table} : this stores the name of the table whose schema is like the destination table. This variable is not mapped in the Before Conversion or Before Conversion Validation command stages.

Dillon Sadofsky at 21:53, 9 October 2012

2012-10-09T21:53:55Z

← Older revision		Revision as of 16:53, 9 October 2012
Line 75:		Line 75:
	# [If the user has the insert new rows checkbox checked, new rows are inserted into the destination table (import only) using an INSERT INTO <destination> ON DUPLICATE KEY UPDATE... query. If this is a test, a special query is run that JOINs the conversion table to the destination table, looking for rows with no matches on all fields marked as Update or Update Required.]		# [If the user has the insert new rows checkbox checked, new rows are inserted into the destination table (import only) using an INSERT INTO <destination> ON DUPLICATE KEY UPDATE... query. If this is a test, a special query is run that JOINs the conversion table to the destination table, looking for rows with no matches on all fields marked as Update or Update Required.]
	# [If the user has the update existing rows checkbox checked, existing rows that match the input data get their fields updated (import only) using and UPDATE statement that joins the converted data table to the destination table using all the Update and Update Required fields. If this is a test import, a special query is run that JOINs the conversion table to the destination table, lookign for rows with matches on all Update and Update Required fields.]		# [If the user has the update existing rows checkbox checked, existing rows that match the input data get their fields updated (import only) using and UPDATE statement that joins the converted data table to the destination table using all the Update and Update Required fields. If this is a test import, a special query is run that JOINs the conversion table to the destination table, lookign for rows with matches on all Update and Update Required fields.]
	# [~~Update Only~~] [If the 'Optimize Table after import' checkbox is checked, an OPTIMIZE TABLE statement is run against the destination table]		# [Import only] [If the 'Optimize Table after import' checkbox is checked, an OPTIMIZE TABLE statement is run against the destination table]
	# All 'After Import' import commands are run, in order of rank. This is pretty much identical to the Before Import command step, except more tables exist.		# All 'After Import' import commands are run, in order of rank. This is pretty much identical to the Before Import command step, except more tables exist.
	# All the temporary tables created in earlier steps are dropped.		# All the temporary tables created in earlier steps are dropped.

Dillon Sadofsky at 21:52, 9 October 2012

2012-10-09T21:52:08Z

← Older revision		Revision as of 16:52, 9 October 2012
Line 12:		Line 12:

	==`importtypefield`==		==`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\|importtypefieldsource]], which dictates ways that this data can come in.		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`\|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:		Each field should have an appropriate data type and column name set. Several of the more complicated and important fields are described here:

Line 38:		Line 38:
	===`conversion`===		===`conversion`===
	This is an advanced field that follows the [[#Conversion Field Format\|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.		This is an advanced field that follows the [[#Conversion Field Format\|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.

			==`importtypecommand`==
			This is a type that stores queries (commands) that can be run before and after an import is performed to extend current import behavior. These queries follow the [[#Conversion Field Format\|conversion field format]] for references to other columns and data. Currently, the user doesn't see these commands (this may change in the future), but every command should have a user friendly name, description (for describing what is happening as the command runs), error description (for describing what went wrong/couldn't finish), and values for several more complex fields which are described below:
			* `timing`: this dictates when in the [[#Import Process\|import process]] that a command is run. The current options are the 'Before Import' step and the 'After Import' step. See the import process section for more info.
			* `rank`: this field (defaults to 1) dictates the order of command execution for all commands with the same timing.
			* `permanence`: Dictates what types of operations the command is used on. 'Test' means only on test imports, 'Import' is only on real imports, and 'Both' is for both.
			* `testresults`: if this is set to true, this query's results should be displayed in the test results dialog. This is currently not implemented, but will be eventually.
			* `commandeffect`: tells the import screen whether the results should be ignored ('None'), or added to the total number of inserted rows ('Inserted'), updated rows ('Updated'), or deleted rows ('Deleted').
			* `postfail`: dictates error handling. If this is set to 'Stop', a query error will halt import execution. If its set to 'Error', execution stops and the errordescription is shown to the user, and if its set to 'Continue' execution continues.

	=Conversion Field Format=		=Conversion Field Format=
Line 59:		Line 68:
	# Data is imported to this holding table		# Data is imported to this holding table
	# Conversion queries that are found to not involve references to the raw data table are run a single time now, for speed purposes.		# Conversion queries that are found to not involve references to the raw data table are run a single time now, for speed purposes.
	# 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,		# 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, error handling depends on the postfail and errordescription fields (see [[#`importtypecommand`\|importtypecommand]] for more details).
			# A converted data table is created that has the same structure as the destination table. This is done by running CREATE TABLE XXX LIKE `destination_table`. This table name is stored in {?converted_data_table}.
			# The conversion query is run. This query pulls data from the raw data table and puts it into the converted data table. At this stage, all the mappings and conversion queries are run to turn the raw data into the destination table data.
			# [Import only (not test)]: [If the backup table before import checkbox was checked by the user, the destination table is copied into a backup. This usually takes the format of backup_importer.<table_name><timestamp>. The backup_importer database is the default value for a global setting that can be defined at each company.]
			# [Import only (not test)]: [If the user has the 'empty table before import' checkbox checked, a TRUNCATE TABLE statement is run]
			# [If the user has the insert new rows checkbox checked, new rows are inserted into the destination table (import only) using an INSERT INTO <destination> ON DUPLICATE KEY UPDATE... query. If this is a test, a special query is run that JOINs the conversion table to the destination table, looking for rows with no matches on all fields marked as Update or Update Required.]
			# [If the user has the update existing rows checkbox checked, existing rows that match the input data get their fields updated (import only) using and UPDATE statement that joins the converted data table to the destination table using all the Update and Update Required fields. If this is a test import, a special query is run that JOINs the conversion table to the destination table, lookign for rows with matches on all Update and Update Required fields.]
			# [Update Only] [If the 'Optimize Table after import' checkbox is checked, an OPTIMIZE TABLE statement is run against the destination table]
			# All 'After Import' import commands are run, in order of rank. This is pretty much identical to the Before Import command step, except more tables exist.
			# All the temporary tables created in earlier steps are dropped.


	=Special Variable Mapping=		=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.		* {?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.
			* {?converted_data_table}: this stores the name of the table that whose schema is like the destination table. This variable is not mapped in the Before Import command stage.