Translation Tables

Translation Tables

image073.png

Figure 44 Reference items - Translation tables

Translate blocks are used to convert message values in to SICS values - and require mapping tables to hold both sides of the mapping. These mapping tables are maintained using the translation table function in Reference items. There are two parts to the transformation table - the table definition, and the table contents. First, we consider the definition of the table.

Define new table #

image074.png

Figure 45 Translation tables - Define new table

A new table is created using the Define New Table option. The table must have a unique name. You can also enter a description of the purpose of the table.

In the column definitions area, you enter the input (document) and output (SICS) fields of the mapping. Each mapping can have any number of inputs or outputs, but when adding further rows, all the inputs will be gathered at the top, and all the outputs at the bottom. The context menu for column definitions also allows you to delete rows, or to reorder the rows by moving them up or down. However, you cannot move the last input row downwards, or the last output row upwards. (The menu options are enabled, but have no effect).

Add Input field #

Input fields can be of three types:

  • String
  • Integer
  • Decimal

The field must be given a unique name.

Add Output field #

Output fields can be any of these types:

  • String
  • Integer
  • Decimal
  • ACORD code
  • Accounting entry code
  • Business Partner
  • Currency
  • eMessaging party identifier
  • Legal Area
  • Reference data
  • Reporting unit

The field must be given a unique name. For certain output types, a further selection must be made.

  • For ACORD code, the codeset must be chosen
  • For Legal Area, an area type (country, state, city etc.) must be chosen
  • For Reference data, the reference data type must be chosen

View table definition #

image076.png

Figure 46 Translation tables - View table definition

This window shows the definition of an existing table.

Edit table definition #

Editing a table definition is only allowed if the table has no contents. All the rows in the table must be deleted before the table can changed. The edit window is otherwise identical to the define new table window above.

Export #

Multiple Translation tables can be exported at the same time. The export option creates an XML file with both the table definition and the table contents. The exported Translation tables are saved to a folder, one file for each Translation table, each file named the same as the Translation table. This facility is useful when transferring mappings and any associated translation tables from one environment to another.

Import New Table #

This option creates a new table from data exported from another database. If there is existing table with an identical table definition or name exists, a warning is raised to use Merge Table or Replace Table menu option. If the choosen XML to be imported has any duplicates, an error is displayed to remove duplicates from the XML before importing. Please note that translation tables should be imported before any mapping that uses them - the mapping will fail to import if any translation table it requires cannot be found.

Merge Table via Import #

Merges data into an existing table, so long as the name and table definitions are identical. When merging tables, new rows will be added and differences in output values for existing input values will be merged. If the choosen table (XML file) from the folder does not have the same name or identical table definition as the selected table from SICS to merge, an error is displayed. If the choosen XML to be merged has any duplicates, an error is displayed to remove duplicates from the XML before merging.

Replace Table via Import #

Replaces data in an existing table, so long as the name and table definitions are identical. When replacing tables, all the existing data will be deleted and new data from the XML will be imported. If the choosen table (XML file) from the folder does not have the same name or identical table definition as the selected table from SICS to replace, an error is displayed. If the choosen XML to be replaced has any duplicates, an error is displayed to remove duplicates from the XML before replacing.

Delete table #

Deletes the table. If the table is used by any transformation mapping, a validation error will result. The mappings must be changed to point to a different table or the translate blocks must be removed before the table can be deleted.

View table contents #

Most of the time, users are not concerned with the table definition. More important on a day-to-day basis is the content of the table.

image077.png

Figure 47 Translation tables - view contents

The contents table will show a column for each input and each output defined for the table. All you can do in this window is view the mappings.

Edit table contents #

In edit mode you can change the name of the table, edit a row and open a context menu. When editing a row, the data you enter must match the type for each column. For output columns containing ACORD or SICS data (e.g. ACORD code, Reference data, entry type) a drop down of allowed values is presented. For eMessaging Party Identifier, a partner find window is opened to allow you to search for the required party, and from there select an agency/id pair to use in the mapping.

The context menu has the following options:

New #

Creates a new row, which can then be edited

Copy #

Makes a copy of an existing row. The contents of at least one input field must be altered before the table can be saved.

Delete #

Deletes the selected row from the table

Merge Values via Import #

Opens a navigation window so that additional values from a spreadsheet can be imported into the table and differences in output values for existing input values will be merged. If the choosen spreadsheet to be merged has any duplicates, an error is displayed to remove duplicates from the spreadsheet before merging.

Replace Values via Import #

Opens a navigation window so that all the existing values in the table will be deleted and values from a spreadsheet can be imported into the table. If the choosen spreadsheet to be replaced has any duplicates, an error is displayed to remove duplicates from the spreadsheet before replacing.

image079.png

Figure 48 Translation tables - Merge Values via Import or Replace Values via Import

You can use Generate Template to create a new spreadsheet with columns that reflect the table to which you are trying to add. Otherwise, you need to have the column headings exactly in line with the table definition, or an error will result. Having selected the spreadsheet, use the Process button verify it is correct. This will test that output values match system requirements - e.g. if the Output is an ACORD code, the spreadsheet value must exist in the relevant ACORD codeset. If there no problems, the import button is enabled,

image080.png

Figure 49 Translation tables - import values OK

click OK to complete the process phase, then Import to load the contents of the spreadsheet into the table. No check of duplicates is made during the import process, but you will not be able to save the table changes if duplicates exist - either edit one or more input values, or delete the duplicates.