Overview - Legacy (Integration)

Data Manipulation steps are used to manipulate/edit the data you are uploading into a compatible format so that it can be imported into your IRP database successfully. This section allows you to set the step/s which will be performed on the import data. They will be repeated automatically, each time a Stock Integration is performed. There are a number of different manipulations which can be performed on an import file:

Setting	Description
Custom Manipulation	This allows you to execute a SQL Script. This option should be used if you cannot manipulate data using the following Data Manipulations. In the example below, the Price value is being changed to the Price plus 10% where the original Price value is less than £5.
Add New Field	This allows you to add a new field to the import file. You can provide the Name, Data Type, Size and Default values as well as enable/disable Null values. In the example below, the EntryDate value is being added to record the date that the data was entered. A default value of the current date is being set using the GETDATE() command.
Remove Field	This allows you to remove an existing field from the import file. In the example below, the ManufacturerCode field will be removed from the import file.
Rename Field	This allows you to rename an existing field in the import file. You must supply the Field you wish to rename and the new name value. In the example below, the ManufacturerCode field will be renamed PartCode in the import file.
Change Field Type	This allows you to change the Data Type and/or Size of an existing field in the import file. In the example below, the Data Type of the Price Field will be changed to Money.
Replace Text	This allows you to replace text in an existing field with a different value in the import file. In the example below, the text 'Size 8' is being replaced with 'Small' in the Option field.
Remove Text	This allows you to remove text from an existing field in the import file. In the example below, the text 'Brand X' will be removed from anywhere in the Model field.
Append Text	This allows you to append text to an existing field in the import file. In the example below, the text 'Brand Y' will be added to the beginning of the Model field.
Split Field	This allows you to split the value held in one field into two separate fields. In the example below, the ManufacturerCode field contains values such as 'CAT01-15895'. This value contains the Category ID of 'CAT01' and the product PartCode of '15895'. The values are separated by the '-' character. This will split these two values into separate fields called Category and PartCode.
Remove Quotation Marks	This allows you to remove quotation marks from an existing field in the import file. In the example below, quotation marks will be removed from the Model field.
Set Field Value	This allows you to set a default value for all records in an existing field, in the import file. In the example below, a default value of '1' will be set against all records of the StockHeldExternally field.
Capitalize Field	This allows you to capitalize the first character of an existing field in the import file. In the example below, the first letter of the value stored in the Brand field will be capitalized in each record of the import file.
Remove Whitespace	This allows you to remove all whitespace from an existing field in the import file. In the example below, all whitespace in the PartCode field will be removed in each record of the import file.

To add a Data Manipulation Step, follow these steps:

1. Go to Integration > Overview in the IRP left navigation menu.
2. Click the Edit button for the type of legacy integration that you want to perform (Product, Stock or Custom).
3. Click the Manipulate Imported Data tab.
   There are three sections here:

   Setting	Description
   Add Data Manipulation Step	Allows you to add a new Data Manipulation Step.
   Edit Data Manipulation Steps	Allows you to edit an existing Data Manipulation Step.
   Sample of Imported Stock Data	Displays a sample data set of the previously imported data.

4. To add a Data Manipulation Step, select the Manipulation from the drop down and enter the required values discussed above.
   Please note that further information on each value is displayed by hovering the mouse pointer over the corresponding text field.
5. Once you are satisfied with the manipulation, click the Add Step button to save it.
6. Repeat points 3 and 4 for each Data Manipulation required in the Stock Integration.

Once a Data Manipulation Step has been added, you can edit the following details:

Once changes have been made, click the Update Data Manipulation Steps button to save them.

Field Mappings are used to link the fields in the import file to fields in your IRP database. Setting the Field Mappings will ensure that the correct fields and values are updated and stored within your IRP database when a file is integrated.

There are a number of required Field Mappings which must be set:

After these have been added, click the Update Pre-Requisites button to save them.

After the required fields have been added, new functionality will appear in the Set up Field Mappings section. This will allow you to add the following optional Field Mappings via drop down:

To add Field Mappings, follow these steps:

1. Go to Integration > Overview in the IRP left navigation menu.
2. Click the Edit button for the Product legacy integration that you want to work with.
3. Click the Map Imported Fields tab.
   > There are three sections here:

   Setting	Description
   Set up Field Mappings	Allows you to add a new Field Mapping.
   Edit Field Mappings	Allows you to edit an existing Field Mapping.
   Sample of Imported Stock Data	Displays a sample data set of the previously imported data.

4. Enter the Required Field Mappings values (column headers) from your import file:
   
   Please note that the first three options also require the relevant data type to be set by selecting the corresponding radio button.
   Click the Update Pre-Requisites button to add the Required Field mappings.
5. Add any Optional Field Mappings you can provide data for:
   
   Click the Add Field Mapping button after entering a Field name to add the Optional Field mappings. Repeat this step for each additional Optional Field Mapping.

To edit Field Mappings, simply change the Import Field value to the correct value and click the Update Field Mappings button to save the changes.

Field Mappings are used to link the fields in the import file to fields in your IRP database. Setting the Field Mappings will ensure that the correct fields and values are updated and stored within your IRP database when a file is integrated.

There are two required Field Mappings which must be set:

After these have been added, click the Update Pre-Requisites button to save them.

After the required fields have been added, new functionality will appear in the Set up Field Mappings section. This will allow you to add the following optional Field Mappings via drop down:

To add Field Mappings, follow these steps:

1. Go to Integration > Overview in the IRP left navigation menu.
2. Click the Edit button for the Stock legacy integration that you want to work with.
3. Click the Map Imported Fields tab.
   > There are three sections here:

   Setting	Description
   Set up Field Mappings	Allows you to add a new Field Mapping.
   Edit Field Mappings	Allows you to edit an existing Field Mapping.
   Sample of Imported Stock Data	Displays a sample data set of the previously imported data.

4. Enter the Required Field Mappings values (column headers) from your import file:
   
   Please note that the first mapping requires you to select the corresponding Field using the drop down box.
   Click the Update Pre-Requisites button to add the Required Field mappings.
5. Add any Optional Field Mappings you can provide data for:
   
   Click the Add Field Mapping button after entering a Field name to add the Optional Field mappings. Repeat this step for each additional Optional Field Mapping.

To edit Field Mappings, simply change the Import Field value to the correct value and click the Update Field Mappings button to save the changes.

The Integration Query is the SQL Query which will run to import the data from the import file/staging table to the various tables in your IRP database. Without the query, Integration is not possible.

An understanding of SQL, IRP Database Structure and your data is required to create the import script. We advise that you contact your IRP Support Team regarding the creation of this query.

Note that this tab will be displayed only if your Admin User account has DBA status.

To add or edit an Integration Query, follow these steps:

1. Go to Integration > Overview in the IRP left navigation menu.
2. Click the Edit button for the type of legacy integration that you want to perform (Product, Stock or Custom).
3. Click the Integration Query tab.
4. We advise creating this query in an external editor and ensuring that it is valid before pasting it into the Query SQL field.
5. Click the Update Integration Query button to save the query.

With the Data Manipulation steps, Field Mappings (not applicable to Custom Integrations) and Integration Query set, you can go on to perform the integration as described in the Legacy Integrations How To Add or Edit an Integration Query article.

After setting the Data Manipulation steps, Field Mappings (not applicable to Legacy Custom Integrations) and Integration Query, you can perform an integration to update the data held in your IRP database.

To perform a legacy integration, follow these steps:

1. Go to Integration > Overview in the IRP left navigation menu.
2. Click the Edit button for the type of legacy integration that you want to perform (Product, Stock or Custom).
3. Remain on the Upload and Integrate tab.
4. Step 1: Upload and Import Delimited File
   
   1. Click the Choose File button and select the integration file on your machine.
   2. If the Column Headers are included in the file, check the File Has Column Names checkbox. This will ensure that the first row is left out of the integration.
   3. Click the Upload button to upload the data.
5. Step 2: Stage Imported Data
   Click the Stage button to perform the Data Manipulation Steps and the Field Mappings (if relevant) defined previously. A sample of the data that will be imported will be displayed in the Sample of Imported Data section. If you are happy with the sample data, proceed to the next step. Otherwise, make any necessary changes to the Data Manipulation Steps or Field mappings and repeat the last two steps, until correct.
6. Step 3: Integrate Staged Data
   Click the Integrate button to run the Integration Query.